Show Quantity based on Item number another table

[Powerbi_superus]

Hi All,

I am new to Power bi  and DAX

Hi   Table  A   WITH  SONUMBER ,ITEMNO 
       Table  B   WITH  SONUMBER, ITEMNO, Quantity
       Table A   and Table B have same SONUMBER  and ITEMNO .

I have to show up the Quantity of Table B with repective to SONUMBER ,ITEMNO OF Table A

Please let me know how we can dax to show quantity of Table B as there is  no join between ITEM NO of  TABLE A AND TABLE B.

Thank you all

[v-alq-msft · Accepted solution]

Hi, @Powerbi_superus 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

TableA:

TableB:

There is no relationship between two tables. 

You may create a measure like below.

Qty = 
SUMX(
    ADDCOLUMNS(
        TableA,
        "Result",
        CALCULATE(
            SUM(TableB[Quantity]),
            FILTER(
                ALL(TableB),
                [SoNumber]=EARLIER(TableA[SoNumber])&&
                [ItemNo]=EARLIER(TableA[ItemNo])
            )
        )
    ),
    [Result]
)

Or

Qty 2 = 
CALCULATE(
    SUM(TableB[Quantity]),
    TREATAS(TableA,TableB[SoNumber],TableB[ItemNo])
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Powerbi_superus]

Thank you Allan

[amitchandak]

@Powerbi_superus , refer if treatas can help

https://docs.microsoft.com/en-us/dax/treatas-function