topic Re: Aggregating conditional distinct counts in DAX Commands and Tips

Aggregating conditional distinct counts

ScottKC — Thu, 23 Mar 2023 03:26:18 GMT

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	Lisa	A	$2.00
22/02/2023	Lisa	B	$20.00
22/03/2023	Lisa	C	$200.00
05/03/2023	Bart	B	$5.00
06/03/2023	Maggie	B	$7.00
10/03/2023	Homer	C	$100.00
15/03/2023	Maggie	A	$7.00
22/03/2023	Bart	C	$0.25
22/03/2023	Bart	B	$8.00
22/03/2023	Bart	B	$9.00
22/03/2023	Maggie	C	$17.00
22/03/2023	Maggie	C	$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:

CustomerName	Channel A flag	Channel B flag	Channel C flag	All Channels flag
Lisa	1	1	1	1
Bart	0	1	1	0
Maggie	1	1	1	1
Homer	0	0	1	0
TOTALS	2	3	4	2

Any suggestions?

Re: Aggregating conditional distinct counts

amitchandak — Thu, 23 Mar 2023 01:52:51 GMT

ScottKC , Try measures like

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

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

Re: Aggregating conditional distinct counts

ScottKC — Thu, 23 Mar 2023 03:43:27 GMT

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.

Re: Aggregating conditional distinct counts

d_gosbell — Mon, 27 Mar 2023 00:18:45 GMT

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

Re: Aggregating conditional distinct counts

ninjaryder1st — Mon, 22 May 2023 20:53:28 GMT

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