Forum Discussion

JackMarten's avatar
JackMarten
New Member
2 years ago

Filtering table based on two other tables

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 it...
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    JackMarten
    New Member
    2 years ago

    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].