Forum Discussion

rrwprioste's avatar
rrwprioste
Frequent Visitor
8 years ago
Solved

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         C...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    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's avatar
v-juanli-msft
Community Support
8 years ago

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