Count (Distinct): DAX equivalent or rename table header

[hksl]

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

[BhaveshPatel]

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]

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 · Accepted solution]

@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,

[BhaveshPatel]

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.

[hksl]

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

. . .                        . . .                                       . . .