Forum Discussion
DAX filter performance issue
- 4 years ago
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.
brian0782 can you rewrite this IF statement by storing the calculations in a variable and using those variables might improve the performance.
Hi, I tried this but didn't make much difference to performance: