Forum Discussion

daledale's avatar
daledale
Frequent Visitor
6 years ago
Solved

Help with IF DAX

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

Need Help
  • dax's avatar
    dax
    6 years ago

    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.

     

3 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    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

  • dax's avatar
    dax
    Community Support
    6 years ago

    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's avatar
      daledale
      Frequent Visitor
      6 years ago

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