Forum Discussion
Resident RockstarGet 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]
)
Community ChampionHow 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.
Resident RockstarThanks 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.
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)
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]
)