cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

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!

 

1 ACCEPTED SOLUTION
Super User III
Super User III

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 

SU18_powerbi_badge

View solution in original post

7 REPLIES 7
Super User III
Super User III

@danm 

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 

SU18_powerbi_badge

 

 

Helper I
Helper I

Thank you @AlB !

Is simple and fast ! Great solution. We all appreciate if you add some comments to point us in right direction.

 

Cheers

Super User III
Super User III

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 

SU18_powerbi_badge

View solution in original post

Super User IV
Super User IV

@danm , In the model view, check relations and try to convert  1-M relation as single direction relation



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helper I
Helper I

Hi @

 

Not sure about the slicer

Super User IV
Super User IV

@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] ) )



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helper I
Helper I

No of unique Products = DISTINCTCOUNT(Sales_Fact[ProductNumber])

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.