Forum Discussion

ArchStanton's avatar
ArchStanton
Power Participant
1 year ago
Solved

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's avatar
    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's avatar
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