cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Slow DAX measure

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?

2 REPLIES 2
Highlighted
Community Support
Community Support

Re: Slow DAX measure

Hi @jayeshmenon ,

 

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.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
Highlighted
Frequent Visitor

Re: Slow DAX measure

@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

Helpful resources

Announcements
June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors