cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DeGiacomo72
Frequent Visitor

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 product (retained),  upgraded, downgraded, or blank (cancelled). So I have logic on this becuase muliple products fall under the multiple business units. So i made variables for each column of products (different table) and labeled the variables as the business unit name ( a column/variable for every business unit). So when my switch statement  would go and look for that variable and in that underlying column to see if its an upgrade, downgrade, ect. I then got "multiple values when one was expected error" and added 2 IF and HASONEVALUE formulas but now my output is blank, any help? 

 

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] = EXP_B && 'IRR Retina'[P2 Max - Binder Price Plan] = EITL, "Downgrade",
'IRR Retina'[P1 Max - Binder Price Plan] = EXP_B && 'IRR Retina'[P2 Max - Binder Price Plan] = ITL, "Downgrade",
'IRR Retina'[P1 Max - Binder Price Plan] = EXP_B && 'IRR Retina'[P2 Max - Binder Price Plan] = GTP, "Downgrade",
'IRR Retina'[P1 Max - Binder Price Plan] = EXP_B && 'IRR Retina'[P2 Max - Binder Price Plan] = GBS, "Downgrade",
'IRR Retina'[P1 Max - Binder Price Plan] = EITL && 'IRR Retina'[P2 Max - Binder Price Plan] = ITL, "Downgrade",
'IRR Retina'[P1 Max - Binder Price Plan] = EITL && 'IRR Retina'[P2 Max - Binder Price Plan] = GTP, "Downgrade",
'IRR Retina'[P1 Max - Binder Price Plan] = EITL && 'IRR Retina'[P2 Max - Binder Price Plan] = GBS, "Downgrade",
'IRR Retina'[P1 Max - Binder Price Plan] = ITL && 'IRR Retina'[P2 Max - Binder Price Plan] = GTP, "Downgrade",
'IRR Retina'[P1 Max - Binder Price Plan] = ITL && 'IRR Retina'[P2 Max - Binder Price Plan] = GBS, "Downgrade",
'IRR Retina'[P1 Max - Binder Price Plan] = EXP_B && 'IRR Retina'[P2 Max - Binder Price Plan] = RB, "Upgrade",
'IRR Retina'[P1 Max - Binder Price Plan] = EITL && 'IRR Retina'[P2 Max - Binder Price Plan] = EXP_B, "Upgrade",
'IRR Retina'[P1 Max - Binder Price Plan] = EITL && 'IRR Retina'[P2 Max - Binder Price Plan] = RB, "Upgrade",
'IRR Retina'[P1 Max - Binder Price Plan] = ITL && 'IRR Retina'[P2 Max - Binder Price Plan] = EITL, "Upgrade",
'IRR Retina'[P1 Max - Binder Price Plan] = ITL && 'IRR Retina'[P2 Max - Binder Price Plan] = EXP_B, "Upgrade",
'IRR Retina'[P1 Max - Binder Price Plan] = ITL && 'IRR Retina'[P2 Max - Binder Price Plan] = RB, "Upgrade",
"Cancelled")))
1 ACCEPTED SOLUTION

@DeGiacomo72 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.

View solution in original post

9 REPLIES 9
v-robertq-msft
Community Support
Community Support

Hi, 

Have you followed the DAX formula posted by AlexisOlson to find the solution to your problem?

If so, would you like to mark his reply as a solution so that others can learn from it too?

 

For more info about the DAX function SelectedValue() in the Power BI, please refer to this link:

https://docs.microsoft.com/en-us/dax/selectedvalue-function

 

Thanks in advance!

How to Get Your Question Answered Quickly 

 

Best Regards,

Community Support Team _Robert Qin

VahidDM
Super User
Super User

Hi @DeGiacomo72 

 

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/

 

 

@DeGiacomo72 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.

AlexisOlson
Super User
Super User

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.

DeGiacomo72
Frequent Visitor

@amitchandak any ideas?

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!