Been thinking about how to do this using MDX and came up with something that seems to work:
My Employee dimension looks like the below:
Note Jane Doe repeats twice and Bob Martin 3 times.
The following code essentially enumerates each member and counts a new set where the full name is matched using a wildcard search (full match in this case)
Where the count is greater than 1, we keep that member in a new set and then display this set on rows with the count of members with the same name.
The set is also ordered to keep the repeated names together instead of by employee id.
WITH
SET [AllEmployees] AS
NONEMPTY([Employee].[Employee].MEMBERS)
MEMBER [}ElementAttributes_Employee].[SharedNames] AS
COUNT(
TM1FilterByPattern(
{[Employee].[Employee].MEMBERS},
[AllEmployees].CURRENTMEMBER.PROPERTIES("Full Name"),
"Full Name")
)
SET [EmployeesWithSharedNames] AS
FILTER(
[AllEmployees],
[}ElementAttributes_Employee].[SharedNames] > 1
)
SELECT
{[}ElementAttributes_Employee].[Full Name], [}ElementAttributes_Employee].[SharedNames]} ON 0,
{ORDER(
[EmployeesWithSharedNames],
[EmployeesWithSharedNames].CURRENTMEMBER.PROPERTIES("Full Name"),
BASC)} ON 1
FROM [}ElementAttributes_Employee]
The result is as follows:
Note that I used the Employee attributes cube as base as this made the most sense for what is needed.
This is possibly a complex solution, or more so than a dump to Excel but may be useful to others looking to solve a similar problem in the future.
------------------------------
George Tonkin
Business Partner
MCI Consultants
Johannesburg
------------------------------
Original Message:
Sent: Fri August 02, 2024 04:09 AM
From: Mark Wragg
Subject: same element names with different IDs
Hi,
One way would be to open the Employee attribute cube in PAX to do a countif on the relevant attribute, i.e. the attribute which holds the name and then use the excel spill functions (filter, unique, sort) to create a unique list of the employee names and a count of their occurrences.
e.g. =SORT(UNIQUE(FILTER(B1:C20,C1:C20 >1)))
(assuming column B held the names and column C the countif formula).
If you want to hold this information in a cube then I think you would have to create a new dimension that holds the names and use TI to loop over the employees and insert logic that would count the occurrences, factoring in whether the names were exactly identical etc.
regards,
Mark
------------------------------
Mark Wragg
------------------------------