Help with DAX RELATED function

[Anonymous]

Hi - I've been through so many other posts on the DAX RELATED function but still cannot seem to diagnose the problem with my model:

Data model

Fact table

Lookup table

First image is data model

Second image shows the query in the fact table (Timeshee Data) and the third shows the relevant entry in the lookup table (Data_MasterResourceList).  (Note that this is one example shown by filtering but there is 200 employees and not a single one returns via the related query. 

Things I have tried:

1. Confirmed that link is one to many and that query is being run in the fact table and not lookup table

2. Ensured that 'Employee' and 'Full Name' fields are both set to text and both 'cleansed' in the Query Editor. 

I have two questions

1. In this case - what am I doing wrong?

2.  More generally - I'm clearly at beginner stage of learning and I suspect my data model is not best practice - is there any recommended reference source/training on a good data model structure. 

many thanks in advance for your help. 

[Anonymous]

Thanks @v-easonf-msft and @amitchandak  for your suggestions

Interestingly I found the problem in the original dataset but now I'm not sure how to fix it for future data imports - the fix was to delete the single space between first name and last name in the timesheet data set and then re add the space. There was no double spaces so it looks like what is coming out of the timesheet system is not an acutal space.  I checked this in Query Editor by extracting before delimitter (with 'space' set as the delimitter) and only the modified entries extract, all the others remain as the full text suggested the delimitter doesn't exist. 

I tried a few other delimitters e.g.  comma,  %20  to see if I could figure it out but I cannot. Is there anyway to see/check what the delimitter actually is or otherwise fix this? (When extracting the length both the 'fixed' and 'not fixed' versions have the same number of characters so *something* is there!) There is 9000 entries in the timesheet data so manual is not going to happen 😂

[v-easonf-msft]

Hi, @Anonymous 

I checked the relationships and formula and I don't find anything obviously wrong.

If the formula below doesn't work either then I think you may need to check the original data to make sure there are no extra spaces.

Team = LOOKUPVALUE(Data_MasterResourceList[CRSType],Data_MasterResourceList[Full Name],'Timesheet Data'[Employee])

Best Regards,
Community Support Team _ Eason

[amitchandak]

@Anonymous , Plot the employee name from both tables into a table visual and check for blank values. if there is a name from either side showing a blank, it means values are not matching.

If needed use clean and trim in the power query

Power Query Trim and Clean : https://youtu.be/NRYPsCnS0w4