Forum Discussion

CalebSalo's avatar
CalebSalo
Regular Visitor
3 years ago

Compare Two Lists and Return Missing Items

I have 2 tables.

The first table is a bill of materials (BOM) that has 3 columns:

1) the product being built

2) which part number (PN) is needed

3) the inventory status of the PN (whether we need to purchase it or we have it on-hand)

The second table is a list of what has been purchased and has 3 columns:

1) the product being built

2) the particular instance of that product in question (the instances are distinguished from one another by listing the date of its release)

3) which PN was purchased

 

I would like to have a 3rd table generated that would have 3 columns:

1) the product

2) the release date

3) the PN(s) that have not been ordered yet. If a particular product with a particular release date is missing more than one PN, it would be great if they could be listed in one cell. However, if that's not possible, it could appear in a separate row. If a particular product with a particular release date has had all its necessary PNs purchased, it should say "Fully ordered" or something like that. 

 

What's giving me trouble is I need to make the comparison between the tables based on the release date and need to only consider the PNs in the BOM which are marked "purchased" 

 

Some other things to note:

The BOM requirements across different release dates are the same. For example, a car released in July will have the same BOM PNs as a car released in October. 

I would like to accomplish this with DAX code instead of doing it in Power Query if possible. 

 

Below is a screenshot of the tables I've described. 

 

Thank you so much!

 

 

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    CalebSalo , Plot  this measure along with product and release date from orders table

     

    mising product =
    var _tab = except(Summarize(filter(BOM, BOM[Product]= max(Orders[Product])),BOM[Product]), distinct(Orders[Product]))
    return
    if(isblank(countx(_tab,[Product])), "Fully Ordered", concatenateX(_tab, [Product], ", "))

     

    another approch

    Power BI Items/Categories not sold to the customers
    Power BI Items/Categories not sold to the customers: https://youtu.be/AbuKvAnicwo

    • CalebSalo's avatar
      CalebSalo
      Regular Visitor
      3 years ago

      Hi amitchandak thank you for your response. 

       

      I've created the measure as instructed but I don't think the output table was correct. I believe the computer 1/1/2023, car 3/1/2023, and computer 2/1/2023 should have missing PNs listed in the column. I've attached a screenshot of my visuals page. 

       

      I should also note that sometimes there are items that appear in the orders table for a particular product that are not on the BOM. These items can be ignored and visibility of them is not necessary. Not sure if this affects the proposed solution but thought I would mention it.