Sometime you are required to create a relationship between tables when both tables are having a duplicate values. In this case, If you try to create a relationship, Power BI suggest a many to many relationship for this scenario.
In case, if you do not want to use many to many relationship between tables even then you want to use some other way to create one to many relationship between tables then in this case you can create a table that will have the list of unique values from both tables, and then that table will be linked to both original columns in the relationship.
To demonstrate this, Lets take two sample table named ItemList and ItemQuantity.
You can see, both table have duplicate values for ItemId column.
Now to see the report for Total Amount by Item.
Lets take a table visual and drag ItemId, ItemName and Amount field from ItemList table to visual as shown below.
You can see, now you have total Amount by Item, so far it seems fine.
Now you want to see additional details such as total Available Quantity for each item, for this you need to fetch Available Quantity of Items that exists in another table named ItemQuantity.
Lets drag the Available Quantity field from ItemQuantity table into table visual next to Amount field.
As you can see, the Total Available quantity is repeating same for each item.
Which does not look correct and when you see the relationship between tables you will get to know that there is no relationship exist between table as shown below.
As you know that both tables having a duplicate values for Item Id, and Total available quantity values can be fixed by creating a relationship between tables.
Lets create a relationship between tables, click on Relationship pane, then click on Mange relationships icon under Home tab.
Then click on New.. button to create a relationship.
You can see, the relationship which is suggested is many to many relationship as both table have a duplicate values for ItemId.
Do not let go with this and click on cancel button, will use another way to create a one to many relationship between tables.
Just in case, if you do not want many to many relationship but any other relationship between tables.
For this you need to add an intermediary table to the model that will have the list of unique item ids from both tables, later that will be linked to both original columns in the relationship.
Lets open Power Query Editor, then select the ItemList table.
Now right click on ItemId column header, then select Add as New query from context menu.
Once you click on Add as New Query, you will see that a List table named ItemId is created in the data model which contains all the Item ids from table ItemList.
Now, will convert this List table into a valid table using following steps.
Select the ItemId list table, then right click on List column header and select To Table from context menu.
Clicking on To Table, a To Table dialog box opens, just click on OK button.
Once you click on OK button, A list table is converted into a table. Rename the column1 as ItemId.
Now repeat the same steps for ItemQuantity table, and get a separate table which contains all the item ids from table ItemQuantity .
You can see, a List table named ItemId(2) is created. Lets convert this List table into a valid table.
Just right click on column header List then select To table from context menu.
Rename the table’s column1 to ItemId.
After that, appned all the Item Id of ItemId(2) into ItemId table using append queries operation then remove all duplicate Item ids.
The idea is that to get a table which will have unique item ids from both table.
Lets append the Item id of table ItemId(2) into ItemId table.
First select the ItemId table then, click in Combine icon under Home table, and select Append Queries.
After that a Append dialog box opens.
Select ItemId(2) table from dropdown and click on OK button, you will see all the item ids from table itemId(2) is appneded into Itemid table.
Now remove the duplicate ItemIds, right click on ItemId column header and select Remove Duplicates from context menu.
Once you remove the duplicate ItemId, now you have a separate table ItemId which contains the unique ItemId from both table ItemList and ItemQuantity.
As you have all the unique item ids from both table into ItemId table, so you don’t have need of Itemid(2) table that you can remove, Just right click on table then select Enable load.
After that, click on Continue button.
Now Click on Close & Apply to save the changes.
Now go to relationship pane, and create a relationship of both table ItemList and Itemquantity with new table ItemId as shown below.
You can see, now there is a one to many relationship between tables.
Lets go to report page, and now use ItemId of ItemId table then you will see the Total Available quantity is now fixed.
Read Also
Transform the Data in Power Bi
Implementation of Dynamic Page Navigation in Power BI
Personalize Visuals Preview in Power Bi
Create dynamic Title in Power Bi using slicer
Show or Hide visuals in Power BI
Show/Hide Measures in Line chart visuals using slicer in Power BI
5,159 total views, 4 views today