Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 numbe...
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

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

CNENFRNL's avatar
CNENFRNL
Icon for Community Champion rankCommunity Champion
5 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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.