cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

1 ACCEPTED SOLUTION
Super User

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 4
New Member

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

Super User

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

Super User

@ScottKC , Try measures like

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

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

Frequent Visitor

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors