distinct count and group by

[fiorela]

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

[divsforeal]

hey

How to get this in power Query.

Thanks

[PaulDBrown]

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

[fiorela]

thank you so much !!! works too

[az38 · Accepted solution]

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

[osandrolucas]

This measure works to me.

Thanks

[fiorela]

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