cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
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

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

 

View solution in original post

6 REPLIES 6
Highlighted
Resolver IV
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
Consultant at Headspring
Highlighted

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

@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
Consultant at Headspring
Highlighted

@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

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

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

 

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors