Forum Discussion
Distinct Count based on 2 columns in same table
I'm not sure I understand the block here. What's keeping you from filtering on CustCount? It was very easy to add this filter:
If you're trying to use this value in a slicer, just add calculated columns to your data and use those instead:
CustCountColumn = CALCULATE( COUNTROWS(RawData), FILTER(RawData, RawData[Address]=EARLIER(RawData[Address]) && RawData[Customer] = EARLIER(RawData[Customer])))
CustCountCol = CALCULATE( COUNTROWS(RawData), FILTER(RawData, RawData[Customer] = EARLIER(RawData[Customer])))
This is very easy to do in PowerBI once you understand the power of context.
First, I added the sample data you provided, then I created a basic table visual. I added Customer and Address to the table. Then I added customer to the table again, but clicked the dropdown and chose to summarize it as a Count this time. That gave me the Cust-AddrCount you wanted.
The harder one was the CustCount. Since the data is already grouped by Customer-Address in this visual, you have to manually remove the filters via DAX. I created a measure called CustCount and added it to the table:
CustCount = CALCULATE(COUNT(RawData[Customer]), ALLEXCEPT(RawData,RawData[Customer]))
That gave me the exact results you were looking for. Here's my .pbix file so you can play around with it. https://drive.google.com/open?id=1JHQDsZVqCrbHuGBew4sVwwTkd4A0Zd4e
If you have further questions about context or the ALLEXCEPT function, feel free to ask.
Thanks Cmcmahan !
I apologize for failing to mention this requirement - but the adding of the customer name to the table and choosing Count doesn't work for me becuase I want to be able to filter that value. For example, I'd like to filter out all Cust-Addr pairs which have a count of 3.
Should I consider simply adding a calculated column on the PowerQuery side of things? If so, would you have any insight into that?
I'm not sure I understand the block here. What's keeping you from filtering on CustCount? It was very easy to add this filter:
If you're trying to use this value in a slicer, just add calculated columns to your data and use those instead:
CustCountColumn = CALCULATE( COUNTROWS(RawData), FILTER(RawData, RawData[Address]=EARLIER(RawData[Address]) && RawData[Customer] = EARLIER(RawData[Customer])))
CustCountCol = CALCULATE( COUNTROWS(RawData), FILTER(RawData, RawData[Customer] = EARLIER(RawData[Customer])))
Those two formulae are exactly what I was looking for, Cmcmahan , thanks! I was able to use those two and divide them, then filter out the columns that are 100% matches.