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 Sivarajan21,

 

Thanks for your help on this too. 

 

Hi all, 

I think i know what is the issue already. Both the name in full name are not constant. Meaning i have same person but the name is recorded differently in different excel, thats why it is not showing only the missing person. 

 

But if i use all the three DAX that you shown to me, its working if i use it on staff ID rather than name since staff ID is more uniform. But how can i show the name of the missing staff since its only showing staff ID now. 

i can clean up the name in the both the excel to be the same for now but i cant be doing it everytime one by one.... 

 

Is there a way to show the name from one of the excel after comparing both excel using staff ID?

sivarajan21's avatar
sivarajan21
Icon for Post Prodigy rankPost Prodigy
1 year ago

Hi LemonKing ,

 

I got your point!

PFA dax and result:

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

 

Is this your expected output? you have to make sure your staff id is primary key in both tables

Please let me know!

 

Best regards,