nest calculation that needs a filter statement on it with an IN statement not working

TariqArnabi — Wed, 11 May 2022 13:53:20 GMT

I have a nest calculation that needs a filter statement on it with an in statement can anyone help me figure out why this is not working?

Industry Average =
VAR Slicer =
    SELECTEDVALUE ( 'Benchmark Slicer'[Slicer] )
VAR Fail_Count =
    CALCULATE (
        COUNT ( 'SafetyRpts JobData'[SubCategoryName] ),
        'SafetyRpts JobData'[ItemPassFail] = "F"
            && 'SafetyRpts JobData'[ImmediatelyCorrected] = "Y"
    )
VAR Pass_Count =
    CALCULATE (
        COUNT ( 'SafetyRpts JobData'[SubCategoryName] ),
        'SafetyRpts JobData'[ItemPassFail] = "P"
    )
VAR Result =
    SWITCH (
        TRUE (),
        Slicer = "Regional",
            CALCULATE (
                DIVIDE (
                    IF (
                        AND (
                            MAX ( 'SafetyRpts JobData'[ItemPassFail] ) = "F",
                            MAX ( 'SafetyRpts JobData'[ImmediatelyCorrected] ) = "Y"
                        )
                            || MAX ( 'SafetyRpts JobData'[ItemPassFail] ) = "P",
                        Fail_Count + Pass_Count
                    ),
                    CALCULATE ( COUNT ( 'SafetyRpts JobData'[SubCategoryName] ) ),
                    FILTER (
                        VALUES ( 'SafetyRpts InspectionData' ),
                        'SafetyRpts InspectionData'[AccountName] = ( [Find Client Region] )
                    )
                ),
                Slicer = "Inpector",
                (
                    CALCULATE (
                        DIVIDE (
                            IF (
                                AND (
                                    MAX ( 'SafetyRpts JobData'[ItemPassFail] ) = "F",
                                    MAX ( 'SafetyRpts JobData'[ImmediatelyCorrected] ) = "Y"
                                )
                                    || MAX ( 'SafetyRpts JobData'[ItemPassFail] ) = "P",
                                Fail_Count + Pass_Count
                            ),
                            CALCULATE ( COUNT ( 'SafetyRpts JobData'[SubCategoryName] ) )
                        )
                    )
                ),
                Slicer = "Global",
                (
                    CALCULATE (
                        DIVIDE (
                            IF (
                                AND (
                                    MAX ( 'SafetyRpts JobData'[ItemPassFail] ) = "F",
                                    MAX ( 'SafetyRpts JobData'[ImmediatelyCorrected] ) = "Y"
                                )
                                    || MAX ( 'SafetyRpts JobData'[ItemPassFail] ) = "P",
                                Fail_Count + Pass_Count
                            ),
                            CALCULATE ( COUNT ( 'SafetyRpts JobData'[SubCategoryName] ) )
                        )
                    )
                )
            )
    )
RETURN
    Result

Re: nest calculation that needs a filter statement on it with an IN statement not working

v-xiaotang — Mon, 16 May 2022 07:32:53 GMT

Hi TariqArnabi

Thanks for reaching out to us.

Please return each variable in turn to check whether the return value is correct. If there is no problem with their return values, could you please provide some sample data for us to investigate further? Thanks

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

topic nest calculation that needs a filter statement on it with an IN statement not working in DAX Commands and Tips

nest calculation that needs a filter statement on it with an IN statement not working

Re: nest calculation that needs a filter statement on it with an IN statement not working