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.

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

  • v-menakakota's avatar
    v-menakakota
    Community Support
    10 months ago

    Hi  LemonKing  ,
    Thanks for reaching out to the Microsoft fabric community forum. 

     

    The issue comes from the names not being recorded in the same format across both files, which is why the DAX using names always shows the full list. The best way forward is to compare based on Staff ID since that field is consistent and unique.

    Once you find the missing IDs, you can always pull the staff names from the Staff Listing table, so you’ll still be able to display the actual names of employees who didn’t register for training.

    In general, it’s a good idea to use Staff ID for the logic, and then just use names as labels for reporting. That way you won’t have to clean up names every time.

     

    Best Regards, 
    Community Support Team 

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      10 months ago

      Hello LemonKing  ,

       

      I am also part of CST Team and we’d like to confirm whether your issue has been successfully resolved. If you still have any questions or need further assistance, please don’t hesitate to reach out. We’re more than happy to continue supporting you.

       

      Regards,

      B Manikanteswara Reddy

      • v-menakakota's avatar
        v-menakakota
        Community Support
        10 months ago

        Hi LemonKing ,

        I hope the below details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you 

         

        If I misunderstand your needs or you still have problems on it, please feel free to let us know.   

        Best Regards, 
        Community Support Team  

  • sivarajan21's avatar
    sivarajan21
    Post Prodigy
    10 months 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,