topic Re: Count where Rank=1 in DAX Commands and Tips

Count where Rank=1

Anonymous — Wed, 18 Nov 2020 13:10:46 GMT

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.

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

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

Re: Count where Rank=1

amitchandak — Wed, 18 Nov 2020 15:20:33 GMT

Anonymous , 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.

Re: Count where Rank=1

Anonymous — Wed, 18 Nov 2020 15:32:22 GMT

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.

Re: Count where Rank=1

CNENFRNL — Fri, 20 Nov 2020 01:36:52 GMT

Hi, Anonymous , 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.

Re: Count where Rank=1

Anonymous — Tue, 24 Nov 2020 11:40:49 GMT

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.

Re: Count where Rank=1

CNENFRNL — Wed, 25 Nov 2020 21:27:28 GMT

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

Re: Count where Rank=1

Anonymous — Thu, 26 Nov 2020 14:00:59 GMT

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.