Forum Discussion
Get distinct Employee
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]
)
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?
Greg_Deckler yes my dear friend you are 100 % correct.
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.
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.
EDIT: NOPE that was wrong. I'll try again.