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_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? 
 
Need Help
  • rajendraongole1's avatar
    rajendraongole1
    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 

1 Reply

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    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