Forum Discussion

LemonKing's avatar
LemonKing
Frequent Visitor
11 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 wont display the missing name. Instead it display the full listing.... 

 

I have tried this by creating a new table and i tried to link the relationship for filter too: 

 

No Training Employee =
EXCEPT(
    CALCULATETABLE(DISTINCT('Staff Listing'[Full Name (by Formula)])),
    CALCULATETABLE(DISTINCT('Training List'[Full Name]))
)
 
and this: 
 
No Training Employee =
FILTER('Staff Listing',NOT(CONTAINS('Training List','Training List'[Full Name], 'Staff Listing'[Full Name (by Formula)])))
 
Both return full stafflisting name. Not sure where im missing in both DAX. 
I have another DAX to display the number of staff who didnt register for any training:
 
No Training =
(DISTINCTCOUNT('Staff Listing'[IGG])-DISTINCTCOUNT('Training List'[IGG]))+0
 
Can someone guide me on this to show where im wrong?
 
Thank you 
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.

12 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    11 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
      11 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..

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    11 months ago

    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.

    • LemonKing's avatar
      LemonKing
      Frequent Visitor
      11 months 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
        Super User
        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.

  • sivarajan21's avatar
    sivarajan21
    Post Prodigy
    11 months ago

    Hi LemonKing 

     

    Tried and created below sample data and tested dax:

    Staff Listing Table:

    Full Name

    Alice Smith
    Bob Jones
    Carol Brown
    David White

     

    Training List:

    Full Name

    Alice Smith
    Carol Brown

     

    Result:

     

    Dax:

    No Training Employee =
    EXCEPT (
        DISTINCT ( 'Staff Listing'[Full Name] ),
        DISTINCT ( 'Training List'[Full Name] )
    )
     
    Is this your expected output?
    Please let me know!
     
    Best Regards,
  • LemonKing's avatar
    LemonKing
    Frequent Visitor
    11 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
      11 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

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