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 ...

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.

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

ArchStanton
Power Participant
1 year ago
Thanks, much appreciated! 👍

Forum Discussion

Help need with long DAX code