Forum Discussion
Multiple Condition IF using switch true
Anonymous This looks like a reasonable interpretation for how to handle multiple values. However, the naked P1, P2 column references will probably still cause problems.
This is what my version would look like using this logic:
Migration Status = VAR RB = VALUES ( 'Consolidated Sheet'[RB] ) VAR GTP = VALUES ( 'Consolidated Sheet'[GTP] ) VAR GBS = VALUES ( 'Consolidated Sheet'[GBS] ) VAR ITL = VALUES ( 'Consolidated Sheet'[ITL - EXP non premium - EITL - Non premium - Binder Price Plan] ) VAR EXP_B = VALUES ( 'Consolidated Sheet'[EXP - Binder Price Plan] ) VAR EITL = VALUES ( 'Consolidated Sheet'[EITL - Binder Price Plan] ) VAR P1_Max = SELECTEDVALUE ( 'IRR Retina'[P1 Max - Binder Price Plan] ) VAR P2_Max = SELECTEDVALUE ( 'IRR Retina'[P2 Max - Binder Price Plan] ) RETURN IF ( ISBLANK ( P1_Max ) || ISBLANK ( P2_Max ), BLANK (), SWITCH ( TRUE (), P1_Max = P2_Max, "Retained", P1_Max = EXP_B && P2_Max IN UNION ( EITL, ITL, GTP, GBS ), "Downgrade", P1_Max = EITL && P2_Max IN UNION ( ITL, GTP, GBS ), "Downgrade", P1_Max = ITL && P2_Max IN UNION ( GTP, GBS ), "Downgrade", P1_Max = EXP_B && P2_Max = RB, "Upgrade", P1_Max = EITL && P2_Max IN UNION ( RB, EXP_B ), "Upgrade", P1_Max = ITL && P2_Max IN UNION ( RB, EXP_B, EITL ), "Upgrade", "Cancelled" ) )In this, P1_Max and P2_Max cannot have multiple values but the rest of the variables can.
I prefer to use SELECTEDVALUES instead of VALUES since it's just a shortcut for IF ( HASONEVALUE ( ... ), VALUES ( ... ) ).
Try this:
Migration Status =
VAR RB = SELECTEDVALUE ( 'Consolidated Sheet'[RB] )
VAR GTP = SELECTEDVALUE ( 'Consolidated Sheet'[GTP] )
VAR GBS = SELECTEDVALUE ( 'Consolidated Sheet'[GBS] )
VAR ITL =
SELECTEDVALUE ( 'Consolidated Sheet'[ITL - EXP non premium - EITL - Non premium - Binder Price Plan] )
VAR EXP_B = SELECTEDVALUE ( 'Consolidated Sheet'[EXP - Binder Price Plan] )
VAR EITL = SELECTEDVALUE ( 'Consolidated Sheet'[EITL - Binder Price Plan] )
VAR P1_Max = SELECTEDVALUE ( 'IRR Retina'[P1 Max - Binder Price Plan] )
VAR P2_Max = SELECTEDVALUE ( 'IRR Retina'[P2 Max - Binder Price Plan] )
RETURN
SWITCH (
TRUE (),
P1_Max = P2_Max, "Retained",
P1_Max = EXP_B && P2_Max IN { EITL, ITL, GTP, GBS }, "Downgrade",
P1_Max = EITL && P2_Max IN { ITL, GTP, GBS }, "Downgrade",
P1_Max = ITL && P2_Max IN { GTP, GBS }, "Downgrade",
P1_Max = EXP_B && P2_Max = RB, "Upgrade",
P1_Max = EITL && P2_Max IN { RB, EXP_B }, "Upgrade",
P1_Max = ITL && P2_Max IN { RB, EXP_B, EITL }, "Upgrade",
"Cancelled"
)Wow this is much cleaner and I love the SELECTEDVALUES! But we are so close, it looks like im getting all "retained" and when I removed that (too see if it just wasnt reading the variables), I got all "Downgrade". It looks like it taking the first value and not going down to the other conditions.
Do you have exactly one value selected for the relevant variables?
If you're getting all "Retained", then I'm guessing that's because P1_Max and P2_Max are both blank because SELECTEDVALUES is detecting multiple values for them.
No not just one, each variable has multiple values (RB has 4 but ITL has 38 for example) inside of it.
OK. I don't know how the logic is supposed to work if there are multiple values in your case. It definitely gets more complicated since checking equality doesn't really work.