cancel
Showing results 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

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.

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

 Product A Product B VOL 510 536 #customers 4 4 CountRank1 2 2

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
Community Champion

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

 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!
6 REPLIES 6
Community Champion

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

 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!
Frequent Visitor

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.

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) )``````

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!
Frequent Visitor

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.

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.

Frequent Visitor

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.