Skip to main content
cancel
Showing results for 
Search instead 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

Reply
snandy2011
Helper IV
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)

 

Please help to solve this problem

 

Any suggesation is really appreciable.

 

Thanks,

snandy2011 

1 ACCEPTED SOLUTION
v-danhe-msft
Microsoft Employee
Microsoft Employee

Hi @snandy2011,

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

1.Sample data:

1.PNG

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.

2.PNG

You can also download the PBIX file to have a view.

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.

View solution in original post

4 REPLIES 4
v-danhe-msft
Microsoft Employee
Microsoft Employee

Hi @snandy2011,

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

1.Sample data:

1.PNG

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.

2.PNG

You can also download the PBIX file to have a view.

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.

Hi @v-danhe-msft

 

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 once again for your awesome reply.

 

Thanks,

snandy2011

snandy2011
Helper IV
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)

 

Please help to solve this problem

 

Any suggesation is really appreciable.

 

Thanks,

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors