Custom DAX functions for calculated columns?

[bleow]

I have a calculated column with many lines of code, and I have to repeat this calculation with 1 different parameter for other calculated columns. Is there a way to create a custom function with DAX code, and call that function for multiple calculated columns?

For context, this is the code I'm referring to that calculates number of annual leaves taken on a day. I need to do the same calculation for other types of leaves as well (e.g. medical).

Outside of the question, if you have any suggestions to make the code more elegant, do let me know as well.

ANNUAL LEAVE = 
/* LEAVE COUNTER per day by department/category from event-in-progress leave record data */
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

VAR isSatSunPubHol =  
(
    // if is saturday or sunday, will return true
    '.DEPT LEAVE APPS'[isSatSun] || '.DEPT LEAVE APPS'[isPubHol]
)

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

VAR calcLeave = 
(

    //annual leaves excluding 0.5 day offs
    CALCULATE( COUNTROWS('3 COMBINED Record ANNUAL' ), //count total number of leave applications

        //only keep values where leave started on or before the date
        FILTER( VALUES('3 COMBINED Record ANNUAL'[Leave From DATE]), 
        '3 COMBINED Record ANNUAL'[Leave From DATE] <= ( '.DEPT LEAVE APPS'[Date] ) ), 

        //...and then only keep values where leave ends on or after the date or has no end date
        FILTER( VALUES('3 COMBINED Record ANNUAL'[Leave To DATE]),
        '3 COMBINED Record ANNUAL'[Leave To DATE] >= ( '.DEPT LEAVE APPS'[Date] ) ), 
        
        //...and ignore the half-day leave edge cases (accounted for below)
        FILTER(VALUES('3 COMBINED Record ANNUAL'[Duration]), 
        not('3 COMBINED Record ANNUAL'[Duration] = 0.5)),

        //...and now we apply the Country and Category filter.
        FILTER( VALUES('3 COMBINED Record ANNUAL'[Country]), 
        '3 COMBINED Record ANNUAL'[Country] == ('.DEPT LEAVE APPS'[Country] ) ),

        FILTER( VALUES('3 COMBINED Record ANNUAL'[Department]), 
        '3 COMBINED Record ANNUAL'[Department] == ('.DEPT LEAVE APPS'[Department] ) )
    ) //endCalculate

    +

    //annual leaves that are only 0.5 day offs - exactly same as above except we only countrows that are 0.5 day offs AND divide by 2 at the end 
    (
        CALCULATE( COUNTROWS('3 COMBINED Record ANNUAL' ), //count total number of leave applications

            //only keep values where leave started on or before the date
            FILTER( VALUES('3 COMBINED Record ANNUAL'[Leave From DATE]), 
            '3 COMBINED Record ANNUAL'[Leave From DATE] <= ( '.DEPT LEAVE APPS'[Date] ) ), 

            //...and then only keep values where leave ends on or after the date or has no end date
            FILTER( VALUES('3 COMBINED Record ANNUAL'[Leave To DATE]),
            '3 COMBINED Record ANNUAL'[Leave To DATE] >= ( '.DEPT LEAVE APPS'[Date] ) ), 
            
            //...and ignore the half-day leave edge cases (accounted for below)
            FILTER(VALUES('3 COMBINED Record ANNUAL'[Duration]), 
            ('3 COMBINED Record ANNUAL'[Duration] = 0.5 ) ),

            //...and now we apply the Country and Category filter.
            FILTER( VALUES('3 COMBINED Record ANNUAL'[Country]), 
            '3 COMBINED Record ANNUAL'[Country] == ('.DEPT LEAVE APPS'[Country] ) ),

            FILTER( VALUES('3 COMBINED Record ANNUAL'[Department]), 
            '3 COMBINED Record ANNUAL'[Department] == ('.DEPT LEAVE APPS'[Department] ) )
        ) //endCalculate
    ) / 2

)

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

// if there is no leaves taken on that day OR that day falls on a weekend/public holiday, return 0.
RETURN 
if
(
    OR(ISBLANK(calcLeave), isSatSunPubHol),
    0, 
    calcLeave
)

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

[garythomannCoGC]

Custom (Reusable) DAX Function   please vote :}

[v-angzheng-msft · Accepted solution]

Hi, @bleow 

You can separate the reusable part with the measure value, and then call the measure, or create a custom function in the power query.

I tried to make some modifications to your code, you can follow this idea and change it to suit you, hope this helps.

ANNUAL LEAVE =
VAR isSatSunPubHol = ( '.DEPT LEAVE APPS'[isSatSun] || '.DEPT LEAVE APPS'[isPubHol] )
VAR _F1 =
    FILTER (
        '3 COMBINED Record ANNUAL',
        '3 COMBINED Record ANNUAL'[Leave From DATE] <= ( '.DEPT LEAVE APPS'[Date] )
            && '3 COMBINED Record ANNUAL'[Leave To DATE] >= ( '.DEPT LEAVE APPS'[Date] )
            && NOT ( '3 COMBINED Record ANNUAL'[Duration] = 0.5 )
            && '3 COMBINED Record ANNUAL'[Country] == ( '.DEPT LEAVE APPS'[Country] )
            && '3 COMBINED Record ANNUAL'[Department] == ( '.DEPT LEAVE APPS'[Department] )
    )
