Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hey all,
I have a dataset that is 2M+ rows and this measure is killing my performance. How do I not use FILTER() and MAX()/MIN() together in a measure that references different tables? Below I'll lay out the measure in question and the other measure it references. What I have done is set up a date filtering system where the user will select something like "Year to date" as a value in 'Dates'[Preset Dates] and then I want it to show the count for the product IDs filtering by > the MIN month and < the MAX month in the 'Dates' table that I Have set up. All the other measures work great, but there are over two million product IDs and its killing the report to have that FILTER dax in there. If it was all in one table I could use a column calculation, but I am referencing different tables here and can't do a column calculation that I know of. Any thoughts? Thanks in advance!
Original Measure=
if(HASONEVALUE('Dates'[Preset Dates]),
CALCULATE([Count of Products]
,FILTER('Category','Category'[Accounting Month] <= MAX(Dates[Max Month])
&& 'Category'[Accounting Month] >= MIN(Dates[Min Month])
))
,CALCULATE([Count of Products]))
Count of Products =
CALCULATE(DISTINCTCOUNT('Category'[ID]),'Category'[Type]="Office Supply" || 'Category'[Type]="General Merchandise")
Solved! Go to Solution.
Thanks for that!
Here's an initial suggestion - interested to hear whether this improves performance. All I've done is change the 2nd argument of filter to a filter on 'Category'[Accounting Month] using DATESBETWEEN.
Original Measure Updated = IF ( HASONEVALUE ( 'Dates'[Preset Dates] ), CALCULATE ( [Count of Products], DATESBETWEEN ( 'Category'[Accounting Month], MIN ( Dates[Min Month] ), MAX ( Dates[Max Month] ) ) ), [Count of Products] )
Regards,
Owen
Hi there @MWinter225
As you've suggested, I would say the FILTER('Category'...) is killing the measure.
Generally speaking you should avoid filtering a fact table within a measure, and instead filter the specific columns required, in this case Accounting Month.
Also, it can improve performance if you directly apply the filtered values rather than using <= or >= conditions, as these are converted to FILTER (ALL(...)).
I could help come up with a specific change to your measure, but just want to check a few things first.
Regards,
Owen
Thanks for that!
Here's an initial suggestion - interested to hear whether this improves performance. All I've done is change the 2nd argument of filter to a filter on 'Category'[Accounting Month] using DATESBETWEEN.
Original Measure Updated = IF ( HASONEVALUE ( 'Dates'[Preset Dates] ), CALCULATE ( [Count of Products], DATESBETWEEN ( 'Category'[Accounting Month], MIN ( Dates[Min Month] ), MAX ( Dates[Max Month] ) ) ), [Count of Products] )
Regards,
Owen
@OwenAuger That did the trick! Thank you so much! You saved me a lot of headache!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |