Forum Discussion

Michael_DOC's avatar
Michael_DOC
Regular Visitor
1 year ago
Solved

Return nonblank value issues.

Below is my Dax formula:    Medical Score = VAR ClosestRow =     CALCULATE (         FIRSTNONBLANK(SCORES[SCOR_MEDICAL], 1),           FILTER (             SCORES,             SCORES[SCOR_P...
  • rajendraongole1's avatar
    1 year ago

    Hi Michael_DOC - you can modify the logic to filter out blank values in the FIRSTNONBLANK part of your measure. 

    ceck the below measure:

    Medical Score =
    VAR ClosestRow =
    CALCULATE (
    FIRSTNONBLANK(
    SCORES[SCOR_MEDICAL],
    NOT (ISBLANK(SCORES[SCOR_MEDICAL])) // Exclude blank values
    ),
    FILTER (
    SCORES,
    SCORES[SCOR_PER_ID] = PS[Number] &&
    ABS(DATEDIFF(PS[OffenseDt], SCORES[SCOR_EFF_DATE], DAY)) =
    MINX (
    FILTER (
    SCORES,
    SCORES[SCOR_PER_ID] = PS[Number] &&
    NOT (ISBLANK(SCORES[SCOR_MEDICAL])) // Exclude blank values in comparison
    ),
    ABS(DATEDIFF(PS[OffenseDt], SCORES[SCOR_EFF_DATE], DAY))
    )
    )
    )
    RETURN
    ClosestRow

     

    Hope it works