Forum Discussion

hksl's avatar
hksl
Helper I
9 years ago
Solved

Count (Distinct): DAX equivalent or rename table header

I have table visualization with 2 columns. First column: "State", another column should be number of clubs  for each state. There is field in table "Club_Number". When I use right-click-on-Club_Numb...
Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    hksl

     

    In this scenario, since you want to calculate the distinct count within each State, you should have your calculation group on "State". Please use formula like:

     

    Number of Clubs:=CALCULATE(DISNTICTCOUNT('MY TABLE'[CLUB_NUMBER]),ALLEXCEPT('MY TABLE','MY TABLE'[State]))

     

    Regards,

     

hksl's avatar
hksl
Helper I
9 years ago

Thank you, but   Number of Clubs:=DISNTICTCOUNT('MY TABLE'[CLUB_NUMBER])   does not work.

It seems to produce sum of club numbers ?

 

State                 Count of CLUB_NUMBER            Number of Clubs

CA                           933                                              2733

CO                             41                                              2733

FL                                 8                                             2733

. . .                             . . .                                                . . . 

v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
9 years ago

hksl

 

In this scenario, since you want to calculate the distinct count within each State, you should have your calculation group on "State". Please use formula like:

 

Number of Clubs:=CALCULATE(DISNTICTCOUNT('MY TABLE'[CLUB_NUMBER]),ALLEXCEPT('MY TABLE','MY TABLE'[State]))

 

Regards,