Forum Discussion

Baskar's avatar
Baskar
Resident Rockstar
9 years ago
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...
  • Anonymous's avatar
    Anonymous
    9 years ago

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