Forum Discussion
Get distinct Employee
- Anonymous9 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]
)
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]
)