Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Simply query DAX - IF null...

HI,

 

I have problem with this: 

Column2 = if('merge1'[Components.name]; "1";"0")
 
I get info:
You cannot create a single value for the 'Components.name' column in the 'merge1' table. This may include the fact that the calculation formula refers to a table containing multiple values without aggregation summaries, takes into account how the minimum value, maximum value, number or sum can involve the use of a single result.
 
I changed this query to this:
"Column2 = if(ISBLANK(COUNTBLANK('merge1'[Components.name])); "1";"0")"
 
But I can't add it to the filter.
 
An effect that I would like to get.
 
Components.name | Column2
null              | 0
Adobe             | 1
Windows           | 1
null              | 0
 
May I ask you for help?
Need Help
  • AlB's avatar
    AlB
    6 years ago

    Hi Anonymous 

    Perhaps

    Column2 = IF (ISBLANK('merge1'[Components.name]); "1"; "0" )

     

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

     

  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    It seems you create a measure not a column, so it throw the error.

    If you create a measure below, it can be right.

    but if the [Components.name] column is of text type, null value doesn't mean to be blank, it is a "null" text value.

    you can create a measure like

    Measure = if(MAX('Table'[Components.name])<>"null", "1","0")

    But measures can't be added into slicer, it just can be added into visual level filter.

    To create a column and add it into a slicer,

    Column = if('Table'[Components.name]<>"null", "1","0")

or 
Column = if(ISBLANK('Table'[Components.name]), "0","1")

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • AlB's avatar
    AlB
    Community Champion
    6 years ago

    Hi Anonymous 

    Perhaps

    Column2 = IF (ISBLANK('merge1'[Components.name]); "1"; "0" )

     

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      HI AlB 

       

      I get information

      "You cannot specify a single value for the 'Components.name' column in the 'merge1' table. This may be due to the fact that the measurement formula refers to a column containing many values without a specific aggregation, such as a minimum value, maximum value, number or sum, allowing to obtain a single result."

      • AlB's avatar
        AlB
        Community Champion
        6 years ago

        Hi Anonymous 

        Are you creating a calculated column or a measure?? That sounds like the message you would get when creating a measure. It should be fine with a calculated column

        Please mark the question solved when done and consider giving kudos if posts are helpful.

        Contact me privately for support with any larger-scale BI needs, tutoring, etc.

        Cheers 

         

    • edhans's avatar
      edhans
      Community Champion
      6 years ago

      Anonymous please post a sample of your data and your expected result. Also be specific about where you are doing this. To AlB 's point, DAX for columns and measures is different becuase the former uses row context and the latter uses filter context, two totally different things. Formulas perfectly valid in one will fail in the other.

      How to get good help fast. Help us help you.
      How to Get Your Question Answered Quickly
      How to provide sample data in the Power BI Forum

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi Anonymous 

    It seems you create a measure not a column, so it throw the error.

    If you create a measure below, it can be right.

    but if the [Components.name] column is of text type, null value doesn't mean to be blank, it is a "null" text value.

    you can create a measure like

    Measure = if(MAX('Table'[Components.name])<>"null", "1","0")

    But measures can't be added into slicer, it just can be added into visual level filter.

    To create a column and add it into a slicer,

    Column = if('Table'[Components.name]<>"null", "1","0")

or 
Column = if(ISBLANK('Table'[Components.name]), "0","1")

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.