Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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
malagari
Responsive Resident
Responsive Resident

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
Anonymous
Not applicable

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

@Anonymous 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
Anonymous
Not applicable

@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

Anonymous
Not applicable

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

Anonymous
Not applicable

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.