VAR _F2 =
    FILTER (
        '3 COMBINED Record ANNUAL',
        '3 COMBINED Record ANNUAL'[Leave From DATE] <= ( '.DEPT LEAVE APPS'[Date] )
            && '3 COMBINED Record ANNUAL'[Leave To DATE] >= ( '.DEPT LEAVE APPS'[Date] )
            && ( '3 COMBINED Record ANNUAL'[Duration] = 0.5 )
            && '3 COMBINED Record ANNUAL'[Country] == ( '.DEPT LEAVE APPS'[Country] )
            && '3 COMBINED Record ANNUAL'[Department] == ( '.DEPT LEAVE APPS'[Department] )
    )
VAR calcLeave =
    (
        CALCULATE ( COUNTROWS ( '3 COMBINED Record ANNUAL' ), _F1 )
            + ( CALCULATE ( COUNTROWS ( '3 COMBINED Record ANNUAL' ), _F2 ) ) / 2
    )
RETURN
    IF ( OR ( ISBLANK ( calcLeave ), isSatSunPubHol ), 0, calcLeave )

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bleow]

Thanks! Is there any way to apply a filter to the _F1/2 vars after they are created? Var _F1 and _F2 are identical except the 

 && NOT ( '3 COMBINED Record ANNUAL'[Duration] = 0.5 )

 , so I'm wondering if I can do something like:

VAR _F0 =
    FILTER (
        '3 COMBINED Record ANNUAL',
        '3 COMBINED Record ANNUAL'[Leave From DATE] <= ( '.DEPT LEAVE APPS'[Date] )
            && '3 COMBINED Record ANNUAL'[Leave To DATE] >= ( '.DEPT LEAVE APPS'[Date] )
            && '3 COMBINED Record ANNUAL'[Country] == ( '.DEPT LEAVE APPS'[Country] )
            && '3 COMBINED Record ANNUAL'[Department] == ( '.DEPT LEAVE APPS'[Department] )
    )

VAR calcLeave =
    (
        CALCULATE(
           COUNTROWS('3 COMBINED Record ANNUAL'),
           //_F1
           FILTER( VALUES(_F0[Duration] ),
           NOT _F0[Duration] = 0.5
        )
        + 
       (CALCULATE( 
           COUNTROWS('3 COMBINED Record ANNUAL'),
           //_F2
           FILTER( VALUES(_F0[Duration] ),
           _F0[Duration] = 0.5
        ) ) / 2
    )

 where I factorise out the common filter conditions as one VAR and then apply the last unique filter condition only during calculate(), preventing code rewrite?

I tried the above but got the error "cannot find name [Duration]".

[v-angzheng-msft]

Hi, @bleow 

Unfortunately, _F0 returns a table but it's not the table name, so you can't use it that way.

Maybe something like this:

ANNUAL LEAVE =
VAR isSatSunPubHol = ( '.DEPT LEAVE APPS'[isSatSun] || '.DEPT LEAVE APPS'[isPubHol] )
VAR _F0 =
    FILTER (
        '3 COMBINED Record ANNUAL',
        '3 COMBINED Record ANNUAL'[Leave From DATE] <= ( '.DEPT LEAVE APPS'[Date] )
            && '3 COMBINED Record ANNUAL'[Leave To DATE] >= ( '.DEPT LEAVE APPS'[Date] )
            && '3 COMBINED Record ANNUAL'[Country] == ( '.DEPT LEAVE APPS'[Country] )
            && '3 COMBINED Record ANNUAL'[Department] == ( '.DEPT LEAVE APPS'[Department] )
    )
VAR calcLeave =
    (
        CALCULATE (
            COUNTROWS ( '3 COMBINED Record ANNUAL' ),
            _F0,
            FILTER (
                '3 COMBINED Record ANNUAL',
                '3 COMBINED Record ANNUAL'[Duration] = 0.5
            )
        )
            + (
                CALCULATE (
                    COUNTROWS ( '3 COMBINED Record ANNUAL' ),
                    _F0,
                    FILTER (
                        '3 COMBINED Record ANNUAL',
                        '3 COMBINED Record ANNUAL'[Duration] <> 0.5
                    )
                )
            ) / 2
    )
RETURN
    IF ( OR ( ISBLANK ( calcLeave ), isSatSunPubHol ), 0, calcLeave )

Best Regards,
Community Support Team _ Zeon Zheng

[amitchandak]

@bleow , You can create one power query.  DAX I doubt