Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
CalebSalo
Regular Visitor

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!

 

Capture.PNG

 

2 REPLIES 2
amitchandak
Super User
Super User

@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

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. 

 

CalebSalo_1-1670520238601.png

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.