Using a Visual Level Filters in Power BI when connecting to a Tabular Model? Be careful,It could be a very Bad Idea !!

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.

image

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.

Query Time:

image

Server Timing:

image

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.

image

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.

Query Timing:

image

Server Timing:

image

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.

Cheers !!

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s