Are you aware of Visual Level Filters (VLF) in Power BI Desktop?
I was not until I saw this video by Patrick from GuyInACube (YouTube – Twitter).
VLF’s are amazing and I think they add a very neat solution to a request often made by users. Having dimensions filtering other dimensions.
VLS’s were added a year ago in the June 2019 Power BI Desktop release but I didn’t notice that, and I really think it deserves some publicity.
Problem
When working with Power BI the general recommended data model is the star schema with the relationships between dimensions and fact tables to be one-to-many where dimensions filter facts.
However a very common request from users are that if they have several dimension slicers that a selection in one slicer for e.g. Country also reduce the possible values in other slicers.
So when the users has selected a country, the slicers for Product, Year, Month, … should then only show the values applicable for the selected country.
Before VLF’s the solution would be to change the relationships between dimensions and facts to be bi-directional instead of one-to-many.
And that is not recommended at all as that can create a hole lot of problems. Many if not all the books I have been reading about data modelling in Power BI has loudly said – Don’t do that.
VLF – The solution
It is very easy to start using VLF’s.
All you need is a measure that you then add to the filter section for the slicers that you want to be filtered.
In the example here I am using the AdcentureWorksDW2017 database and I have created a report with a chart showing sales amount over time.
The data model is simple containing 3 dimensions and one fact table.
I have added 7 slicers for Country, Gender, Color, Product Category, Product Subcategory, Year and Month, and all slicers has been configured with VLF’s using the explicit measure “Sales count” and testing for “is not blank”.
When selecting the color Yellow the possible values for Product category, Product subcategory and Year are reduced.
When selecting the product subcategory Jerseys the possible values for Product category and Year are further reduced.
So once again. VLF’s provide a simple solution to a real challenge.
Performance
When I saw Patrick’s video I wondered how the performance would be and in order to test that I created a few scripts that could enlarge the standard AdventureWorksDW2017 database.
So far the scripts only enlarge the FactInternetSales table, but future version might do more. You can find the scripts here on GitHub
I have tested with 1, 10, 25, 50, 100 and 250 million rows in FactInternetSales and by using the Performance analyzer in Power BI Desktop I could not see any difference in timing when the number of records increased.
However the one performance difference I could clearly see is that it matters if the slicers used are configured as lists or dropdowns.
Slicers that are configured as list are more expensive as when slicers are configured as dropdowns. But the least expensive approach is to have slicers hidden in a slider panel as explained by Reid Havens in Creating a Slicer Pop Out Window with Bookmark Buttons in Power BI
Slicers as list
Slicers as dropdown
Slicers in pop-out panel
As slicers are hidden when the report page opens up, they have no cost at all at that time.
When the slicer panel pops out the slicers has a cost similar to when they are placed directly on the report page.
Downloads
Report and data model
The Power BI Desktop report as well as the script creating the data model that I have used here can be downloaded from GitHub.
Acknowledgement
List of values
The cards for year and months uses the code to format a list of values made by Alberto Ferrari and presented in this video from GuyInACube (YouTube – Twitter).
The code displays single values and ranges of values very nice.
Creating measure tables
In the report I have organized the measures in separate tables using the method explained by Reid Havens in this video.
By doing this I got all the measures listed before the normal tables and also with measure icons instead of table icons.
