Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
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 😂
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
@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
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |