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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Sarunas
Frequent Visitor

Count where Rank=1

Hi,

 

I'm looking for a way to make the following analysis work:

 

There is a slicer with Product selection. When specific products are selected, I need one measure (#customers) to show the number of customers selling both products.


Also, I need another measure (CountRank1) to show in how many customers Product A sells better, and in how many Product B sells better.

 

Please find the screenshot with explanation below.

Sarunas_0-1605614785807.png

The desired result is (also, for the measures to work on other visuals, not only the table)

 Product AProduct B
VOL510536
#customers44
CountRank122

 

Please find the pbix sample file in the link below.

https://gofile.io/d/0MQJ51

 

I would really appreciate any help - spent many hours on this and couldn't make it work.

1 ACCEPTED SOLUTION
CNENFRNL
Community Champion
Community Champion

Hi, @Sarunas , you might want to try new measures I authored again. (I attached a new file for it)

Screenshot 2020-11-25 222639.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

6 REPLIES 6
CNENFRNL
Community Champion
Community Champion

Hi, @Sarunas , you might want to try new measures I authored again. (I attached a new file for it)

Screenshot 2020-11-25 222639.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

@CNENFRNL,

 

That was it - I could make the CountRank1 work by recreating your '# Customers overlap' logic and inserting condition in there:

IF( [Product rank] = 1, [Product overlap] +0, 0 )

 

Thanks! Appreciate your help a lot.

CNENFRNL
Community Champion
Community Champion

Hi, @Sarunas , I've so far only worked out how to count customers who sell all selected products; you might want to try a measure like this,

 

# Customers = 
COUNTROWS (
    FILTER (
        DISTINCT ( Sales[Customer ID] ),
        COUNTROWS (
            CALCULATETABLE (
                EXCEPT ( DISTINCT ( Products[Product ID] ), DISTINCT ( Sales[Product ID] ) )
            )
        ) = 0
    )
)
Names = 
VAR __ids =
    FILTER (
        DISTINCT ( Sales[Customer ID] ),
        COUNTROWS (
            CALCULATETABLE (
                EXCEPT ( DISTINCT ( Products[Product ID] ), DISTINCT ( Sales[Product ID] ) )
            )
        ) = 0
    )
VAR __names =
    CALCULATETABLE (
        DISTINCT ( Customers[Customer name] ),
        CALCULATETABLE ( Sales, __ids )
    )
RETURN
    CONCATENATEX ( __names, Customers[Customer name], UNICHAR(10) )

 

Screenshot 2020-11-20 023522.png

 

As to the rank part, it appears harder than expected. I'm also looking forward to solutions by others gurus.


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Hi, @CNENFRNL 

 

Thanks for a different view at the customers count. I tried to play with your suggestion, but couldn't make the Ranking work.

 

# Customers only works as a separate visual - if we add it to the table which includes Products (on columns for example) - it doesn't show the desired result (it shows 7 instead of 4). That could be the key to solving the Ranking problem I believe, but I couldn't make it work.

amitchandak
Super User
Super User

@Sarunas , in rank you are using 'Prod_selection_1v1'[Product name], same should be used on visuals.

In that case rank 1 count is coming 5,5 what seems right as there are 10 customers.

Hi, @amitchandak ,

 

The issue here is - I only need to compare customers which have both products in their assortment. In others words, if at least one Product's VOL = 0, then dismiss this customer.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.