Forum Discussion
Solved
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...
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]
)
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