How to write multiple AND statement into switch statement

[snandy2011]

Hi all,

I just want to do dynamic calculation of one of my measure. Let me please explain,

I have a slicer of 4 different Channel group, like, Direct,Social, referral and Organic.

When I will click  suppose direct and social, then it will show Percentage of leading of those two selected channel group.

After, when iclick Organic and referral, it will show percentage of leading between organic and referral.

I have calculated on prcentage of leading on other's measure.like

Direct vs  Social = DIVIDE(  ([Current Month Session For Direct TC] - [Current Month Session For Social TC]), [Current Month Session For Social TC] )

Similar I have Organic vs Referral and Others.

for making this dynamic i have written following formula,

Measure Selection for TC = IF(ISCROSSFILTERED( 'Table For Comparison'[Default Channel Grouping]),

SWITCH( TRUE(),

       AND(VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Direct",VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Organic Search"),[Direct vs  Organic],

    AND(VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Direct",VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Referral"),[Direct vs  Referral],

    AND(VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Direct",VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Social"),[Direct vs  Social],

    AND(VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Organic Search",VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Referral"),[Organic vs Referral],

    AND(VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Organic Search",VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Social"),[Organic vs  Social],

    AND(VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Referral",VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Social"),[Referral vs  Social],  BLANK()),BLANK() )

but, it shows me, " A table of multiple values was supplied where a single value was expected"

can i use multiple values statement in switch, if not, then what can be the others solution?

Desired output,

Supppose direct's current month session = 500, organic =200,Social=600, Referral =100

if i select direct and organic both on the slicer it will show (500-200/200), again when i select social and referral it will show, (600-100/100),

And, these calculation will be on one measure (dynamic)

Please help to solve this problem

Any suggesation is really appreciable.

Thanks,

snandy2011

[v-jiascu-msft · Accepted solution]

Hi @snandy2011,

Try a measure like below, please.

Measure Selection for TC =
VAR selectedValues =
    CALCULATE (
        COUNT ( 'Table For Comparison'[Default Channel Grouping] ),
        ALLSELECTED ( 'Table For Comparison'[Default Channel Grouping] )
    )
RETURN
    IF (
        ISCROSSFILTERED ( 'Table For Comparison'[Default Channel Grouping] )
            && selectedValues = 2,
        SWITCH (
            TRUE (),
            MIN ( 'Table For Comparison'[Default Channel Grouping] )
                IN { "Direct", "Organic Search" }
                && MAX ( 'Table For Comparison'[Default Channel Grouping] )
                    IN { "Direct", "Organic Search" }, [Direct vs  Organic],
            MIN ( 'Table For Comparison'[Default Channel Grouping] )
                IN { "Direct", "Referral" }
                && MAX ( 'Table For Comparison'[Default Channel Grouping] )
                    IN { "Direct", "Referral" }, [Direct vs  Referral],
            MIN ( 'Table For Comparison'[Default Channel Grouping] )
                IN { "Direct", "Social" }
                && MAX ( 'Table For Comparison'[Default Channel Grouping] )
                    IN { "Direct", "Social" }, [Direct vs  Social],
            MIN ( 'Table For Comparison'[Default Channel Grouping] )
                IN { "Organic Search", "Referral" }
                && MAX ( 'Table For Comparison'[Default Channel Grouping] )
                    IN { "Organic Search", "Referral" }, [Organic vs Referral],
            MIN ( 'Table For Comparison'[Default Channel Grouping] )
                IN { "Organic Search", "Social" }
                && MAX ( 'Table For Comparison'[Default Channel Grouping] )
                    IN { "Organic Search", "Social" }, [Organic vs  Social],
            MIN ( 'Table For Comparison'[Default Channel Grouping] )
                IN { "Referral", "Social" }
                && MAX ( 'Table For Comparison'[Default Channel Grouping] )
                    IN { "Referral", "Social" }, [Referral vs  Social],
            888
        ),
        999
    )

Best Regards,

Dale

[snandy2011]

Hi @v-jiascu-msft

i am really sorry for late reply.I am from India actually and I Had holiday on yesterday.

Thanks a lot for your awesome solution.It has worked greatly. Except i did a liitle tweak.I used distinctcount function instead of only count and it worked perfect.

Thanks a lot once again for your reply.learnt new things from that

Thanks,

snandy2011