Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Multiple Condition IF using switch true

Hey, trying to do a multiple condition Switch True Statement. In the beginning of the contract a client had a product that sits in P1, then during their contract period they either kept the same prod...
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    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.

VahidDM's avatar
VahidDM
Super User
4 years ago

Hi Anonymous 

 

Try this:

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] )
RETURN
    IF (
        HASONEVALUE ( 'IRR Retina'[P1 Max - Binder Price Plan] ),
        IF (
            HASONEVALUE ( 'IRR Retina'[P2 Max - Binder Price Plan] ),
            SWITCH (
                TRUE (),
                'IRR Retina'[P1 Max - Binder Price Plan] = 'IRR Retina'[P2 Max - Binder Price Plan], "Reatined",
                'IRR Retina'[P1 Max - Binder Price Plan]
                    IN EXP_B
                        && 'IRR Retina'[P2 Max - Binder Price Plan] IN UNION ( EITL, ITL, GTP, GBS ), "Downgrade",
                'IRR Retina'[P1 Max - Binder Price Plan]
                    IN EITL
                        && 'IRR Retina'[P2 Max - Binder Price Plan] IN UNION ( ITL, GTP, GBS ), "Downgrade",
                'IRR Retina'[P1 Max - Binder Price Plan]
                    IN ITL
                        && 'IRR Retina'[P2 Max - Binder Price Plan] IN UNION ( GTP, GBS ), "Downgrade",
                'IRR Retina'[P1 Max - Binder Price Plan]
                    IN EXP_B
                    && 'IRR Retina'[P2 Max - Binder Price Plan] IN RB, "Upgrade",
                'IRR Retina'[P1 Max - Binder Price Plan]
                    IN EITL
                        && 'IRR Retina'[P2 Max - Binder Price Plan] IN UNION ( EXP_B, RB ), "Upgrade",
                'IRR Retina'[P1 Max - Binder Price Plan]
                    IN ITL
                        && 'IRR Retina'[P2 Max - Binder Price Plan] IN UNION ( EITL, RB, EXP_B ), "Upgrade",
                "Cancelled"
            )
        )
    )

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

 

 

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

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.