Forum Discussion

LemonKing's avatar
LemonKing
Frequent Visitor
1 year ago
Solved

Displaying Missing Name

Hi All,    Need to seek some help from the expert here.    I have tried to search the web and forum and used the DAX to find out and display the missing name between the two excel but it just won...
Need Help
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    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.

LemonKing's avatar
LemonKing
Frequent Visitor
1 year ago

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.

 

rajendraongole1's avatar
rajendraongole1
Icon for Super User rankSuper User
1 year ago

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.