Filter based on whats not in another table

[Nicho248]

Hello - Trying to write basic DAX formula. Must be doing something wrong. 

I have two tables. One table, Product, has all the part numbers. I have setup a relationship to another table, UoM. UoM has less part numbers in it. I'd like to know which part numbers are missing in UoM table, but are in Product table.

The relationship is setup as one to many (Product to UoM). Sometimes UoM has the part number in there more than once.

How do I do this? Here is example code I have tried that fails.

Missing UOM = 

Calculate (

COUNT( 'Product'[Product ID]),

FILTER('Product','Product'[Product ID] <> RELATED('UoM'[Product ID])))

Thanks

Nick

[lbendlin · Accepted solution]

If you want to keep the 1:* relationship, use COUNTROWS(RELATEDTABLE()) in a calculated column in Products.  Otherwise, inactivate the relationship and use EXCEPT.

[Nicho248]

thank you. this worked great.

[Wilson_]

Hey Nicho,

Try something like this:

Missing UOM =
COUNTROWS (
    EXCEPT (
        VALUES ( 'Product'[Product ID] ),
        VALUES ( 'UOM'[Product ID] )
    )
)

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.

[Nicho248]

Wilson - I was not aware of EXCEPT and I will use this in the future. I marked the other recommendation as the solution because it helped filter a table visualization to identify which parts were actually missing. Unforunately with only your recommendation the table was providing non-sensical answers, however the measure on its own was working correctly. Thank you for your tip!

[Wilson_]

Nicho,

No worries, sometimes solutions work or not depending on the context in which they are used. It looks like you were looking for a calculated column and not a measure based on the other comment, whereas I shared a measure.

Glad you were able to get to a solution. 🙂