Forum Discussion
Displaying Missing Name
Hi LemonKing - Your full name fields are not matching at all between the two tables.
you can create a calculated column
Test Match =
IF (
CONTAINS ( 'Training List',
'Training List'[Full Name],
'Staff Listing'[Full Name (by Formula)]
),
"In Training",
"Not in Training"
)best practice should be at power query you can try below suggestions:
On both tables:
Transform → Format → Trim , Transform → Format → Clean,
Transform → Format → Uppercase
try the below measures now to get the count 7
No Training Employees =
EXCEPT (
VALUES ( 'Staff Listing'[Full Name (by Formula)] ),
VALUES ( 'Training List'[Full Name] )
)No Training Count =
COUNTROWS (
EXCEPT (
VALUES ( 'Staff Listing'[Full Name (by Formula)] ),
VALUES ( 'Training List'[Full Name] )
)
)No Training Count =
COUNTROWS (
EXCEPT (
VALUES ( 'Staff Listing'[Full Name (by Formula)] ),
VALUES ( 'Training List'[Full Name] )
)
)Hope this helps.
Hi LemonKing - As per my understanding, if each employee has exactly one row in Training List when registered. If an employee attends multiple trainings, you’ll undercount.
No Training =
COUNTROWS (
EXCEPT (
VALUES ( 'Staff Listing'[IGG] ),
VALUES ( 'Training List'[IGG] )
)
)
or another approach should be:
In Power Query, clean both name fields:
Transform → Format → Trim & Clean
Text.Upper([Full Name]) (force uppercase for both tables)
try the below logic
No Training Employee =
EXCEPT (
VALUES ( 'Staff Listing'[Full Name (by Formula)] ),
VALUES ( 'Training List'[Full Name] )
)
Hope this helps.
Hi rajendraongole1,
Thanks for your time on this.
"No training" measure is just to show the number of staff who didnt register for any training. As long as their name didnt appear in my training list, it will display in my card.
For now, i have 7 staffs who didnt register.
As for the logic that you shared:
No Training Employee =
EXCEPT (
VALUES ( 'Staff Listing'[Full Name (by Formula)] ),
VALUES ( 'Training List'[Full Name] )
)
I have tried but its not working. it show the list of all the staffs and not just the missing staffs.
Hi LemonKing - Your full name fields are not matching at all between the two tables.
you can create a calculated column
Test Match =
IF (
CONTAINS ( 'Training List',
'Training List'[Full Name],
'Staff Listing'[Full Name (by Formula)]
),
"In Training",
"Not in Training"
)best practice should be at power query you can try below suggestions:
On both tables:
Transform → Format → Trim , Transform → Format → Clean,
Transform → Format → Uppercase
try the below measures now to get the count 7
No Training Employees =
EXCEPT (
VALUES ( 'Staff Listing'[Full Name (by Formula)] ),
VALUES ( 'Training List'[Full Name] )
)No Training Count =
COUNTROWS (
EXCEPT (
VALUES ( 'Staff Listing'[Full Name (by Formula)] ),
VALUES ( 'Training List'[Full Name] )
)
)No Training Count =
COUNTROWS (
EXCEPT (
VALUES ( 'Staff Listing'[Full Name (by Formula)] ),
VALUES ( 'Training List'[Full Name] )
)
)Hope this helps.