Forum Discussion

pmetaliya's avatar
pmetaliya
Icon for Advocate I rankAdvocate I
5 years ago
Solved

Calculate distinct count total at a group level

Hi,  I'm trying to create a measure to get the "Other" count for each ethnicity but having trouble calculating total distinct count group by Ethnicity. There is 1:M relationship between Person(Ethni...
DAX
Distinct Counts
percentage
  • wdx223_Daniel's avatar
    wdx223_Daniel
    5 years ago
    # Incident = CALCULATE(DISTINCTCOUNT('Behavior'[DISCIPLINE_INCIDENT_ID]))

# Incident All 1 = 
SUMX(
    VALUES('Person'[Ethnicity]),
    [# Incident])

# Incident Other 1 = VAR _all=CALCULATE([# Incident All 1],ALL(Person[Ethnicity])) RETURN IF([# Incident All 1],_all-[# Incident All 1])

     

wdx223_Daniel's avatar
wdx223_Daniel
Icon for Community Champion rankCommunity Champion
5 years ago
# Incident = CALCULATE(DISTINCTCOUNT('Behavior'[DISCIPLINE_INCIDENT_ID]))

# Incident All 1 = 
SUMX(
    VALUES('Person'[Ethnicity]),
    [# Incident])

# Incident Other 1 = VAR _all=CALCULATE([# Incident All 1],ALL(Person[Ethnicity])) RETURN IF([# Incident All 1],_all-[# Incident All 1])

 

pmetaliya's avatar
pmetaliya
Icon for Advocate I rankAdvocate I
5 years ago

Wow. Thanks, Daniel. Your measures are working as expected.