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)?

 

DateCustomerNameChannelSaleAmount
22/01/2023LisaA$2.00
22/02/2023LisaB$20.00
22/03/2023LisaC$200.00
05/03/2023BartB$5.00
06/03/2023MaggieB$7.00
10/03/2023HomerC$100.00
15/03/2023MaggieA$7.00
22/03/2023BartC$0.25
22/03/2023BartB$8.00
22/03/2023BartB$9.00
22/03/2023MaggieC$17.00
22/03/2023MaggieC$17.00

 

Quite simple to get the distinct count of customers = 4

 

I can also get the distinct count of customers for each Channel:

A = 2 customers (Lisa and Maggie)

B = 3 customers (Lisa, Bart and Maggie)

C = 4 customers (Lisa, Homer, Bart and Maggie)

 

But am struggle to the distinct count of customers who have a record in each channel = 2 (Lisa and Maggie)

 

EDIT:

The desired output would look like this:

 

CustomerNameChannel A flagChannel B flagChannel C flagAll Channels flag
Lisa1111
Bart0110
Maggie1111
Homer0010
TOTALS2342

 

Any suggestions?

 

  • 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

     

     

4 Replies

  • 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

     

     

  • 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.

  • ninjaryder1st's avatar
    ninjaryder1st
    New Member
    3 years ago

    I have a similar challenge:

     

    Trying to count unique names (same people repeatedly occur in table rows) in a table based on some other column's categorical status:

     

    Homer

    Marge

    Maggie

    Bart
    Maggie 

    Maggie

    Bart

     

    They all have a categorical status of "Red, Blue, or Green"  and I wanted to count each employee only once, that hs "red" status.      Can't seem to find the DAX expression for this, or steps to create columns or measures to do this.  

    Thanks