very recently when we were working on a BI project, we needed to create a Power BI report to visualize certain detail level information. Therefore we used Matrix visual for that. When we were developing the report, we felt that since the amount of information is massive, we should filter out some of the data base on some thresholds. So what we did was when creating the Matrix , we introduced a Visual Level Filter saying “YTD Sales Amount” should be greater than 1000. However , once we introduced that filter, just to make some performance improvement to the report, we noticed performance has become even worse.
In order to see what caused this problem, I ran a profiler and checked the DAX query generated from the Power BI. Check bellow DAX statement I got using the profiler.
As you can see, the visual filter I added in Power BI is added as a variable (_ValueFilterDM15) inside DAX and that variable is used as a filter in evaluate statement. Lets run the same query in DAX studio and check the result.
As you can see , it took close to 72 seconds ( 71,770 ms) to execute this query.
Lets remove the visual level filter from the Matrix and see what is the query generated by Power BI. Check the bellow query and see the difference compare to previous query.
Obviously the filter variable is missing and that no such filters in evaluate statement. Lets execute this query in DAX studio and lets check the result.
This time the query runs much faster compare to previous case. While I’m not a DAX expert to explain this difference, what I understood was using a Visual Level filter to limit the no of record is a bad idea as it reduce performance drastically. Note that performance difference will always depend on your query. In my case it was always 3 to 4 times slower than normal scenario. I believe this happens only if you use numeric field as a filter. If you use a text field as a filter, Power BI generates a different DAX query and it will give better performance than normal query. Thanks for reading and please comment your experience about this.