cancel
Showing results for
Did you mean:
Highlighted
Helper I

## DAX for If with Filter

Hi,

I am little new to DAX so need help. I want to right a DAX showing static values with an IF condition. What I am after is below.

NewColumn = If the value in the color column is "Red" then show 50 else show 100

what I wrote was

NewColumn = If( Filter (Table,ColumnName = "Red"),50,100)

However, it gave me an error saying "The expression referes to multiple columns. Multiple columns cannot be converted to a scalar value."

Please can some one help me with correct DAX for what I want to acheive here?

Thanks

Yash

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper I

Hi All,

I have found a solution to this myself...

I wrote few DAX funtions to create measures with a static numbers.  This then I used in a Stacked bar chart, with bit of formating and it worked for me.

Thanks to all who took time to read my question and tried to help me.

Thanks & Regards

Yash

6 REPLIES 6
Highlighted
Resolver IV

The FILTER function returns a table, and the IF doesn't know how to work against an entire table (the multiple columns error).

Are you essentially trying to do this?

```Item   |   Color   |   NewColumn
1          Red           50
2          Red           50
3          Blue          100```

If this is the case, you can simple do:

`NewColumn = IF( [Color] = "Red", 50, 100 )`

When you are creating a calculated column, it is calculating the value of the cell within a row context.  This means that when it's calculating the value for NewColumn in Row 1 (from the example above), it knows about the [Item] and [Color] column.

Dan Malagari
Highlighted
Helper I

Hi Malagari,

Thanks for replying the solution you mentioned below did not work. Since Color is a column in my data model it does not show up untill table name is specified and that will work only when I am trying to put a function ahead of it that is either SUM or something like that.

Thanks

Yash

Highlighted
Resolver IV

@Sarpotdary Thanks for the info.  I'll need to know a little more about your table structure.  What table is the Color column in, where are you trying to add this 'NewColumn', and how do these tables relate together?

Dan Malagari
Highlighted
Helper I

@malagari I just have one single table which is having this column called as Color. The data is for 2017 however I need to create 5 charts for 2013 to 2016 where 2017 would take the data from the table itself when I put it into a stacked bar chart. However for rest of the years I need to put a measure which will show the stacked bar chart with the bar being split into two parts 50 for Red and 100 for Green (considering I only have two values in color column, Red and Green). My intention of writting a DAX was to have "fake" values but still splitting them by Red and Green values from Color column.

Hope this helps.

Thanks

Yash

Highlighted
Helper I

Hi, can someone help me here please? I am stuck at this stage and cannot move ahed in my project. Any help would be highly appreciated.

Thanks

Yash

Highlighted
Helper I

Hi All,

I have found a solution to this myself...

I wrote few DAX funtions to create measures with a static numbers.  This then I used in a Stacked bar chart, with bit of formating and it worked for me.

Thanks to all who took time to read my question and tried to help me.

Thanks & Regards

Yash

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors