Forum Discussion
Two columns with same fields but relationship doesn't work
I have two tables to which I've added a merged column using Power Query "Merged Column" option. Both columns have the same fields and I am able to create a relationship. However the relationship doesn't work. PowerBI doesn't recognize that those are the same fields (I know because I've tried to look them up using LOOKUPVALUE but it returns empty cells). I've tried trimming in power query but doesn't help. Anyone knows what may be happening?
One column in plan fact table:
The other one in dim_calendar table:
Thanks!
The problem is that the fields do not match:
Changing the field in the calendar table (YearMonth)
11 Replies
Try creating a table visual with both columns and see if the rows match
Here's what I got in the visual. Blanks from dim_calendar field:
I take it the relationship is one-to-many from the dim calendar down to the fact table. If so, you need to make the calendar table column the parent in the matrix (or use a Table visual instead)
How does the relationship not work? Can you create the relationship without problems, or do you get errors?
What column are you using for merging on the two tables? Could there be a way for all this without merging anything, just using relationships?
It seems strange to me that your plan fact has those months as a column (instead of just a date), but I can't put my finger on it without seeing the system as a whole.
I can create a relationship with no issues and it is active.
The reason why I'm merging is that the plan is created on the month granularity while all other fact tables are on the date granularity. If I create a relationship based on the year field the split by month doesn't work and if I create a relationship based on the month field the split by year doesn't work. Hence I'm creating a merged year + month.
Sorry, this may sound obvious, but are you sure the relationship links both columns (and not some other field?)
If so, it might be worth trimming and cleaning both columns just in case
