Forum Discussion
Using measure as filter causes visualisation to hang
Community ChampionIf you've built a test pbix file can you share it anyway? Realise the speed issue won't be present but it will show us how your model is setup.
I'm assuming in your main pbix file you have a much larger amount of data. Your visual filter measure is then being triggered for every single row. What we need to do is see if we can optimise that code or alter the model to speed it up but would need to see more to help.
Hi bcdobbs this is the test file https://file.io/nTOMlGKdIih3
I have a lot of other measures in the 'live' file's table, which I expect is the problem. But I'm looking for a method to establish what the exact problem is 🙂
Thanks
- bcdobbs
Thanks Anonymous .
So to confirm my understanding you're wanting the visual to display orders that were either ordered in the selected date range or delivered in the selected date range?
How many rows does your real Sales table have?Hi bcdobbs the real datatable has about 300,000 rows
The live file only hangs when I introduce the filter measure (i.e. the equivalent of the measures "Measure" or "Measure 2" in the test file I posted)
- bcdobbs
Hi Anonymous ,
I haven't got an instant solution but the method of diagnose would be to copy the query from Performance Analyser (View >> Performance Analyzer) and see exactly what is happening. You can paste it into DAX Studio and turn on Server Timings to see how long things are taking.Your visual filter measure on it's own isn't bad but if I create another measure and use it in the same visual I can start to get a feel for what might be happeneing. The code that comes out looks like this:
"Delivery Time" is my new measure just for a trial.
This query is what Power Bi sends when it renders the visual.
You can see that the date filter is picked up and although referenced in SUMMARIZECOLUMNS isn't reducing the row set at all. Everything gets computed for every row and then at the end that table only keeps rows with [Measure] = 1.
Some options:
1) Use two date tables with live relationships to both Order Date and Delivery Date. That will stop every row being calcualted every time.
2) I think rather than using a visual filter if you moved the USERELATIONSHIP inside your actual measures you'd get a performance benefit. You could either use two version of each measure or if you used a disconnected table to enable you to select "Order" or "Delivery" and then test using SELECTEDVALUE in the measure to determine which relationships to activate.
Hope that gives you some starting points.