Distinct Count based on overlapping values on another row

[Anonymous]

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!

[Ashish_Mathur · Accepted solution]

Hi,

Write this calculated column formula

=calculate(distinctcount(Data[company]),filter(data,data[city]=earlier(data[city])))

Hope this helps.

[Anonymous]

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!

[Ashish_Mathur]

Hi,

Write this measure and see the image below

Measure = CALCULATE(DISTINCTCOUNT(Data[Company]),Data[Company]="Alpha"||Data[Company]="Beta")

Hope this helps.