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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

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.

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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