Forum Discussion

ScottKC's avatar
ScottKC
Frequent Visitor
3 years ago
Solved

Aggregating conditional distinct counts

hi all How could I achieve the following in a measure? An aggregate count of distinct Customers who had sales in all Channels (A,B,C)?   Date CustomerName Channel SaleAmount 22/01/2023 L...
  • d_gosbell's avatar
    d_gosbell
    3 years ago

    I think logic like the following should work

    All Channels Count = 
// get the list of customers and count the distinct channels for each customer
var CustChannels =  summarize('Table', 'Table'[CustomerName], "ChannelCount", DISTINCTCOUNT( 'Table'[Channel]))
// get the total distinc channels regardless of the filter context
var distinctChannels =COUNTROWS(DISTINCT(ALL('Table'[Channel])))
// count the customers where the number of channels is the same as to the total distinct channels
return countrows(Filter(CustChannels, [ChannelCount] = distinctChannels))

     

    This is giving me the output on the last column

     

     

amitchandak's avatar
amitchandak
Super User
3 years ago

ScottKC , Try measures like

 

M1 = countrows(Values(Table[CustomerName]))

 

M2 = countx(Values([Channel]), if([M1] >=2, [Channel], blank()))

  • ScottKC's avatar
    ScottKC
    Frequent Visitor
    3 years ago

    Thanks for the reply. It hasn't quite worked for my scenario (I've edited the OP to reflect desried output)... but I'm now trying to use countx() and values() to see if I can modify my measures to suit.