Out of stock measure calculating correctly for a table but not other visuals

[laetitiaf]

I have this measure where I am trying to identify stock codes that are considered out of stock.

I'm coming across an issue with the final step in trying to remove any stock that is flagged as we wanting to have in stock in a specific DC & not in others.

The total shows as being correct when in a table with DC's included, athough once that DC measure is removed the calculation fails.

Code:

Out of Stock = 
VAR DC_Visible_on_Web = //-- Does not work for visuals without DC column included --//
CALCULATE(COUNT(SOH[Stock Code]), 
    FILTER('restricted location', 'restricted location'[location_restrict] IN VALUES('DC Refrence'[Stock Location]))) // Returns how many SKUs are supposed to be in stock

RETURN
    CALCULATE(
        COUNT('SOH'[Stock Code]), 
        FILTER(SOH,
            'SOH'[Status] = "Enabled" && // Keeps SKUs that are supposed to ranged on shelves
            'SOH'[Max Ranged QTY] > 0 && // Another check to make sure we only select for stock we want ranged
            'SOH'[Arrow On Hand Qty] <= 0 && // Leaves out products we have stock of
            'SOH'[Last Purchase Date] <> BLANK() // Leaves out stock we haven't purchased before 
        ),
        FILTER(SKU, 
         SKU[visibility] <> 1 //Leaves out stock that we are not showing on the web
        ),
        FILTER(
            SOH, DC_Visible_on_Web <= 0 // Leaves out stock that we have restricted to not show in that location - a value means its shown in the location, if else we don't want it ranged
        )
    )

Model:
Restricted location and SOH being Data tables, Status, Visibility, Supplier Allocation, SKU and DC reference being Fact tables

[bhanu_gautam · Accepted solution]

@laetitiaf , Try using

DAX
Out of Stock =
VAR DC_Visible_on_Web =
CALCULATE(
COUNT(SOH[Stock Code]),
FILTER(
'restricted location',
'restricted location'[location_restrict] IN VALUES('DC Refrence'[Stock Location])
)
)

RETURN
CALCULATE(
COUNT('SOH'[Stock Code]),
FILTER(
SOH,
'SOH'[Status] = "Enabled" &&
'SOH'[Max Ranged QTY] > 0 &&
'SOH'[Arrow On Hand Qty] <= 0 &&
'SOH'[Last Purchase Date] <> BLANK()
),
FILTER(
SKU,
SKU[visibility] <> 1
),
FILTER(
SOH,
IF(
HASONEVALUE('DC Refrence'[Stock Location]),
DC_Visible_on_Web <= 0,
TRUE()
)
)
)

[laetitiaf]

Thanks that worked replacing the 'DC Refrence'[Stock Location] with 'SKU'[SKU]

[Anonymous]

Hi @laetitiaf,

Could you please a bit more about your .pbix file?

Sorry that I could not reproduce your issue based on the current info you provide.

Best Regards,
Qi
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!