Get distinct Employee

[Baskar]

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

[eromrab]

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

[v-jiascu-msft]

Hi @Baskar,

@Anonymous's post works. Please mark it as answer if it resolved your problem. Or any posts that works. 

Best Regards!

Dale

[v-jiascu-msft]

@Baskar

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

[Greg_Deckler]

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?

[Baskar]

@Greg_Deckler yes my dear friend you are 100 % correct.

[Greg_Deckler]

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.

[Baskar]

Thanks for your valuable time @Greg_Deckler.

i have to mention one thing here that employee list i put it here some sample data. it could be change for every name.

if it is only 4 employee i can use your method, but my case is different.

My case is very worst, think how i locked . feel very sad about me in this case. 

[Anonymous]

EDIT: NOPE that was wrong.  I'll try again.

[Anonymous]

I feel like i'm getting close.  I've been able to create a calculated table that contains a column with each of the values between your /.  The only problem is i've been unable to plug this calculated column into DISTINCTCOUNT.  I can use COUNTX for it.  I'll post my work so far and hopefully someone else can get it over the line.

STEP 1.  Create a dummytbl that is a single column,  Rows are the values: 1, 2, 3, ...., <The upper limit to be held in column of Employee Names>.

Step 2. Here is my count measure:

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

 (Editted in fixed version)

[Anonymous · Accepted solution]

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