Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Counting unique values in a column

Hi all,

 

Looking for help with something that I thought would be quite simple, but doesn't appear to be working for me.

 

I have the below table: 

COMPANYLOCATIONJOB TITLE

Apple

SpainData Analyst
MicrosoftFranceSoftware Dev
FacebookSpainData Analyst
ARMIrelandData Analyst
IntelUKData Analyst
GoogleUSACEO
AmazonJapanCOO

 

I would like to get a distinct count of the number of companies and the number of locations, ie the number of times each one appears at least once. In the case of location it would be 6, for company it would be 7.

 

I thought I could do this using DISTINCTCOUNT() or COUNTROWS(DISTINCT()) but neither are working for me. Would anyone have any suggestions?

 

Thanks!

  • besomebody20's avatar
    besomebody20
    3 years ago

    Hi Anonymous ,

     

    Here are a couple of measures you can try

     

    Distinct Count that will include BLANK rows

    Company Distinct Count = COUNTROWS(VALUES('Table'[COMPANY]))

     

    Distinct Count that will exclude BLANK rows

    Company Distinct Count = COUNTROWS(ALLNOBLANKROW('Table'[COMPANY]))

2 Replies

  • besomebody20's avatar
    besomebody20
    Icon for Resolver I rankResolver I
    3 years ago

    Hi Anonymous ,

     

    Here are a couple of measures you can try

     

    Distinct Count that will include BLANK rows

    Company Distinct Count = COUNTROWS(VALUES('Table'[COMPANY]))

     

    Distinct Count that will exclude BLANK rows

    Company Distinct Count = COUNTROWS(ALLNOBLANKROW('Table'[COMPANY]))
  • v-yadongf-msft's avatar
    v-yadongf-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi Anonymous ,

     

    Has your problem been solved? If solved, please consider Accept it as the solution to help the other members find it more quickly.

     

    Best regards,

    Yadong Fang