Distinct count but only if value exists in another table weekly

[dom]

Hello Power BI friends

I wonder if you help me solve the challenge I have.

The data model has 3 tables calendar, table 1 with SKUs planned for production and table 2 SKUs actually produced. I need to calculate weekly adherence to the production plan - count the number of SKUs produced but only if they were planned ( exists in table 1). Here is my table structure:

TABLE 1 - products planned for production

Table 2 products actually produced

Below is an example of what  I would like to achieve:

Week no       #SKUS planned      #SKUs planned and filled        Adherence to plan (SKUs produced/ SKUsplanned)

1                             120                                100                                    83%

2                             180                                179                                    99%

I would appreciate your help

Thanks

Dom

[amitchandak · Accepted solution]

@dom , Try a measure like

#SKUs planned and filled = calculate(distinctcount(Table2[SKU]), filter(Table2, Table2[sku] in values(Table1[sku])))

you can also explore treatas

https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/

[dom]

thanks @amitchandak that worked 🙂