Help need with long DAX code

[ArchStanton]

Hi,

I'm struggling to interpret this DAX code that my predecessor wrote in the PowerBI report i'm using. Can someone possibly add comments to it thats states what each section does so i can copy & paste it back in please?
The purpose of the code is to calculate the number of days the item spent in the Adjudication team.

The various ISBLANK functions are making this hard to decipher as well as its length.

Days in Adj = 
IF (
    NOT ( ISBLANK ( 'Cases'[datepassedtoadjudication] ) )
        || NOT ( ISBLANK ( 'Cases'[dateassignedforadjudication] ) )
            || NOT ( ISBLANK ( 'Cases'[dateacceptedforinvestigation] ) ),
    IF (
        ISBLANK ( 'Cases'[Resolution Date] ),
        DATEDIFF (
            IF (
                ISBLANK ( 'Cases'[datepassedtoadjudication] ),
                IF (
                    ISBLANK ( 'Cases'[dateassignedforadjudication] ),
                    'Cases'[dateacceptedforinvestigation],
                    'Cases'[dateassignedforadjudication]
                ),
                'Cases'[datepassedtoadjudication]
            ),
            NOW (),
            DAY
        ),
        DATEDIFF (
            IF (
                ISBLANK ( 'Cases'[datepassedtoadjudication] ),
                IF (
                    ISBLANK ( 'Cases'[dateassignedforadjudication] ),
                    'Cases'[dateacceptedforinvestigation],
                    'Cases'[dateassignedforadjudication]
                ),
                'Cases'[datepassedtoadjudication]
            ),
            'Cases'[Resolution Date],
            DAY
        )
    ),
    BLANK ()
)

Thanks!

[rajendraongole1 · Accepted solution]

Hi @ArchStanton - Absolutely! inline comments you can reuse it eg: // . This measure calculates the number of days a case spent

Days in Adj =
IF (
// Check if any of the adjudication-related date fields are not blank
NOT ( ISBLANK ( 'Cases'[datepassedtoadjudication] ) )
|| NOT ( ISBLANK ( 'Cases'[dateassignedforadjudication] ) )
|| NOT ( ISBLANK ( 'Cases'[dateacceptedforinvestigation] ) ),

// If the case has not been resolved yet (no resolution date), calculate days until today
IF (
ISBLANK ( 'Cases'[Resolution Date] ),
DATEDIFF (
// Determine the earliest adjudication date to start counting from
IF (
ISBLANK ( 'Cases'[datepassedtoadjudication] ),
IF (
ISBLANK ( 'Cases'[dateassignedforadjudication] ),
// Fallback if both other dates are blank
'Cases'[dateacceptedforinvestigation],
// Use dateassignedforadjudication if available
'Cases'[dateassignedforadjudication]
),
// Prefer datepassedtoadjudication if available
'Cases'[datepassedtoadjudication]
),
NOW (), // Use current date if no resolution
DAY
),

// If the case has been resolved, calculate days until resolution date
DATEDIFF (
IF (
ISBLANK ( 'Cases'[datepassedtoadjudication] ),
IF (
ISBLANK ( 'Cases'[dateassignedforadjudication] ),
'Cases'[dateacceptedforinvestigation],
'Cases'[dateassignedforadjudication]
),
'Cases'[datepassedtoadjudication]
),
'Cases'[Resolution Date],
DAY
)
),

// If no relevant adjudication dates exist, return blank
BLANK ()
)

HOpe this helps.

[ArchStanton]

This is great thanks!!

[johnt75]

This is working out which dates to compare based on whether the dates are set or not. For the start date, it will pick the first non-blank value from passed to adjudication, assigned for adjudication or accepted for investigation.

For the end date, it will pick either resolution date if that is set, otherwise it will use today's date.

This can be greatly simplified by using the COALESCE function, which returns the first non-blank value in the list or parameters you pass in. e.g.

Days in Adj =
VAR StartDateToUse =
    COALESCE (
        'Cases'[datepassedtoadjudication],
        'Cases'[dateassignedforadjudication],
        'Cases'[dateacceptedforinvestigation]
    )
VAR EndDateToUse =
    COALESCE ( 'Cases'[Resolution Date], TODAY () )
VAR Result =
    IF (
        NOT ISBLANK ( StartDateToUse ),
        DATEDIFF ( StartDateToUse, EndDateToUse, DAY )
    )
RETURN
    Result

[ArchStanton]

Thanks for this, I didn't realise things could be simplified so much using COALESCE, could you please kindly add comments to it so I can get my head around it?

[johnt75]

Days in Adj =
VAR StartDateToUse =
    // Choose the first of these dates which has a non-blank value
    COALESCE (
        'Cases'[datepassedtoadjudication],
        'Cases'[dateassignedforadjudication],
        'Cases'[dateacceptedforinvestigation]
    )
VAR EndDateToUse =
    // If resolution is non-blank then use that, otherwise use today
    COALESCE (
        'Cases'[Resolution Date],
        TODAY ()
    )
VAR Result =
    IF (
        NOT ISBLANK ( StartDateToUse ),
        // Only return a result if there is a valid starting date
        DATEDIFF (
            StartDateToUse,
            EndDateToUse,
            DAY
        )
    )
RETURN
    Result

I hope this clears it up

[ArchStanton]

Thanks, much appreciated! 👍