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]
)
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