Forum Discussion
DAX filter performance issue
I'd consider expanding the table with the dates so you have a row per day for each item:
Have a look at:It takes a basic table with From and To dates and then in power query:
1) Adds a Number of Days custom column:
Number.From( [Date To] - [Date From] ) + 12) Sets it's data type as whole number.
3) Adds a custom column to contain a list of dates:
List.Dates([Date From], [Number of Days], #duration(1, 0, 0, 0))4) Expands the list to new rows.
5) Removed other columns.
6) Set date column as a date.
I think you can use this to massively increase the speed of the filter on that table.
If you're then still having performance issues the only other thing I can think is to move the filter directly in DAX by getting VALUES ( 'Binding Authority'[UMR] ) and using TREATAS within calculate to move it directly over to your big table.
You could try removing the bridge table and replacing it with a direct many many relationship with Binding Authority set to filter Claim Amounts.
My understanding is the engine should be more optimised for that.
Failing that you need to change the grain of the binding authority so each row represents a day. That makes the filter much simpler. Can share some code later. Sounds odd that adding more rows will speed it up but it massively will.
Hi, I did have this as a many to many but had to change it to get a calculation working. I think when I had this has a direct many to many I was still experiencing performance issues
I'd consider expanding the table with the dates so you have a row per day for each item:
Have a look at:It takes a basic table with From and To dates and then in power query:
1) Adds a Number of Days custom column:
Number.From( [Date To] - [Date From] ) + 12) Sets it's data type as whole number.
3) Adds a custom column to contain a list of dates:
List.Dates([Date From], [Number of Days], #duration(1, 0, 0, 0))4) Expands the list to new rows.
5) Removed other columns.
6) Set date column as a date.
I think you can use this to massively increase the speed of the filter on that table.
If you're then still having performance issues the only other thing I can think is to move the filter directly in DAX by getting VALUES ( 'Binding Authority'[UMR] ) and using TREATAS within calculate to move it directly over to your big table.
So I have expanded out the dates and this has massively helped! Performance is hugely improved and no longer timing out.
I would never have thought adding extra rows would improve this.
The only drawback I would say is this would only work in a single select. I'm trying to think of a situation where a user might want to select multiple dates but at the moment this is working.
Many thanks
