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])))
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.