Amend DAX to ignore exception

[pandeion]

Hi All

I've enharited some dashboards and looking to simplify a measure now. This was originaly created in order to get certain values from a file if Period filter was 2021 P1-P3. Trying to simplify the measure and delete this exception now as it's not needed anymore. Everything else should be kept. Rather newbie here and struggling to follow the logic. Any ideas on how to approach this and delete the part i don't need anymore? TIA

[pandeion]

Thanks! It has worked!

[Anonymous]

Hi @pandeion ,

Try to modify your formula like below:

Answer as Number EUT YTD =
VAR latest_period =
    MAX ( 'D_SalesPeriod'[Period] )
VAR year_val =
    SELECTEDVALUE ( D_SalesPeriod[Cycle Year] )
VAR not_total =
    ISINSCOPE ( 'EUT tab_dimentions'[Q_lc1] )
VAR result =
    IF (
        not_total,
        CALCULATE (
            SUM ( 'F_Form Answer'[Answer as Number] ),
            FILTER (
                ALL ( 'D_SalesPeriod' ),
                'D_SalesPeriod'[Cycle Year] = year_val
                    && 'D_SalesPeriod'[Period] <= latest_period
            )
        ),
        CALCULATE (
            SUM ( 'F_Form Answer'[Answer as Number] ),
            FILTER (
                ALL ( 'D_SalesPeriod' ),
                'D_SalesPeriod'[Cycle Year] = year_val
                    && 'D_SalesPeriod'[Period] <= latest_period
            ),
            'EUT tab_dimentions'[Q_lc1] = "Conversion"
        )
            - CALCULATE (
                SUM ( 'F_Form Answer'[Answer as Number] ),
                FILTER (
                    ALL ( 'D_SalesPeriod' ),
                    'D_SalesPeriod'[Cycle Year] = year_val
                        && 'D_SalesPeriod'[Period] <= latest_period
                ),
                'EUT tab_dimentions'[Q_lc1] = "Losses"
            )
    )
RETURN
    IF ( ISBLANK ( result ), 0, RESULT )

Best Regards,
Adamk Kong

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

[tamerj1]

Hi @pandeion 
Please copy and paste the measure code in a reply.

[pandeion]

Hey tamerj1! Thanks for replying, code below.

Answer as Number EUT YTD =

VAR latest_period = MAX('D_SalesPeriod'[Period])

var year_val = SELECTEDVALUE(D_SalesPeriod[Cycle Year])

VAR p1_p3 = "2021 P01-P03"

VAR not_total  = ISINSCOPE('EUT tab_dimentions'[Q_lc1])

VAR result =

IF(not_total,

   // Check if special case "2021 P1-P3"

    IF(latest_period = p1_p3,

        // Case 2021 P1 - P3

        CALCULATE(Sum('EUT P1-P3'[Value])),

        //Case 2021 P4 >=

        if(year_val=2021,CALCULATE(Sum('EUT P1-P3'[Value]), All('D_SalesPeriod'[Period]), 'D_SalesPeriod'[Period]=p1_p3),0) +

        CALCULATE(SUM('F_Form Answer'[Answer as Number]), All('D_SalesPeriod'[Period]),D_SalesPeriod[Cycle Year]=year_val, 'D_SalesPeriod'[Period]<=latest_period)),

    IF(latest_period = p1_p3,

        // Case 2021 P1 - P3

         CALCULATE(SUM('EUT P1-P3'[Value]), 'EUT tab_dimentions'[Q_lc1]="Conversion") -

         CALCULATE(SUM('EUT P1-P3'[Value]), 'EUT tab_dimentions'[Q_lc1]="Losses"),

        //Case 2021 P4 >=

        CALCULATE(SUM('F_Form Answer'[Answer as Number]), 'EUT tab_dimentions'[Q_lc1]="Conversion", All('D_SalesPeriod'[Period]), D_SalesPeriod[Cycle Year]=year_val,'D_SalesPeriod'[Period]<=latest_period) +

        if(year_val=2021,CALCULATE(SUM('EUT P1-P3'[Value]), 'EUT tab_dimentions'[Q_lc1]="Conversion", All('D_SalesPeriod'[Period]), 'D_SalesPeriod'[Period]=p1_p3),0) -

        if(year_val=2021,CALCULATE(SUM('EUT P1-P3'[Value]), 'EUT tab_dimentions'[Q_lc1]="Losses", All('D_SalesPeriod'[Period]), 'D_SalesPeriod'[Period]=p1_p3),0) -

        CALCULATE(SUM('F_Form Answer'[Answer as Number]), 'EUT tab_dimentions'[Q_lc1]="Losses", All('D_SalesPeriod'[Period]),D_SalesPeriod[Cycle Year]=year_val,'D_SalesPeriod'[Period]<=latest_period))

)

RETURN

IF(ISBLANK(result), 0, RESULT)

[tamerj1 · Accepted solution]

@pandeion 

This is based on my interpretation of your DAX code and your explanation or the requirement.

Answer as Number EUT YTD =
VAR latest_period =
    MAX ( 'D_SalesPeriod'[Period] )
VAR year_val =
    SELECTEDVALUE ( D_SalesPeriod[Cycle Year] )
VAR p1_p3 = "2021 P01-P03"
VAR not_total =
    ISINSCOPE ( 'EUT tab_dimentions'[Q_lc1] )
VAR result =
    IF (
        not_total,
        IF (
            year_val = 2021,
            CALCULATE ( SUM ( 'EUT P1-P3'[Value] ), 'D_SalesPeriod'[Period] = p1_p3 )
        )
            + CALCULATE (
                SUM ( 'F_Form Answer'[Answer as Number] ),
                D_SalesPeriod[Cycle Year] = year_val,
                'D_SalesPeriod'[Period] <= latest_period
            ),
        CALCULATE (
            SUM ( 'F_Form Answer'[Answer as Number] ),
            'EUT tab_dimentions'[Q_lc1] = "Conversion",
            D_SalesPeriod[Cycle Year] = year_val,
            'D_SalesPeriod'[Period] <= latest_period
        )
            - CALCULATE (
                SUM ( 'F_Form Answer'[Answer as Number] ),
                'EUT tab_dimentions'[Q_lc1] = "Losses",
                D_SalesPeriod[Cycle Year] = year_val,
                'D_SalesPeriod'[Period] <= latest_period
            )
            + IF (
                year_val = 2021,
                CALCULATE (
                    SUM ( 'EUT P1-P3'[Value] ),
                    'EUT tab_dimentions'[Q_lc1] = "Conversion",
                    'D_SalesPeriod'[Period] = p1_p3
                )
                    - CALCULATE (
                        SUM ( 'EUT P1-P3'[Value] ),
                        'EUT tab_dimentions'[Q_lc1] = "Losses",
                        ALL ( 'D_SalesPeriod'[Period] ),
                        'D_SalesPeriod'[Period] = p1_p3
                    )
            )
    )
RETURN
    RESULT + 0