Issues with ISINSCOPE Measure

DAX DAX Calculations
1

Hello!
I am authoring a measure that uses ISINSCOPE Function. This function looks if a particular category is in scope and if it is, it returns the MAX for that measure otherwise, it returns the SUM of the measure thereof.

The outside scope total works perfectly but in scope total for the category is not working.

image
image1432Ă—693 84.5 KB

As seen from the image above, I am expecting the total to be 19,080 for the TripA total but what I get is 540 and therefore my Efficiency calculation is wrong. This should be in sync with the total row.

The image above is accessed from a drill-through page:

image
image1286Ă—1080 60.5 KB

My DAX code is like so:

      Planned Duration(Mins) = 
IF (
    ISINSCOPE ( Employee[Emp] ),
    CALCULATE ( MAX ( 'Scope'[Planned Duration(Min)] ) ),
    IF (
        ISINSCOPE ( JobCode[JobCode] ),
        CALCULATE ( MAX ( 'Scope'[Planned Duration(Min)] ) ),
        IF (
            ISINSCOPE ( 'Description'[Description] ),
            CALCULATE ( MAX ( 'Scope'[Planned Duration(Min)] ) ),
            VAR _Planned =
                SUMMARIZE (
                    'Scope',
                    'Scope'[Date],
                    'Scope'[AreaCode],
                    'Scope'[Description],
                    'Scope'[SessionID],
                    'Scope'[Zone],
                    'Scope'[Emp],
                    'Scope'[JobCode],
                    "PlannedDuration", MAX ( 'Scope'[Planned Duration(Min)] )
                )
            RETURN
                SUMX ( _Planned, [PlannedDuration] )
        )
      )
)

Explanation:
The planned Duration column is to return the maximum minutes per row for that particular day but when it comes to “totaling”, it is Input * the MAX of Planned Duration per row

I do need help with this, please.
ISINSCOPE Rep.pbix (3.1 MB)
File:
ISINSCOPE.xlsx (27.5 KB)

1 Like
2

Hi
I have been able to resolve the issue. Code share below:

#Test = 
IF (
    ISINSCOPE ( Employee[Emp] )  && ISINSCOPE ( xDates[Date] ),
    CALCULATE ( MAX ( 'Scope'[Planned Duration(Min)] ) ),
    IF (
        ISINSCOPE ( JobCode[JobCode] ) && ISINSCOPE ( xDates[Date] ),
        CALCULATE ( MAX ( 'Scope'[Planned Duration(Min)] ) ),
        IF (
            ISINSCOPE ( 'Description'[Description] ) && ISINSCOPE ( xDates[Date] ),
            CALCULATE ( MAX ( 'Scope'[Planned Duration(Min)] ) ),
            VAR _Planned =
                SUMMARIZE (
                    'Scope',
                    'Scope'[Date],
                    'Scope'[AreaCode],
                    'Scope'[Description],
                    'Scope'[SessionID],
                    'Scope'[Zone],
                    'Scope'[Emp],
                    'Scope'[JobCode],
                    "PlannedDuration", MAX ( 'Scope'[Planned Duration(Min)] )
                )
            RETURN
                SUMX ( _Planned, [PlannedDuration] )
        )
      )
)

ISINSCOPE Rep.pbix (87.1 KB)
ISINSCOPE.xlsx (27.5 KB)