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.
So I have a dataset of 4.6million rows containing insurance rates for various quotes and their respective insurance company. I have managed to calculate a rank function that ranks each company's rates for each quote (KEY) and average it, keeping in mind filters made on the company name. It looks as follows:
Rank: AVERAGEX(VALUES('Test'[KEY]),RANKX( FILTER(ALLSELECTED('Test'[Company]),[AvgRate]),[AvgRate],,ASC))
AvgRate: AVERAGE('Test'[Rate])
However the rank measure takes really long to calculate to calculate. Looking into its timings in DAX Studio, it takes 39,161ms to run. Can anyone suggest an alternate way to structure my measure such that it takes less time?
Hi @Anonymous ,
You could use VAR() to save the result of an expression as a named variable.
rank =
VAR a =
VALUES ( 'Test'[KEY] )
VAR b =
FILTER ( ALLSELECTED ( 'Test'[Company] ), [AvgRate] )
RETURN
AVERAGEX ( a, RANKX ( b, [AvgRate],, ASC ) )
And you could refer to this video to learn how to optimize your dax.
@v-eachen-msft Thanks for the suggestion however it took even longer in DAX Studio to as the total time to just calculate this measure was 63,723ms
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 |
---|---|
107 | |
98 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |