Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

If Statement in Measure

Hello, I have example Table:
Name, Score, Goal, GoalFlag

I need to create a measure that says if GoalFlag ="Over" subtract Goal from Score else if GoalFlag= "Under" then subtract Score from Goal

In excel it would be something like = if(GoalFlag="Over",Score-Goal,if(GoalFlag="Under",Goal-Score,Score-Goal)

 

Any help would be apprecaiated 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    According to my understanding, if [GoalFlag]="Under", then [Goal]- [Score] else [Score]-[Goal] ,right?

    If so ,you could simply use :

    Measure = 
IF (
    MAX ( 'Table'[GoalFlag] ) = "Under",
    MAX ( 'Table'[Goal] ) - MAX ( 'Table'[Score] ),
    MAX ( 'Table'[Score] ) - MAX ( 'Table'[Goal] )
)

    It is equivalent to the following writing:

    Measure =
IF (
    MAX ( 'Table'[GoalFlag] ) = "Over",
    MAX ( 'Table'[Score] ) - MAX ( 'Table'[Goal] ),
    IF (
        MAX ( 'Table'[GoalFlag] ) = "Under",
        MAX ( 'Table'[Goal] ) - MAX ( 'Table'[Score] ),
        MAX ( 'Table'[Score] ) - MAX ( 'Table'[Goal] )
    )
)

     

    Or use SWITCH():

    Use Switch =
SWITCH (
    MAX ( 'Table'[GoalFlag] ),
    "Over", MAX ( 'Table'[Score] ) - MAX ( 'Table'[Goal] ),
    "Under", MAX ( 'Table'[Goal] ) - MAX ( 'Table'[Score] ),
    MAX ( 'Table'[Score] ) - MAX ( 'Table'[Goal] )
)

     

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

2 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    Anonymous you can do the same thing here, just add another column:

     

    New Column = 
IF ( Table[GoalFlag] = "Under",  Table[Goal] - Table[Score], Table[Score]-Table[Goal )

     

    ✨ Follow us on LinkedIn

     

    Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    According to my understanding, if [GoalFlag]="Under", then [Goal]- [Score] else [Score]-[Goal] ,right?

    If so ,you could simply use :

    Measure = 
IF (
    MAX ( 'Table'[GoalFlag] ) = "Under",
    MAX ( 'Table'[Goal] ) - MAX ( 'Table'[Score] ),
    MAX ( 'Table'[Score] ) - MAX ( 'Table'[Goal] )
)

    It is equivalent to the following writing:

    Measure =
IF (
    MAX ( 'Table'[GoalFlag] ) = "Over",
    MAX ( 'Table'[Score] ) - MAX ( 'Table'[Goal] ),
    IF (
        MAX ( 'Table'[GoalFlag] ) = "Under",
        MAX ( 'Table'[Goal] ) - MAX ( 'Table'[Score] ),
        MAX ( 'Table'[Score] ) - MAX ( 'Table'[Goal] )
    )
)

     

    Or use SWITCH():

    Use Switch =
SWITCH (
    MAX ( 'Table'[GoalFlag] ),
    "Over", MAX ( 'Table'[Score] ) - MAX ( 'Table'[Goal] ),
    "Under", MAX ( 'Table'[Goal] ) - MAX ( 'Table'[Score] ),
    MAX ( 'Table'[Score] ) - MAX ( 'Table'[Goal] )
)

     

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