Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
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.
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?
@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
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
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
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |