Filter your visualization report on passing parameter dynamically on power Bi

In the below screenshot, I am filtering my visualization report based on the selected inputs from the parameter drop down box. click to download the dataset

Please import this dataset on your power bi desktop and follow the instruction below.

• Home->Get Data->Choose Excel->Choose your path->select your excel file
• Click on Home button then Transform data
  
  
  
• Home->Manage Parameter
• 

Note : In type, you can choose any data type based on your requirement. In Suggested values, either you can choose Any value, List of Values or Query. Only the difference is -  in Any value, the input parameter will be constant with the user defined inputs. Inside the List of Values- you can pass multiple parameter but also it would be a constant value or a static parameter. Sometime if you want to modify your parameters you can edit and also data may be differ from your dataset, So the parameter it could not filter your dataset if it is not available.

What if we can add the parameters based on the availability of our data that we have in our Segment field. So, the query option is nothing, it is the set of data that we are creating from our exiting dataset from a particular field.

To create any query on your dataset field , please perform the below step.

Choose you dataset on Edit Query mode-> then Chose your Field->Right Click->Add as new query.

Once the query is created you can choose that Query in query option while creating the parameter.

In this case, the user not to be redefine the parameter values if there are any addons in our dataset.

• select dropdown on your dataset field (Segment on the current data set) and select text filters->Equal 

• Please click on the ABC as per the above screenshot to select parameter.
• Apply and close the power query mode
• Now go to Power BI desktop and transform data
• Edit Parameter
• choose your respective parameter (These parameters are coming from our query that we created on dataset "Segment" field)

• After choosing your parameter value click on apply changes
• and below is my final visualization based on I selected the parameter.

Type of DAX Function in Power BI

https://www.tutorialspoint.com/power_bi/dax_basics_in_power_bi.htm
DAX Functions
In Power BI, you can use different function types to analyze data, and create new columns and measures. It includes functions from different categories such as −
Aggregate
Text
Date
Logical
Counting
Information
Power BI provides an easy way to see the list of all functions. When you start typing your function in the formula bar, you can see the list of all functions starting with that alphabet.
Aggregate Functions
DAX has a number of aggregate functions.
MIN
MAX
Average
SUM
SUMX
Counting Functions
Other counting functions in DAX include −
DISTINCTCOUNT
COUNT
COUNTA
COUNTROWS
COUNTBLANK
Logical Functions
Following are the collection of Logical functions −
AND
OR
NOT
IF
IFERROR
TEXT Functions
REPLACE
SEARCH
UPPER
FIXED
CONCATENATE
DATE Functions
DATE
HOUR
WEEKDAY
NOW
EOMONTH
INFORMATION Functions
ISBLANK
ISNUMBER
ISTEXT
ISNONTEXT
ISERROR
DAX Calculation Types
In Power BI, you can create two primary calculations using DAX −
Calculated columns
Calculated measures

Create Rank based on minimum cost if ID is same in Power Bi

Rank_On_minCost_for_Same_ID = 
    RANKX(
        FILTER(
                'Table1',
                'Table1'[ID]=EARLIER('Table1'[ID])
                ),
        'Table1'[Cost],
        ,
        ASC,
        Dense
        )

Create a new table from an Existing table with filter condtion in

To create a new table with filter condition like SQL using where clause. Here we can use CALCULATETABLE and Summarize function.

Steps :

1. Go to Power bi desktop version
2. Go to modelling
3. New Table
4. Put below formulas

New_Table_name = CALCULATETABLE (
SUMMARIZE 
(
'Old_Table',
'Old_Table'[Col1],
'Old_Table'[Col2],
'Old_Table'[Col3],
'Old_Table'[Col4],Old_Table[Col5],
),
'Old_Table'[ID]=1
)

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"))

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.