How to find all distinct employee names in an entire table (all columns and rows)

[s_schwantes]

My source data in Excel had roughly 26 columns with EE data. Unfortunately, each column could contain multiple names per cell value. I created a query in PBI to split out to ~ 75 columns with one EE name per column. Next I'm looking to find the following:

1) all distinct names in the entire table

2) count of all the distinct names (how many times does a distinct name appear irregardless of row or column)

3) count of all the rows a distint name appears in & all the columns a distinct name appears in - along with a view of what the those column headers are for each distinct name, i.e., list each distinct name and then each column that name appears in...

Any and all suggestions on best approach are wecome. Sorry I can't post any sample data. Hopefully this will suffice as a general baseline . . . 

Thanks!

Steve

[v-xiaotang]

Hi @s_schwantes 

Have you solved this problem? If yes, could you kindly accept the answer helpful as the solution (or kindly share your solution ). so the others can find it more quickly.
really appreciate!
Any question, please let me know. Looking forward to receiving your reply.

Best Regards,
Community Support Team _Tang

[v-xiaotang]

Hi @s_schwantes 

I have a method, but if you have a lot of columns, it will be a bit cumbersome, you need to keep copying and pasting column name.

1) all distinct names in the entire table

you can create a new table, the new table includes all values, then put it into table visual, the table visual will display distinct values automatically,(I suggest this method for the second step)

Table = UNION(SUMMARIZECOLUMNS(mytablename[Column1]),SUMMARIZECOLUMNS(mytablename[Column2]),SUMMARIZECOLUMNS(mytablename[Column3]),SUMMARIZECOLUMNS(mytablename[Column4]))

 or you can get distinc table directly by adding DISTINCT(),

Table = DISTINCT(UNION(SUMMARIZECOLUMNS(mytablename[Column1]),SUMMARIZECOLUMNS(mytablename[Column2]),SUMMARIZECOLUMNS(mytablename[Column3]),SUMMARIZECOLUMNS(mytablename[Column4])))

result of my created sample,

2) count of all the distinct names (how many times does a distinct name appear irregardless of row or column)

The COUNT function counts the number of cells in a column that contain non-blank values.
Syntax
COUNT(<column>)  

actually, count function acts on a column, not multiple columns, so we need to summarize multiple columns into one column.

based on the first step, create the measure

Count = COUNT('Table'[Column1])

result

3) count of all the rows a distint name appears in & all the columns a distinct name appears in - along with a view of what the those column headers are for each distinct name, i.e., list each distinct name and then each column that name appears in...

count of all the rows a distint name appears in, this seems not very possible in DAX...

count of all the columns a distinct name appears in, this is possible but a little troublesome, you need to write all your column names in your measure...

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[s_schwantes]

Actually, my table has more than just columns with EE names so curious how do I isolate just EE names?

[selimovd]

Hey @s_schwantes ,

in DAX you can count the distinct values:

Amount distinct names =
COUNTROWS(
    DISTINCT( myTable[Name] )
)

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[s_schwantes]

Thanks Denis - trying your suggestion and will circle back asap.

[s_schwantes]

Okay, this works for one column; e.g., = countrows(distinct('mytablename'[column_1])) ... how do I make this work across all the columns of interest in my table? 

Thanks!