Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
rrwprioste
Frequent Visitor

Aggregation of Distinct Count Measure

Hello,

 

I need to count how many teams have only one city associated with at least one employee. My data set has many columns, but the relevants for this case are the following:

 

Team         City             Employee

A            Shanghai              3

A               Beijing               5

B               Helsinki             1

B               Beijing               0

C              Berlin                 10

D              Shanghai            5

D                Berlin                6

 

In the example above this count would be 2 (B and C), and I managed to create a measure that counts how many associated cities each team has using:

# of Cities = CALCULATE(DISTINCTCOUNT(Data[City]);FILTER('Data';Data'[Employee]>0))

 

In order to check which teams have employees in only one city I tried to use the simple measure:

Total Team w/ one city = IF( [# of Cities] = 1 ; 1 ; 0 ) , but that is not aggregate-able.

 

Any ideas on how to do it? I'd prefer to have it as a measure instead of new calculated column so I can slice the results according to other columns in the dataset.

 

 

Thanks in advance!

 

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @rrwprioste

Try these measures

# of Cities = IF(MAX([Employee])>0,CALCULATE(DISTINCTCOUNT(Data[City]),FILTER(ALL(Data),[Employee]>0&&[Team]=SELECTEDVALUE(Data[Team]))))

which Team w/ one city = IF( [# of Cities] = 1,1,0)

how many teams have only one city = 
SUMX(ALL(Data),[which Team w/ one city])

10.png

 

Best Regards

Maggie

View solution in original post

2 REPLIES 2
nevajah
New Member

@v-juanli-msft  Hello, I found this example today and it is similar to the problem that i need to solve. How would i need to adjust the formula in above example if i need to calculate the number of teams associated with Berlin only. The result should be 1. I do understand the logic but fail to write it into a formula. It is a distinct count of teams, but combined with a check within a team that city is "Berlin" and nothing else. Thank you for your help. 

v-juanli-msft
Community Support
Community Support

Hi @rrwprioste

Try these measures

# of Cities = IF(MAX([Employee])>0,CALCULATE(DISTINCTCOUNT(Data[City]),FILTER(ALL(Data),[Employee]>0&&[Team]=SELECTEDVALUE(Data[Team]))))

which Team w/ one city = IF( [# of Cities] = 1,1,0)

how many teams have only one city = 
SUMX(ALL(Data),[which Team w/ one city])

10.png

 

Best Regards

Maggie

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.