Forum Discussion
Get distinct Employee
Dear Power BI Champions,
Help me on this case, here i have attached the employee table.
Goal :
How to get the distinct employee in the table. Only using DAX , not in Power Query
My Approach :
Count Employees = SUBSTITUTE(CONCATENATEX(VALUES(Table1[Employees]),Table1[Employees]),"/",BLANK())
Now am getting the total employee vount like "16".
Please help me to reach my goal. Thanks Advanced.
Employee Table
Greg_Deckler
Anonymous
Solved it! Missed a summarize!
EDIT: Added code comments for those that wish to follow along. Its complex!EmployeeCount = COUNTX( //This is the row that does the count
SUMMARIZE( //This will make the distinct values in our column
ADDCOLUMNS( //This creates the calculated column of our Employee Names
FILTER( //This cuts down the dummy table to only be the size of the number of Names we have
CROSSJOIN( //This Merges our Dummy Table with the Employee Names
SUMMARIZE( //This creates each 'Employee Name' row
Table1,
Table1[Employees],
Table1[Name],
"NamesCnt",
1 + len(Table1[Employees]) - len(SUBSTITUTE(Table1[Employees], "/", "")) //Count of Slashes
),
DummyTbl
),
DummyTbl[Dummy] <= [NamesCnt]
),
"SubName",
PATHITEM( // This function splits up the Employee names to be placed in each row
SUBSTITUTE(Table1[Employees], "/", "|"),
DummyTbl[Dummy]
)
),
[SubName]
),
[SubName]
)
10 Replies
Hey Baskar, so that I understand what you are going for, in the table below, each Named person has 4 employees. But, some of those employees overlap. In the example below, the correct answer you are looking for is 9?
Greg_Deckler yes my dear friend you are 100 % correct.
How about this?
Column = LEFT([Employee],1) Column 2 = RIGHT([Employee],1) Column 3 = MID([Employee],3,1) Column 4 = MID([Employee],5,1) Table = CALCULATETABLE(DISTINCT((UNION(DISTINCT(Employees[Column]),DISTINCT(Employees[Column 2]),DISTINCT(Employees[Column 3]),DISTINCT(Employees[Column 4]))))) Measure = DISTINCTCOUNT('Table'[Column])You probably do not really need the individual DISTINCT's on the columns, you could just UNION them all together and then do the DISTINCT, probably less overhead that way.
Hi,
It seems that you already have the answer. I still want to share my solution. If you had a table of all the employees, you could use this formula as a calculated column.
CountEmp = SUMX ( 'AllEmployees', IF ( FIND ( CONCATENATE ( "/", CONCATENATE ( 'AllEmployees'[Employee], "/" ) ), CONCATENATE ( CONCATENATE ( "/", CONCATENATEX ( 'Table1', 'Table1'[Employees], "/" ) ), "/" ), 1, 9999 ) <> 9999, 1, 0 ) )Best Regards!
Dale
Hi Baskar,
Anonymous's post works. Please mark it as answer if it resolved your problem. Or any posts that works.
Best Regards!
Dale
I think this is much simpler...
In Home, drop down "New Source," then click "Blank Query"
Type in this formula:
= List.Distinct(Table[Column])
Then in the "Transform" ribbon, click "To Table"
Done
