Help with IF DAX

[daledale]

Hi everyone,

I'm stuck trying to generate a Measure with IF.

What I want to do would be something like IF the Variance is <0,"Better","Worse".

The issue is that the Variance is an attribute in an unpivoted table. Ex:
NODE-Category-Attribute -   Value
1              A          Q1                  $
1              A           Q2                  $
1              A          Forecast           $
1              A          Variance          $
1              B          Q1                  $
1              B           Q2                  $
1              B          Forecast           $
1              B          Variance          $

And so on... 
So what I want is to create an IF Measure that shows either "Better" or "Worse" according to the Value for the Variance Attribute.. 

Is there a way to do that? I've tryied splitting the data in different tables also but I'm not able to solve it... 

Thanks in advance

[dax · Accepted solution]

Hi daledale, 

Yes, you could try to create a calculated column like below to get your result 

Column = if(T5[Attribute]="Variance" && T5[Value]>0, 1,2)

or you could try to use M code to achieve this goal

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIE4kAQw9BUKVYHScwIJGaAIuaWX5SanFhcApJBkQhLLMpMzEtOBTIRpjjBTTZCFTPCUIbVYCdUg3WBWmIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Node = _t, Category = _t, Attribute = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Node", Int64.Type}, {"Category", type text}, {"Attribute", type text}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Attribute]="Variance" and  [Value]>0 then 1 else 2)
in
    #"Added Custom"

By the way, what did you mean of "I've tryied splitting the data in different tables also but I'm not able to solve it"? If possible, could you please inform me more detailed information(such as your expected output).

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[daledale]

Thanks! I've figured it out with your formula. 

[az38]

hi @daledale 

if i understand you correct try to create a new calculated column

Column = IF(
[Attribute] = "Variance",
IF([Value] < 0,"Better","Worse"),
""
)

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn