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 re...
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.

     

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.

     

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

      I tried: 

      CALCULATE(IF(ISBLANK(COUNTA(qryCompliance[FedRampSrgL2Date])),0,1))
      and it return 0 for the empty/null cells.  If there is a date, it returns 1.
       
      However, when I update the formula, it doesn't return a 0 for empty/null cells. 
       
      Compliance_WW =
      VAR now = format(NOW(),"MM/dd/YYYY")
      RETURN
          IF(
              CALCULATE(IF(ISBLANK(COUNTA(qryCompliance[L2Date])),0,1)),
              SWITCH(
      TRUE(),
      FORMAT(qryCompliance[L2Date],"MM/dd/YYYY") = now, 1,
               Format(qryCompliance[L2Date],"MM/dd/YYYY") < now, 1,
                  FOrmat(qryCompliance[L2Date],"MM/dd/YYYY") > now, 2
              )
          
          )
      • edhans's avatar
        edhans
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Try this - the COUNTA() is the same as COUNT() in DAX unless it is a boolean field. It doesn't work like it does in Excel. 

         

         

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