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_Number and select "Count (Distinct)" I get correct number of clubs for each state and column header "Count of Club_Number". I need different column header, for examle "Number of Clubs".

But:

- I don't see option to reame header, is there way to re-name header ?

- when I try to create measure it gives me different-wrong number of clubs, same for each state.

   Measure is: DISTINCT('MYTABLE'[CLUB_NUMBER])

   What is the DAX statement for "Count (Distinct)" (which shows up in popup menuon right-btn-click) ?

 

Many thanks

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,

     

5 Replies

  • BhaveshPatel's avatar
    BhaveshPatel
    Super User
    9 years ago

    Hi hksl

     

    There is no option to rename the table headers.

     

    In your case, You should create a neW measure, which will allow you to customize your header.

     

    Number of Clubs:=DISNTICTCOUNT('MY TABLE'[CLUB_NUMBER])

     

     

    • hksl's avatar
      hksl
      Helper I
      9 years ago

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

      I get one big number for 'Number of Clubs' for each row, sum of clun numbers ?

       

      State         Count of CLUB_NUMBER      Number Of Clubs

      CA                       933                                    2673

      CO                        41                                     2673

      FL                           8                                     2673

      . . .                        . . .                                       . . .

    • 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.