Reuse Code in same DAX Measure

[manosjxmaah]

Hello!

I am new to DAX and I have a question about reusing code.

I have this calculated measure:

OH CompareTables tofix = 

VAR currentYearSelected = IF(MAX(DateTable[uYear]) = YEAR(TODAY()), TRUE(), FALSE())
VAR sumOH = CALCULATE(
                SUM('Table'[OH]),
                REMOVEFILTERS(DateTable[Year With Current]),
                REMOVEFILTERS(DateTable[uYear]),
                USERELATIONSHIP(DateTable[uDate], CompareDates[uDate])
            )

RETURN
IF(
    currentYearSelected,

    IF(    
        ISINSCOPE(DateTable[QuarterText]),
       
        IF(
            MAX(DateTable[uMonth]) = MONTH(TODAY()),
            CALCULATE(
                SUM('Table'[OH]),
                REMOVEFILTERS(DateTable[Year With Current]),
                REMOVEFILTERS(DateTable[uYear]),
                USERELATIONSHIP(DateTable[uDate], CompareDates[uDate]),
                DateTable[uDay] <= DAY(TODAY()) 
            ),

            sumOH
        ),
       
        CALCULATE(
                SUM('Table'[OH]),
                REMOVEFILTERS(DateTable[Year With Current]),
                REMOVEFILTERS(DateTable[uYear]),
                USERELATIONSHIP(DateTable[uDate], CompareDates[uDate]),
                DateTable[uMonth] <= MONTH(TODAY()) && DateTable[uDay] <= DAY(TODAY()) 
        )
    ),
   
    sumOH
)

 As you can see, I have written this block of code 3 times.

SUM('Table'[OH]),
REMOVEFILTERS(DateTable[Year With Current]),
REMOVEFILTERS(DateTable[uYear]),
USERELATIONSHIP(DateTable[uDate], CompareDates[uDate])

 Is there a way I can reuse the code, so it seems simpler? 

[Anonymous · Accepted solution]

Hi @manosjxmaah ,
Based on the description, it is impossible to reuse the code. Because the sumOH is calculated by condition, the sumOH is value, not a condition anymore.

What’s more, the duplicate code can not be defined as a variable.

SUM('Table'[OH]),
REMOVEFILTERS(DateTable[Year With Current]),
REMOVEFILTERS(DateTable[uYear]),
USERELATIONSHIP(DateTable[uDate], CompareDates[uDate])

Best Regards,

Wisdom Wu

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

[audreygerred]

Hi! Since you defined the VAR of sumOH, you would use the variable in the two spots that you have it written out again.

Use variables to improve your DAX formulas - DAX | Microsoft Learn

[manosjxmaah]

Sorry for the late reply.

But as you can see, in this code block for example, I add the filter DateTable[uDay] <= DAY(TODAY()), so it isn't exactly the sumOH variable I have created.

           CALCULATE(
                SUM('Table'[OH]),
                REMOVEFILTERS(DateTable[Year With Current]),
                REMOVEFILTERS(DateTable[uYear]),
                USERELATIONSHIP(DateTable[uDate], CompareDates[uDate]),
                DateTable[uDay] <= DAY(TODAY()) 
            )

 I have tried using this, but it doesn't give the same result.

CALCULATE(
                sumOH,
                DateTable[uDay] <= DAY(TODAY()) 
            )