Forum Discussion

ToddMate's avatar
ToddMate
Helper II
7 years ago
Solved

Help: Where date is null.

Hi Guys,

 

Hopefully a quick one, i know the measure below doesn't work but for the life of me i can't work out how to say "where the connect date is null" ... Can anyone help out here please.

 

Its a date field which is only populated when the service is connected, hence it is blank / null when it hasn't been connected.

 

 

Connects = CALCULATE(SUM(X_Detail[Count]),FILTER(X_Detail,X_Detail[Product] in {"A,"B"}),FILTER(X_Detail,X_Detail[Connect Date] is null)
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, ToddMate 

    Just adjust your formula as below:

    Connects = CALCULATE(SUM('X_Detail'[Count]),FILTER('X_Detail','X_Detail'[Product] in {"A","B"}),FILTER('X_Detail','X_Detail'[Connect Date] =BLANK() ))

    or use ISBLANK Funtion 

    Connects = CALCULATE(SUM('X_Detail'[Count]),FILTER('X_Detail','X_Detail'[Product] in {"A","B"}),FILTER('X_Detail',ISBLANK('X_Detail'[Connect Date] ) ))

    https://docs.microsoft.com/en-us/dax/isblank-function-dax

     

    is null couldn't be used in DAX.

     

    Best Regards,

    Lin

3 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    7 years ago

    Hey,

     

    just to be sure: the table X_Detail contains rows where the column "Connect Date" contains blank values?

     

    Can you pleae provide a pbix file that contains some sample data, upload the file onedrive or dropbox and share the link.

     

    Regards,

    Tom

     

    • ToddMate's avatar
      ToddMate
      Helper II
      7 years ago

      Hi TomMartens 

       

      Yes that is correct, x_detail contains rows, one of the columns in this table is the connect date which can either contain a date or be null.

       

      I can't upload the .pbix at this stage i'm sorry but i have attached a screenshot of the table field lists.

       

      • v-lili6-msft's avatar
        v-lili6-msft
        Community Support
        7 years ago

        hi, ToddMate 

        Just adjust your formula as below:

        Connects = CALCULATE(SUM('X_Detail'[Count]),FILTER('X_Detail','X_Detail'[Product] in {"A","B"}),FILTER('X_Detail','X_Detail'[Connect Date] =BLANK() ))

        or use ISBLANK Funtion 

        Connects = CALCULATE(SUM('X_Detail'[Count]),FILTER('X_Detail','X_Detail'[Product] in {"A","B"}),FILTER('X_Detail',ISBLANK('X_Detail'[Connect Date] ) ))

        https://docs.microsoft.com/en-us/dax/isblank-function-dax

         

        is null couldn't be used in DAX.

         

        Best Regards,

        Lin