Forum Discussion
Distinct Count based on overlapping values on another row
Greetings!
I want to create a measure that gives me the number of cities that have a store overlap for the Companies, with a given table:
| City | Company | Expected result |
| New York | Alpha | 2 |
| New York | Alpha | 2 |
| New York | Alpha | 2 |
| New York | Beta | 2 |
| New York | Beta | 2 |
| Buffalo | Gama | 1 |
| Houston | Beta | 1 |
| San Francisco | Alpha | 1 |
For example: New York have 2 distinct values for Companies (ALPHA and BETA), so I want the measure to return me the value '2'. I can't use a 'countif(company = alpha)' + 'countif(company = beta)' because it would return '5'. I can't also use a 'CountIf(company = alpha && beta)' because there's no row like this.
Is there a way to use DISTINCTCOUNT to make this work? I managed to do this creating dummies for the City*Company, but I bet there's a smarter way to do it.
PS: please let me know if there's a better way to name this issue, or if there's similar solution for this matter. I did find something close to this here but it solves the issue on Power Query.
Thanks in advance!
Hi,
Write this calculated column formula
=calculate(distinctcount(Data[company]),filter(data,data[city]=earlier(data[city])))
Hope this helps.
3 Replies
Hi,
Write this calculated column formula
=calculate(distinctcount(Data[company]),filter(data,data[city]=earlier(data[city])))
Hope this helps.
Hi Ashish, thank you so much for the prompt response!
This works, it returns the number of companies that are present in each city! Another question followed my analysis afterwards.
Is there a way to mark with a measure/dummy all the cities where Alpha and Beta are present? So, for the table below, DAX would return me 3 (New York, Miami, and Tampa).
City Company New York Alpha New York Beta New York Alpha Denver
Beta Denver Gama
Salt Lake City Gama
Salt Lake City Alpha
Buffalo Alpha
Buffalo Alpha
Miami Beta
Miami Alpha
Tampa Beta Tampa Alpha Tampa Beta I tried writing =calculate(distinctcount(Data[company]),filter(data, data[Company] = "Alpha" || data[Company] = "Beta"),filter(data,data[city]=earlier(data[city])))), but it didn't return the expected result.
Thank you!
Hi,
Write this measure and see the image below
Measure = CALCULATE(DISTINCTCOUNT(Data[Company]),Data[Company]="Alpha"||Data[Company]="Beta")Hope this helps.