cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

## How to write multiple AND statement into switch statement

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)

Any suggesation is really appreciable.

Thanks,

snandy2011

1 ACCEPTED SOLUTION
Microsoft Employee

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Microsoft Employee

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper IV

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.