Forum Discussion

fiorela's avatar
fiorela
Icon for Helper I rankHelper I
6 years ago
Solved

distinct count and group by

Hi there !

 

i would like your help on this. I dont know how to search and how to make the following calculation.

 

I have the following columns:

User   Session  Category

Juan    A                 Z
Maria  B                  Y 

Juan    C                 X

Juan    C                 X

Juan    A                 Y

Maria  C                  X 
Maria  B                  X 

so i would like to count by user, how many DISTINCT CATEGORY they have. For that i've created an unique user column, usar + session:

uniqueuser   countDISTINCTCategory

JuanA                 2

JuanC                  1

MariaB                 2

MariaC                 1

 

i think that i have to use group by, or something like that (DAX function). Any suggestions?

 

Thanks

Need Help
  • az38's avatar
    az38
    6 years ago

    Hi fiorela 

    you dont need any additional columns, just try a measure

    countDISTINCTCategory = calculate(DISTINCTCOUNT('Table'[Category]), ALLEXCEPT('Table', 'Table'[User]))

     

    do not hesitate to give a kudo to useful posts and mark solutions as solution

     

6 Replies

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi fiorela 

    you dont need any additional columns, just try a measure

    countDISTINCTCategory = calculate(DISTINCTCOUNT('Table'[Category]), ALLEXCEPT('Table', 'Table'[User]))

     

    do not hesitate to give a kudo to useful posts and mark solutions as solution

     

    • fiorela's avatar
      fiorela
      Icon for Helper I rankHelper I
      6 years ago

      let me try this, and i will let you know if that works. Thanks in advance

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    6 years ago

    fiorela 

     

    All you need is this measure;

    Number of categories = DISTINCTCOUNT('DataTable CatUser'[Category])

     

    And you can then create a table or matrix to display the breakdown by User or by User/Session: