Solved: Get distinct Employee - Page 2

Baskar · ‎06-08-2017

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
@Seth_C_Bauer

@MarcelBeug
@Vvelarde
@MattAllington

Anonymous · ‎06-11-2017

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

View solution in original post

Get distinct Employee

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly