topic Sum of unique values in multiple columns in DAX Commands and Tips

Sum of unique values in multiple columns

maneschr2022 — Wed, 13 Apr 2022 00:41:33 GMT

Hi, I have 3 columns that I would like to sum their unique values and have a total number of users, for this example, the output should be 9, as Tom is repeated.

I tried to use a formula for a similar example situation but it gave me fewer numbers for some reason.
This is the formula that I tried:

Count Total Users= COUNTROWS(SUMMARIZE(Table[ User Level 1],[ User Level 2]Table[ User Level 3]))

Thanks for your help!

Re: Sum of unique values in multiple columns

Whitewater100 — Wed, 13 Apr 2022 00:58:55 GMT

Hi:

If you don't mind using transform data and higlight the first column and unpivot others you get:

Then this measure will result in nine for the count.

User CT = DISTINCTCOUNT('Table'[Value])

I hope this helps!

Re: Sum of unique values in multiple columns

Jihwan_Kim — Wed, 13 Apr 2022 03:00:40 GMT

Hi,

Please check the below picture and the attached pbix file.

Total users count: = VAR newtable = FILTER ( SUMMARIZE ( UNION ( VALUES ( Data[UserLevel1] ), VALUES ( Data[UserLevel2] ), VALUES ( Data[UserLevel3] ) ), Data[UserLevel1] ), Data[UserLevel1] <> BLANK () ) RETURN COUNTROWS ( newtable )

Re: Sum of unique values in multiple columns

maneschr2022 — Wed, 13 Apr 2022 05:27:05 GMT

Hi, Thanks for your help, it works fine that way is a bit of a work around with tables, so I`ll go for the DAX formula