One to Many relationship is created but doesn't work

[EmanuelParnaiba]

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 (comes from Microsoft Access)

The two tables are linked using the coachee's name, which is unique, in a one-to-many relationship, as shown below: 

As shown above, the relationship is active and in the right direction and type, but it is not working.

- If I try to create a column with "RELATED(dimCurrentRoster[coach])" on factCoachDatabase, it returns blank in all rows. 

- If I try to create a simple table with the count of coachees in the fact table, summarizing by the coachee name in the dimension table, I get this:

What I tried so far:

1. Removing duplicates from the dimension table

2. Removing blank values from the fact and the dimension table

3. Checked that both columns have the same data type (text)

4. Exported both tables to Excel and used VLOOKUP to check for values missing or slightly different, but it worked there

Any ideas on how to solve this?

[andrewsommer · Accepted solution]

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]

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/