Forum Discussion

emiec's avatar
emiec
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago
Solved

Help with DAX and Blank

Hi - I wrote the code below to assign an integer-based off L4 date.  For some L4 dates, they are blank.  To deal with blanks, I wrote an ISBLANK statement to return a 0. However, it doesn't.  Just returns blank/null value.   

 

Any suggestions?

 

Compliance  =
VAR now = format(NOW(),"MM/dd/YYYY")
RETURN
    IF(
        ISBLANK(Format(qryCompliance[L4Date],"MM/dd/YYYY")),0,
        SWITCH(
TRUE(),
FORMAT(qryCompliance[L4Date],"MM/dd/YYYY") = now,1,
         Format(qryCompliance[L4Date],"MM/dd/YYYY") < now, 1,
            FOrmat(qryCompliance[L4Date],"MM/dd/YYYY") > now, 2
        )
    
    )
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi emiec ,

     

    Please remove the Format to work on it. If we use Format, it will be text format, then we cannot get excepted result we need.

    Compliance = 
VAR now =
    DATEVALUE ( NOW () )
RETURN
    IF (
        ISBLANK ( qryCompliance[L4Date] ),
        0,
        SWITCH (
            TRUE (),
            'qryCompliance'[L4Date] = now, 1,
            'qryCompliance'[L4Date] < now, 1,
            'qryCompliance'[L4Date] > now, 2
        )
    )

     

    For more details, please check the pbix as attached.

     

6 Replies

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Just check the field for a blank, not the output of a FORMAT() function.

     

    Compliance =
VAR now =
    FORMAT (
        NOW (),
        "MM/dd/YYYY"
    )
RETURN
    IF (
        ISBLANK ( [L4Date] ),
        0,
        SWITCH (
            TRUE (),
            FORMAT (
                qryCompliance[L4Date],
                "MM/dd/YYYY"
            ) = now, 1,
            FORMAT (
                qryCompliance[L4Date],
                "MM/dd/YYYY"
            ) < now, 1,
            FORMAT (
                qryCompliance[L4Date],
                "MM/dd/YYYY"
            ) > now, 2
        )
    )

     

     

    • emiec's avatar
      emiec
      Icon for Microsoft Employee rankMicrosoft Employee
      6 years ago

      Thanks for the suggestion. I removed the FORMAT portion of the code.  However, I'm still getting blank values. 

      • edhans's avatar
        edhans
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Are you sure they are blank? Empty is not the same as null, and I just ran a quick test:

        isblank(null) is true

        Isblank("") is false

         

        but both visually show the same in DAX. They do not in Power Query. null shows null, and "" shows an empty cell.

         

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi emiec ,

     

    Please remove the Format to work on it. If we use Format, it will be text format, then we cannot get excepted result we need.

    Compliance = 
VAR now =
    DATEVALUE ( NOW () )
RETURN
    IF (
        ISBLANK ( qryCompliance[L4Date] ),
        0,
        SWITCH (
            TRUE (),
            'qryCompliance'[L4Date] = now, 1,
            'qryCompliance'[L4Date] < now, 1,
            'qryCompliance'[L4Date] > now, 2
        )
    )

     

    For more details, please check the pbix as attached.