Help with the dax formula

Hi rit_ty7 

It is important to note that variables in DAX are immutable, meaning their values cannot be altered using CALCULATE to modify their context. For example, if a variable like var a is defined, its context cannot be changed after assignment in var b. As shown in the screenshot, the _LY measure is expected to return the value for 2023, but instead, it continues to return values for each individual year due to the immutability of the variable.

Try this measure instead.

CALCULATE (
    COUNT ( SECONDARY_VISIT_DATA_NEW[VISIT_ID] ),
    KEEPFILTERS ( SECONDARY_VISIT_DATA_NEW[NON_PRODUCTIVE] = "NO"
        && MATERIAL_ATTRIBUTES_CL[MAIN_GROUP]
        IN {
            "Conventional",
            "D-Lite",
            "Elect. Accessories",
            "LED Battens",
            "LED Lamps",
            "LED Outdoors"
        } )
)

The above represents an optimized version of your original measure. Instead of applying FILTER to the entire table, focus it only on the relevant columns. Use KEEPFILTERS to intersect filters, rather than override the existing filter context in DAX. For example, the value will only be calculated if SECONDARY_VISIT_DATA_NEW[NON_PRODUCTIVE] = No and MATERIAL_ATTRIBUTES_CL[MAIN_GROUP] are included in your list. This ensures that the calculation is specific to those conditions and is not applied to all rows.