Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
I'm having trouble merging information into one table on power BI.
I have 3 tables which I work from which I work from
1. Policy
2. Motor Drivers
3. Motor Vehicles
They all have a common field which is the unique policy reference number e.g. GREG-1001. I've managed to create the following 2 relationships:
However when I try to create a relationship between Motor Driver & Motor Vehicle it comes up with the an error message stating "Can´t create a relationship between two columns because one of the columns must have unique values"
In the motor drivers column there can be more than one row per unique reference number if there is more than one driver.
Can anyone help regarding this?
Thanks
Greg
Solved! Go to Solution.
if you're trying to normalize the data into 1 table, I recommending using the "Merge Queries" button in Query Editor to perform the merge and get the columns that you need into 1 data table.
Relationships in Power BI should be used as a way to link a dimension table to a fact table.
It sounds like you have 2 fact tables, and these should never have a relationship joining them.
Try Policy as BRIDGE TABLE in relationship
POLICY MOTOR------POLICY-------------POLICYVEHICLE
M:1 1:M
And while creating the report pull the policy Number from POLICY table as well.
Hi @greglauder,
I guess you have created a one to many relationship for Policy & Motor Drivers and Policy & Motor Vehicles. That case, there existing a indirect many to many relationship between Motor Drivers and Motor Vehicles. It is not able to establish a many to many relationship. So, if you want to merge Motor Drivers and Motor Vehicles, you could try ChrisHaas's suggestion that use the "Merge Queries" button in Query Editor.
Regards,
Yuliana Gu
if you're trying to normalize the data into 1 table, I recommending using the "Merge Queries" button in Query Editor to perform the merge and get the columns that you need into 1 data table.
Relationships in Power BI should be used as a way to link a dimension table to a fact table.
It sounds like you have 2 fact tables, and these should never have a relationship joining them.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |