Help with the dax formula

[rit_ty7]

Hello everyone, is the below formula correct for calculating productive calls(pc) for lighting?

PC Total Call_lighting =

var a = CALCULATE(COUNT(SECONDARY_VISIT_DATA_NEW[VISIT_ID]), FILTER(SECONDARY_VISIT_DATA_NEW, SECONDARY_VISIT_DATA_NEW[NON_PRODUCTIVE] = "NO"))

var b = CALCULATE(a, FILTER(MATERIAL_ATTRIBUTES_CL, MATERIAL_ATTRIBUTES_CL[MAIN_GROUP] IN {"Conventional", "D-Lite", "Elect. Accessories", "LED Battens", "LED Lamps", "LED Outdoors"}))

RETURN

b

[danextian]

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.

[Abhijeet_40]

Hello!

Just a couple of questions. Why are using filter and using an inline method:
PC Total Call_lighting =
CALCULATE(
COUNT(SECONDARY_VISIT_DATA_NEW[VISIT_ID]),
SECONDARY_VISIT_DATA_NEW[NON_PRODUCTIVE] = "NO",
MATERIAL_ATTRIBUTES_CL[MAIN_GROUP] IN {
"Conventional",
"D-Lite",
"Elect. Accessories",
"LED Battens",
"LED Lamps",
"LED Outdoors"
}
)

If this won't work for you. Why not use both the filter block in a single calculate block?

[Ritaf1983]

Hi @rit_ty7 

Please share sample data (excluding sensitive information) in text format, along with your expected result and any relevant logic. For guidance, refer to

How to provide sample data in the Power BI Forum

If possible, upload a simplified .pbix file using this guide:

 How to upload PBI in Community