Forum Discussion

EmanuelParnaiba's avatar
EmanuelParnaiba
Frequent Visitor
1 year ago
Solved

One to Many relationship is created but doesn't work

I have two tables:   dimRoster: contains information about employees, their leaders, and the project they work on (comes from Excel) factCoachDatabase: includes information on coaching sessions (c...
Need Help
Tutorial Requests
  • andrewsommer's avatar
    andrewsommer
    1 year ago

    Check for Hidden Non-Printable Characters; may be the root cause. Excel may handle them well, but Power BI (DAX engine) won’t match strings with trailing spaces, non-breaking spaces (CHAR(160)), or Unicode anomalies.

     

    Create a calculated column in both tables that trims and cleans the text:

    CleanedCoachee = TRIM(CLEAN([CoacheeName]))


    Then create a new relationship based on CleanedCoachee. Do not use the original column.

    Please mark this post as a solution if it helps you. Appreciate Kudos.

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

Check for Hidden Non-Printable Characters; may be the root cause. Excel may handle them well, but Power BI (DAX engine) won’t match strings with trailing spaces, non-breaking spaces (CHAR(160)), or Unicode anomalies.

 

Create a calculated column in both tables that trims and cleans the text:

CleanedCoachee = TRIM(CLEAN([CoacheeName]))


Then create a new relationship based on CleanedCoachee. Do not use the original column.

Please mark this post as a solution if it helps you. Appreciate Kudos.

  • EmanuelParnaiba's avatar
    EmanuelParnaiba
    Frequent Visitor
    1 year ago

    The "CLEAN" function doesn't seem to exist in DAX, but I was able to achieve the same result on Power Query using these options: 

     

    This solved the problem, thank you so much Andrew! o/