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

. . .                             . . .                                                . . . 

BhaveshPatel's avatar
BhaveshPatel
Super User
9 years ago

Hi hksl

 

You have applied my formula in calculated column but the formula is for the measure calculation. This is the reason you are getting unexpected results. Practically, Both your distinct count steps and my DAX formula are same aggregations and it must show the same result. I tried and tested.