Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
danm
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
AlB
Super User
Super User

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
AlB
Super User
Super User

@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

 

 

danm
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

AlB
Super User
Super User

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

amitchandak
Super User
Super User

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

danm
Helper I
Helper I

Hi @

 

Not sure about the slicer

amitchandak
Super User
Super User

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

danm
Helper I
Helper I

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors