Sum of unique values in multiple columns

[maneschr2022]

 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!

[Jihwan_Kim · Accepted solution]

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 )

[Whitewater100]

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!

[maneschr2022]

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