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

     

     

d_gosbell's avatar
d_gosbell
Super User
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