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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.