Filtering table based on two other tables

[JackMarten]

Hello!

I have the following datamodel:

Now what I am trying to do Is look at an itemno in the items table, check for that items related parent items in the Bom components table. Take the itemNos parent item(s) and use them to filter the postedsalesinvoice table and then calculate the sum of the quantity column in the postedsalesinvoice table. 

The relationships are all built on the itemno column. itemno in postedsalesinvoice is just "no".
To clarify, the Bom components table is in this case used to store Parent items. So if I have an itemno called "Item1" on the rows in a matrix visual. I want the measure to look at Item1's parentitems and calculate the total quantity sold for those parentitems.

I tried the following but it did not work:

Parentitems sold =

VAR specificitem = SELECTEDVALUE(items[itemNo])

VAR parentitems =

    CALCULATETABLE(

        VALUES(bomcomponents[parent_Item_No_]),

        specificitem = items[itemNo]

    )

RETURN

CALCULATE(

    SUM(postedsalesinvoice[quantity]),

    FILTER(

        postedsalesinvoice,

        postedsalesinvoice[no] IN parentitems

    )

)
Grateful for any help!

[Anonymous]

Hi, @JackMarten 

Based on your description, you already have a basic understanding of your needs, but there are a few more points that you need to confirm:
Your needs:
1.ultimately want to get the quantity column in the postedsalesinvoice table.
2. realize a matrix, in the matrix can see ItemNo parent_Item_No
Doubtful:
1. what are the specific relationship fields of the three tables
2. I don't see a quantity column in the postsalesinvoice table.
3. most importantly, I hope you can upload the pbix file without sensitive data or provide the datasheet that approximates your goal.

Best Regards,
Yang
Community Support Team

[JackMarten]

Hello 🙂

The quantity column for the postedsalesinvoice table is present you just cant see it in the screenshot. However I am not able to upload the pbix since it doesn contain some sensetive data. I did however manage to get it to work with the following measure:

QTY of sold parentitems =

VAR _SelectedItemNo = SELECTEDVALUE(items[itemNo])

VAR parentitems =

    CALCULATETABLE(

    DISTINCT(bomcomponents[parent_Item_No_]),

    FILTER(

        bomcomponents,

        bomcomponents[itemNo] = _SelectedItemNo

    )

    )

VAR filtereditemtable =

    CALCULATETABLE(

        SELECTCOLUMNS(

            items,

            items[itemNo]),

            items[itemNo] IN parentitems

    )

RETURN

CALCULATE(

    SUM(postedsalesinvoice[quantity]),

    filtereditemtable

)

It is probably pretty bad in terms of optimization but it did the trick. 
The item table and bomcomponents table has a relationship on the itemno field.
The item table and the postedsalesinvoice table has a relationship from 'items'[itemno] to 'postedsalesinvoice'[no].