Return nonblank value issues.

[Michael_DOC]

Below is my Dax formula: 

Medical Score =

VAR ClosestRow =

    CALCULATE (

        FIRSTNONBLANK(SCORES[SCOR_MEDICAL], 1),  

        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]

                ),

                ABS(DATEDIFF(PS[OffenseDt], SCORES[SCOR_EFF_DATE], DAY))

            )

        )

    )

RETURN

    ClosestRow

I want the formula to make ID1 = ID2 without creating a relationship (many to many) and Return ValueB (SCORES[SCOR_MEDICAL])  for DateA (PS[OffenseDt]) is closest to DateB (SCORES[SCOR_EFF_DATE]) . If ValueB is blank, return the next Value in the column. The above Dax formula works 99% but I am still getting a few blank values based on my data. 

How do I have my formula skip blanks and return the next whole number value without removing empties from my table? 

[rajendraongole1 · Accepted solution]

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