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
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's avatar
ArchStanton
Power Participant
1 year ago

Thanks, much appreciated! 👍