Dynamic Measure and Dimension in Power BI

Hello friends! Today we’ll be learning how to create dynamic measures and dimension using slicer. We’ll have a drop down select the drop down for dimension and measure. Easy to follow steps. I have attached Power BI file to used in this blog.

We’ll create dimension and measure slicer separate. First we’ll create the dynamic Dimension and then dynamic Measure

Dynamic Dimension

For dynamic dimension go to options and setting > Options > Preview features > Field parameters select that and press OK (as shown in the image below). Restart Power BI once you get a message to restart the Power BI.

add the field parameter as shown in the image below

Now to create the dynamic dimension. Go to New parameter from modelling tab and select Fields. You’ll get the dialogue box to add the dimension fields you want to add to your Power BI report.

Name the parameter I have named as Dimension in this case and add the fields you want to have in this dimension drop down and press create. You’ll find a dimension parameter in the fields list.

Dynamic Measure

Next step is to create the dynamic measure.

Step1. First we need to create the measures we want to have in the drop down. In this case I have created 2 measures

SalesM = Calculate(SUM('Table'[Sales]))
ProfitM = Calculate(SUM('Table'[Profit]))

Step2. Create a table with list of measures and give a ranking. Go to Home > Enter Data and add two columns first is for the measure name and second is for the ranking (as shown in the image below)

Step3. Now crate another measure to assign the measure selection in the drop down.

Switch Measure = SUMX ('Measure',SWITCH ([Rank],1, [ProfitM],2, [SalesM]))

Create report

1. Add Dimension parameter to the report
2. Add switch measure to the report
3. Add a slicer and add dimension parameter
4. Add another slicer and add Measure column from the measure table to the slicer

Similar logic is being used to create Bar chart using measures in Power BI.

Download Power BI file here

Keep visiting Analytics Tuts for more tutorials.

Thanks for reading! Comment your suggestions and queries