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

## Dynamic Measure Problem By 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,

Based on my test, you could refer to below steps:

1.Sample data:

2.Create a new table and a measure.

New table:

`Copy Table = SUMMARIZE('Table1','Table1'[Channel group],'Table1'[Value])`

New measure:

```Measure = var a=CALCULATE(SUM(Table1[Value]))
var b=CALCULATE(SUM('Copy Table'[Value]))
return DIVIDE(ABS(a-b),MIN(a,b))```

Now you could use the different slicers to show the dynamic calculation.

https://www.dropbox.com/s/jik5izp2fyrxvs9/Dynamic%20Measure%20Problem%20By%20Switch%20Statement.pbix...

Regards,

Daniel He

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

Hi @snandy2011,

Based on my test, you could refer to below steps:

1.Sample data:

2.Create a new table and a measure.

New table:

`Copy Table = SUMMARIZE('Table1','Table1'[Channel group],'Table1'[Value])`

New measure:

```Measure = var a=CALCULATE(SUM(Table1[Value]))
var b=CALCULATE(SUM('Copy Table'[Value]))
return DIVIDE(ABS(a-b),MIN(a,b))```

Now you could use the different slicers to show the dynamic calculation.

https://www.dropbox.com/s/jik5izp2fyrxvs9/Dynamic%20Measure%20Problem%20By%20Switch%20Statement.pbix...

Regards,

Daniel He

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

Thanks a lot for your solution.I have tried your solution and It has worked perfect. I have achieved it by Dax   also. But your soluttion is so simple. got a new thing to learn

Thanks,

snandy2011

Helper IV

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

Helper IV

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