Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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!
Solved! Go to Solution.
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])
Best Regards
Maggie
@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.
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])
Best Regards
Maggie
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
59 | |
57 |