Trying to use the Calculate function to filter out orders on SubCat2 that has FILLLER only

[Anonymous]

Trying to use the Calculate function to filter out the orders on the SubCat2 field that have "Filler" only and do not have any other SubCat values associated with it.  

I have this so far but am getting orders with Molding, Finish Panel, etc associated with that order number included. 

Measure = CALCULATE(DISTINCTCOUNT('DW Manufacturing'[Customer Orders Manufactured.Control ID Key]), ('DW Manufacturing'[Finished Goods Ordered.Product Sub Category2] = "FILLER"))  

Example:  Order 12635610 has a Filler associated and a molding associated in the SubCat 2 field.   I am looking for orders that only have Filler and no other associations. 

[HotChilli · Accepted solution]

Create a measure  (you should really shorten the column names to make life a bit easier)

CountSubCat = CALCULATE(COUNT(TableJ[Sub cat]), ALLEXCEPT(TableJ, TableJ[Control ID Key]))

You then pull Control ID Key on to a table visual with the measure ( and Sub category if you need it ).

Use the visual filters pane to choose "Filler" for sub category.

If the CountSubCat is equal to 1 -> that's the winner.  And there were none in the sample picture.

Hope it helps.  Also can you post data rather than a picture next time.  It always helps.