Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
Hi,
Write this calculated column formula
=calculate(distinctcount(Data[company]),filter(data,data[city]=earlier(data[city])))
Hope this helps.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |