Forum Discussion

LemonKing's avatar
LemonKing
Frequent Visitor
10 months 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
    10 months 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.

bhanu_gautam's avatar
bhanu_gautam
Super User
10 months ago

LemonKing 

Add calculated columns to both tables to trim and lowercase the names:

Clean Name = LOWER(TRIM([Full Name (by Formula)]))

 

Create a calculated table:

No Training Employee =
EXCEPT(
SELECTCOLUMNS('Staff Listing', "Name", 'Staff Listing'[Clean Name]),
SELECTCOLUMNS('Training List', "Name", 'Training List'[Clean Name])
)

 

If you want to display the actual names (not just the cleaned version), you can use LOOKUPVALUE to get the original name from the cleaned name.

LemonKing's avatar
LemonKing
Frequent Visitor
10 months ago

Hi Bhanu_gautam, 

 

Thanks for replying on this. 

 

I have tried your calculated column too but its also showing full stafflisting instead of the missing namelist. I have 7 staff who didnt register for any training/not shown in my excel but the calculated table is not showing the missing staffs. it seems except might be the solution to my issue but something is wrong here that its not showing the missing but rather the full listing..