BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Posted by Sam Lowrie on 01 June 2018
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.
Creating dynamic report titles.
My report title is looking a little generic and shows the same title even when a filter is applied. it would be nice if the title updated depending on the slicer value chosen:
Unfortunately, you cannot currently create dynamic visualisation titles; instead we'll use a card visual.
Ideally the title should include all fields ticked in the slicers, or if none are ticked show All Centres. Speaking of, this is the measure to do it:
Don't worry, we will walk through it step by step!
The first thing to check is if any filtering has occurred via the slicer. This can be done using an IF and the ISFILTERED function.
ISFILTERED returns true if there is a filter on the given column.
If a value of true is returned, then the slicer has some values ticked. To list those values, we use another function in the true part of the IF:
CONCATENATE can only join two strings together, so instead we need CONCATENATEX.
CONCATENATEX takes in a table and for a given column concatenates all the rows together, separated by the specified delimiter. So ours will show all the centre types ticked in the slicer separated by commas:
UNICHAR(10) was used to create line breaks after the first line and after each value.
For the sake of completeness, what would happen if all the boxes were ticked? Fundamentally this is the same as not being filtered, but can Power BI tell?
Arghhh! So filtering for everything still returns True using the ISFILTERED function.
Interestingly if you use the Select All option on the slicer then Power BI knows that there is no filter:
This shouldn't impress me as much as it does.
This example only covers one slicer but the premise for multiple slicers is exactly the same: simply duplicate the IF section for every table that is in a drill through, filter or slicer.
If you would like to see the report used in this blog click here. I've added a drill through and another slicer.