Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape 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.
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 |
Please find the pbix sample file in the link below.
I would really appreciate any help - spent many hours on this and couldn't make it work.
Solved! Go to Solution.
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! |
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! |
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.
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! |
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.
@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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
User | Count |
---|---|
21 | |
21 | |
21 | |
13 | |
12 |
User | Count |
---|---|
43 | |
28 | |
25 | |
23 | |
22 |