BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
This blog shows how to create fully dynamic reports, whereby users can choose which statistics to show and which dimensions to report them by.
- Making Power BI reports dynamic
- How to create dynamic measures (this blog)
- Creating a Power BI report with dynamic dimensions
Posted by Andy Brown on 03 January 2019
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
How to create dynamic measures
This is much the easier of the two problems, although it does require a working knowledge of DAX.
Step 1 - create a table of choices
The first thing to do is to create a slicer allowing you to choose a measure. Here in outline is how to do this. First create a new table:
Click on the Enter Data tool to create a table.
Type in a table name, column name and values such that when you finish you have a table of all the statistics that you might want to show:
The final table of measures, when you've finished creating it.
Step 2 - allowing a user to choose a measure
The next step is to create a slicer based on this table:
A suggested slicer, allowing you to choose one measure to display.
Note that one problem with the standard Power BI slicer is that a user can select more than one measure (or no measures at all). One way to get round is to use the Chiclet Slicer custom visual, which lets you force a user to select one (and one only) item.
Now create a measure which allows you to show a suitable title:
Title = IF(
// if someone has chosen exactly one measure ...
HASONEVALUE(Choices[Choice]),
// ... display its value ...
VALUES(Choices[Choice]),
// ... otherwise show an error message
"You must choose one measure"
)
You can now create a card and set this to display the value of your measure:
The card now displays the name of whichever measure you selected.
Step 3 - getting the chosen measure to display in your visual
The final bit of magic is to create a measure which displays a different statistic according to the measure you selected in the slicer:
Measure to show = IF(
// if someone chose a single measure ...
HASONEVALUE(Choices[Choice]),
SWITCH(
// test its value (ie what user chose)
VALUES(Choices[Choice]),
// if it's "Total sales", display the sum of sales
"Total sales", SUM(SalesData[Sales]),
// if it's "Average price", show the average sales
"Average price", AVERAGE(SalesData[Sales]),
// if it's "Number of sales", count the sales rows
"Number of sales", COUNT(SalesData[Id])
),
BLANK()
)
Now select your table (or any other visual):
We want this table to show the statistic we've chosen.
You can then use this visual to display the measure you've created:
Choose to display your measure in the table.
And as they (used to) say in the UK, Bob's your uncle!
- Making Power BI reports dynamic
- How to create dynamic measures (this blog)
- Creating a Power BI report with dynamic dimensions