How to switch between reports using Bookmarks in Power BI

Before creating any bookmarks in power Bi, please find the below instructions.

Make sure you are enabled the Bookmarks pane in your desktop version. To enable the bookmarks pane please go to view option on your power bi desktop and tick on the Bookmarks pane.

For reference see the below example.

I have two reports called Visual and Table. Both reports contains Total revenue by city.

To create a bookmark in Visual page do the followings steps.

1. Go to Home - Find Button menu.
2. On Button menu select Bookmark Option from drop down list.
3. Go to Table report.
4. Go to bookmark pane on right navigation bar and add a new bookmark and rename it.
5. Go to Visual page and click on the Bookmark button you are selected.
6. Once you will click on the Bookmark button go to Visualizations pane on right side.
7. Go to Action in Visualizations pane.
8. Select Bookmarks in Type option.
9. Choose the bookmark name that you have created and select it on Bookmark option.
10. Click on the Bookmark button CTRL+Click.

CTRL + Click on bookmark button it easily toggle to the table report.

How can Use Tooltip in Power BI

You can create visually rich report tooltips that appear when you hover over visuals, based on report pages you create in Power BI Desktop. The Tooltips will appear from one page to another page once you are putting your mouse over the visual.

To create a tooltip page, please do the following steps.

1. Go to Power BI Desktop
2. Go to page properties, page size and set your layout accordingly.
3. Take any visual, can use Donut Chart
4. Insert the field value.

To create a main page or called Home page.

1. Take any visual like Ribbon chart
2. Put your fields as per report creation
3. Click on the Ribbon Chart
4. Go to properties
5. Put ON tooltip option.
6. Set Type like Report page
7. Then add Page as your tooltip page Name.

Power BI useful Formulas

Power BI Aggregate functions


Measure Name = SUM(Expression or Column Name)

Measure Name = AVERAGE(Expression or Column Name)

Measure Name = MIN(Expression or Column Name)

Measure Name = MAX(Expression or Column Name)

MaxSale = MAX(Expression or Column Name)

Measure Name = COUNT(Expression or Column Name)

Measure Name = VAR.S(Expression or Column Name)

Measure Name = VAR.P(Expression or Column Name)

Measure Name = STDEV.P(Expression or Column Name)

Measure Name = STDEV.S(Expression or Column Name)

Measure Name = MAX(Expression1, Expression2)                             --2 args

Measure Name = MIN(Expression1, Expression2)                             --2 args




Create a new table from existing table

1.       Table_Name = related(Existing_TableName)

2.       NewTableName = SUMMARIZE(ExistingTableName, ExistingTableName[column1], ExistingTableName[column2])                   --selected columns



Power BI DATE functions

1.       Day= DAY(NewTableName[OrderDate])                                                 – day from date column

2.       MonthinDate = MONTH(EmployeeSales[HireDate])

3.       YearInDt = YEAR(EmployeeSales[HireDate])

4.       Hour = HOUR(EmployeeSales[HireDate])

5.       Min = MINUTE(EmployeeSales[HireDate])

6.       Sec = SECOND(EmployeeSales[HireDate])

7.       Date1 = DATE(YEAR(NewTableName[Date]), MONTH(NewTableName[Date]),                 

8.       DAY(NewTableName[Date]))

9.       WeekDay = WEEKDAY(NewTableName[OrderDate], 1)    --If Number = 1 Week begins on Sunday, and If Number = 2 then Week begins on Monday. Let me create a column to find the week number

10.   WeekNum = WEEKNUM(EmployeeSales[HireDate], 1)

11.   YearFrac = YEARFRAC(EmployeeSales[HireDate], NOW())

12.   DateInMonth = FORMAT(NewTableName[OrderDate],"mmmm")

Let me add 2 months to Hire Date on Existing month

13.   Edate = EDATE(EmployeeSales[HireDate], 2)        --adding 2 months

14.   Eomonth = EOMONTH(EmployeeSales[HireDate], 3)         --adding 3 months

15.   DATEDIFF(Start_Date, End_Date, Difference_Value)         ---dateDiff

16.   DateDiff = DATEDIFF(EmployeeSales[HireDate], NOW(), YEAR)    –dateDiff in year

17.   Calender = CALENDAR(DATE(2018,1, 1), DATE(2018, 1, 15))           –creating a date table from a given range

18.   CalenderAutotable = CALENDARAUTO()                 –will create a new date table and will take auto date from existing table



Power BI Logical functions

1.       IfElse = IF(NewTableName[WeekDay]=23,"Yes","No")

2.       Column = IF(NewTableName[IFLeng]=2,FORMAT(NewTableName[IFLeng],"00"))                 -- if IFLeng=2, then add one preceding zero.

3.       Column 2 = IF(NewTableName[WeekDay]>20,"Greater","Smaller")



4.       NestedIfEx = IF(NewTableName [WeekDay] < 30, "Lesser than 30",       IF(NewTableName [WeekDay] > 30, "Good", "Greater than 30" ))



5.       IFAND = IF(AND(NewTableName[WeekDay]>20,NewTableName[WeekDay]<30),"Between Thirty","Not in 20-30")



6.        IFOR = IF(OR(NewTableName[WeekDay]=30,NewTableName[WeekDay]=20),"Thirty-Twenty")



7.       Number_Of_Rows = COUNTROWS('Sales Data') – Your table_name.

8.       IfNot = NOT(IF(NewTableName[WeekDay]>30,"True","False"))



9.       SwitchMonth = SWITCH(MONTH(NewTableName[OrderDate]), 1, "January", 2, "February", 3, "March", 4, "April", 5, "May",12, "December", "Unknown")  --If jan,feb or other months are not matching with their respective serial number , will return Unknown else same month name.

Max cost in Power BI

To find the maximum cost in Power BI please do the all following steps

1. Open your Power BI desktop 
2. Go to Modelling 
3. Go to New Column
4. Add this below formula.

MaxOf = MAX(Table1[Cost])

Min Cost in Power BI

To find the minimum cost in Power BI please do the all following steps

1. Open your Power BI desktop 
2. Go to Modelling 
3. Go to New Column
4. Add this below formula.

MinOf = min(Table1[Cost])  

Sum in Power BI

To do sum in Power BI please do the all following steps

1. Open your Power BI desktop 
2. Go to Modelling 
3. Go to New Column
4. Add this below formula.

Sum_Example = SUM(Table1[Cost]) 

Nested If in Power BI

To create a nested if please follow the below steps

1. Open your Power BI desktop 
2. Go to Modelling 
3. Go to New Column
4. Add this below formula.

NestedIfEx = IF(NewTableName [WeekDay] < 30, "Lesser than 30",

IF(NewTableName [WeekDay] > 30, "Greater than 30"))

empty box

If Else condition in Power BI

If Else condition in Power BI: -



   Open Your data set in Power BI desktop



2. Go to Query
Editor





3 . Go to Add
Column





4.Go to
Custom Column





5. Change your
header name

Output:

Total Number of Rows from a table in Power BI

Total Number of Rows in a Table: -



Go to Desktop mode on Power Bi
Go to Modelling
Go to New Column
Add this Formula
 Number_Of_Rows = COUNTROWS('Sales Data') – Your table_name.
   Enter


It will create a new column and will update the total number of rows from your table.