Forum Discussion
Top 10 - DAX formula optimization
Hi Experts,
I try to create a measure to create Top 1o Customer by number of products bought.
I use the following formulas:
Customer Rank = RANKX(ALL(Sales_Fact[CustomerNumber]), [Total Sales], ,ASC)
Top 10 Customers by Prod # =
VAR
RankingContext = VALUES( Account_Dim[Name] )
RETURN
CALCULATE( [No of unique Products],
TOPN( 10, ALL( Account_Dim[Name] ), [No of unique Products] ),
RankingContext ).
The problem is taking around 5 min to display a table with 10 CustomerName and Rank. The tables are not huge: Account aprox 90.ooo rows and Sales aprox 120.000. The memory and cpu used around 30-40%.
Any help would be very much appreciated!
Hi danm
I'd need more details on the model, visual where measures are used etc. Can you share the pbix?
The name for the measure [Top 10 Customers by Prod #] is a bit confusing, given what it seems to do.
Try this in the meantime:
Top 10 Customers by Prod # V2 = VAR rank_ = RANKX ( ALL ( Account_Dim[Name] ), [No of unique Products] ) RETURN IF ( rank_ <= 10, [No of unique Products] )Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
7 Replies
Hi danm
I'd need more details on the model, visual where measures are used etc. Can you share the pbix?
The name for the measure [Top 10 Customers by Prod #] is a bit confusing, given what it seems to do.
Try this in the meantime:
Top 10 Customers by Prod # V2 = VAR rank_ = RANKX ( ALL ( Account_Dim[Name] ), [No of unique Products] ) RETURN IF ( rank_ <= 10, [No of unique Products] )Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
- No of unique Products = DISTINCTCOUNT(Sales_Fact[ProductNumber])
danm , do you have any bi-directional joins. and having a slicer for those bi-directional joins. Make bi-directional join and try .
Small variation in second formula
No of unique Products = DISTINCTCOUNT(Sales_Fact[ProductNumber])
Top 10 Customers by Prod # =
CALCULATE( [No of unique Products],
TOPN( 10, ALL( Account_Dim[Name] ), [No of unique Products] ),
VALUES( Account_Dim[Name] ) )Hi @amitchandak
I activated cross filter direction = both, changed the formula but there is no changes.
Not sure about the slicer
danm , In the model view, check relations and try to convert 1-M relation as single direction relation
Essentially it is doing what you did in a more direct way. Your code was a bit convoluted.
To be quite honest though, I wouldn't expect the improvement to be really big ex ante. I'd need to see the full pbix to be able to provide a more accurate answer.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
