Populating a new column in Table 1 with Summed & Filtered data from Table 2

[Carl143]

Hi am very new to PowerBi, so excuse the basic question

I have 2 Tables

Key is PN Column

Table 1

PN          New Column

1234

2222

3333

Table 2

PN      Location      QTY Available

1234   BNE             6

1234   CDC            8

2222   BNE             50 

2222   LAX             9

2222   SYD             2

3333   BNE            3

3333   CDC            4

3333   LAX             7

3333   SYD            1

Expected Result

Table 1

PN          New Column

1234        8

2222        0

3333        4

[Jihwan_Kim · Accepted solution]

Hi,

I assume there is a relationship between the two tables like below.

Please check the below picture and the attached pbix file.

RELATEDTABLE function (DAX) - DAX | Microsoft Learn

Expected result Calculated Column = 
SUMX (
    FILTER ( RELATEDTABLE ( 'Table 2' ), 'Table 2'[Location] = "CDC" ),
    'Table 2'[QTY Available]
) + 0

[Carl143 · Accepted solution]

Thanks Jihwan, this was perfect

[Jihwan_Kim · Accepted solution]

Hi,

I assume there is a relationship between the two tables like below.

Please check the below picture and the attached pbix file.

RELATEDTABLE function (DAX) - DAX | Microsoft Learn

Expected result Calculated Column = 
SUMX (
    FILTER ( RELATEDTABLE ( 'Table 2' ), 'Table 2'[Location] = "CDC" ),
    'Table 2'[QTY Available]
) + 0