Relationship between two tables

[Anonymous]

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?

[Anonymous · Accepted solution]

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

[HarishKM]

@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 .👍

[Anonymous]

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 ?

[VijayP]

@Anonymous 

In Power Query editor, Merge these two tables to have single Table and you can perform the action what you want!