Forum Discussion
DAX filter performance issue
Hi
This is related to https://community.powerbi.com/t5/Desktop/Filter-table-based-on-less-than-and-greater-than-date-value/m-p/2270598#M824679
This has been impemented but I'm experiencing performance issues when dragging a field into my tablix visualisation
My data model:
The table 'Claim Amounts' has approx. 1.5m rows
I have a measure that sets a value based on the value selected in the Binder Dates[Date] slicer:
The problem I'm experiencing is that when I try to include Policy Reference in the below visual and apply a filter the query just times out. I guess it's because I'm trying to apply a filter on 1.5m rows
Is there another solution to this? This works just fine except for the 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.
7 Replies
Hi, I tried this but didn't make much difference to performance:
Date Filter =VAR DateFilter = IF(SELECTEDVALUE('Binder Dates'[Date])>=MAX('Binding Authority'[Binder Date From])&&SELECTEDVALUE('Binder Dates'[Date])<=MAX('Binding Authority'[Binder Date To]),1,0)RETURN DateFilter
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.
