DAX / PowerBi: How to get the products that are NOT on stock?

Question

I have 3 tables: STOCK, COLORPRICE, PRODUCT
The relationship between them is:
PRODUCT.PRODUCT = COLORPRICE.PRODUCT (1 to many)
COLORPRICE.EAN = STOCK.EAN (1 to many)

Now I would like to get in a PowerBi Visualisation the active products which are not in our stock.
That means which are not available in "STOCKROOM" and/or "EXPEDITION"
AND where PUBLIC = "Y" and ACTIVE = "Y"
From my sample that should deliver PRODUCT 'aaa 111333555777' and 'bbb 222333555666'.

My sample perhaps looks a bit complex, but this is it:

Answer 1

This measure should return "Not In Stock" when the product is missing or BLANK() when it's not active or public or in expedition or stockroom. It can be used in a matrix visual with Product[Product] on the rows, then only the missing product would be listed.

Not In Stock =
IF(
    ISINSCOPE( 'PRODUCT'[PRODUCT] ),
    IF(
        CALCULATE(
            ISEMPTY( STOCK ),
            'PRODUCT'[PUBLIC] = "Y",
            COLORPRICE[ACTIVE] = "Y",
            STOCK[LOCATION] IN { "stockroom", "expedition" }
        ),
        "Not In Stock"
    )
)

Edit:

to make it work at the EAN level, we can iterate over the COLORPRICE table and perform the check at the COLORPRICE row level. To aggregeate the result at the product level we may set a product Non In Stock when at least one of its EAN is out of stock. This is not tested, but I hope it will work :)

Not In Stock =
IF(
    ISINSCOPE( 'PRODUCT'[PRODUCT] ),
    IF(
        SELECTEDVALUE( 'PRODUCT'[PUBLIC] ) = "Y",
        IF(
            SUMX(
                FILTER( COLORPRICE, COLORPRICE[ACTIVE] = "Y" ),
                IF(
                    CALCULATE(
                        ISEMPTY( STOCK ),
                        STOCK[LOCATION] IN { "stockroom", "expedition" }
                    ),
                    1
                )
            ) > 0,
            "Not In Stock"
        )
    )
)