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.
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] ) + 1
2) 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
What happens at the moment if they select multiple dates? I would expect it to work.