Forum Discussion
Relationship between two tables
Hi Power BI Community!
I am frustraded....I knew how to connect two tables but know I run into a problem
I have two tables with suppliers. One table has the amount of supplies and the other the parts.
The goal is that amount of supplies from the first table is divided with parts on the second table.
How can I connect these?
Hi Anonymous ,
Based on your description, I created some sample data and did a test. Here are my test tables.
I create two measures to do some calculations.
Measure = VAR x1 = ADDCOLUMNS ( SUMMARIZE ( 't1', t1[ID], t1[amount of supplies] ), "t2", LOOKUPVALUE ( t2[amount of supplies], t2[ID], [ID] ) ) RETURN MAXX ( FILTER ( x1, [ID] = SELECTEDVALUE ( t1[ID] ) ), [amount of supplies] / [t2] )Measure 2 = MAX ( 't1'[amount of supplies] ) / MAX ( 't2'[amount of supplies] )Result:
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please share some sample data and the expected result to have a clear understanding of your question? I can do some tests for you.
You can save your files in some cloud sharing platforms and share the link here. (screenshots are as well)
How to provide sample data in the Power BI Forum - Microsoft Power BI Community
Best Regards,
Yuna
4 Replies
Anonymous Is there any key or ID coloum available .if yes then create a relationship with in both table or Perform merge query at power query . merge by ID col.
if Id or key col. is not available then create a key using index or you can take your data engineer help to create a key then create a relationship in between both table .
Kindly refer these documentation using this link
Kudos will appriciated .👍Thanks for your answer HarishKM I have ID on both of them and created a relationship.
I have the right overall view but when I select a supplier it does not change the amount of parts (stays overall)
What can I do ?
Anonymous
In Power Query editor, Merge these two tables to have single Table and you can perform the action what you want!
Hi Anonymous ,
Based on your description, I created some sample data and did a test. Here are my test tables.
I create two measures to do some calculations.
Measure = VAR x1 = ADDCOLUMNS ( SUMMARIZE ( 't1', t1[ID], t1[amount of supplies] ), "t2", LOOKUPVALUE ( t2[amount of supplies], t2[ID], [ID] ) ) RETURN MAXX ( FILTER ( x1, [ID] = SELECTEDVALUE ( t1[ID] ) ), [amount of supplies] / [t2] )Measure 2 = MAX ( 't1'[amount of supplies] ) / MAX ( 't2'[amount of supplies] )Result:
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please share some sample data and the expected result to have a clear understanding of your question? I can do some tests for you.
You can save your files in some cloud sharing platforms and share the link here. (screenshots are as well)
How to provide sample data in the Power BI Forum - Microsoft Power BI Community
Best Regards,
Yuna
