Help need with long DAX code

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!

Need Help

Show and Tell

rajendraongole1
1 year ago
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.

6 Replies

johnt75

Super User

1 year ago

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

Power Participant

1 year ago

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

Super User

1 year ago

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

rajendraongole1
Super User
1 year ago
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
  Power Participant
  1 year ago
  This is great thanks!!

Forum Discussion

Help need with long DAX code