Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Switch between 2 what-if parameter based on slicer and use it in calculating a measure.

Hi all,

I am working on a dashboard wherein I am using a what-if parameter and using it in one of the measures to calculate the annual charge. If I have a single what-if parameter it's working fine. But now I am stuck in this scenario. I have a slicer on Categories column- A and B. I have one what-if parameter for A and one for B.

Whatif-A = GENERATESERIES(200, 10000, 200)
Whatif-A Value = SELECTEDVALUE('Whatif-A'[Whatif-A], 4200)

Whatif-B = GENERATESERIES(1000, 20000, 1000)
Whatif-B Value = SELECTEDVALUE('Whatif-B'[Whatif-B], 11000)

I have a measure that uses the parameter values-
Annual Charge = CALCULATE(SUM(quest[Num]) * 'Whatif-A'[Whatif-A Value] * 0.01) (This is OK if I have just 1 value)

Now, I have a slicer for Categories and I want that if I select A the Whatif-A Value should be used in the measure to calculate Annual Charge and if I select B, the Whatif-B Value should be used.

slider.PNG


I thought of creating a measure of something like this and pass the result of the measure to calculate Annual charge but I am not able to achieve the desired result.

switchM.PNG


Sample data-

DateComNumRC%Categories 03 January 2020 ABC 6.016 14 A
03 January 2020 BCD 6.097 5.5 A
03 January 2020 C 6.54 10 A
03 January 2020 D 7.09 15 A
03 January 2020 E 6.78 30 A
03 January 2020 F 5.905 16 A
03 January 2020 G 7.119 30 A
05 February 2020 C 6.54 10 A
05 February 2020 E 6.78 30 A
05 February 2020 F 5.905 16 A
05 February 2020 ABC 6.016 14 A
05 February 2020 BCD 6.097 5.5 A
05 February 2020 D 7.09 15 A
05 February 2020 G 7.119 20 A
26 February 2020 C 6.54 10 A
02 March 2020 G 7.119 28 A
26 February 2020 G 7.119 28 A
02 March 2020 C 6.54 10 A
02 March 2020 E 6.78 30 A
02 March 2020 BCD 6.097 5.5 A
02 March 2020 ABC 6.016 14 A
02 March 2020 F 5.905 12 A
02 March 2020 D 7.09 15 A
01 April 2020 ABC 6.016 14 A
01 April 2020 C 6.54 10 A
01 April 2020 E 6.78 30 A
01 April 2020 F 5.905 12 A
01 April 2020 BCD 5.391 5.5 A
01 April 2020 D 7.09 15 A
01 April 2020 G 7.119 31 A
06 April 2020 ABC 6.016 14 A
06 April 2020 C 6.54 10 A
09 April 2020 C 6.54 10 A
09 April 2020 E 6.78 30 A
09 April 2020 F 5.905 12 A
09 April 2020 BCD 5.391 5.5 A
09 April 2020 D 7.09 15 A
09 April 2020 G 7.119 20 A
09 April 2020 ABC 6.016 23 A
21 April 2020 C 6.54 10 A
03 May 2020 ABC 5.618 23 B
03 May 2020 BCD 5.391 5.5 B
03 May 2020 C 5.79 10 B
03 May 2020 D 7.09 15 B
03 May 2020 G 7.119 25 B
03 May 2020 E 6.78 30 B
03 May 2020 F 5.905 12 B
03 June 2020 ABC 5.618 23 B
03 June 2020 BCD 5.391 5.5 B
03 June 2020 C 5.79 10 B
03 June 2020 D 6.24 15 B
03 June 2020 G 7.119 25 B
03 June 2020 E 6.78 30 B
03 June 2020 F 5.905 12 B
08 June 2020 ABC 5.618 23 B
08 June 2020 BCD 5.391 5.5 B
08 June 2020 C 5.79 10 B
08 June 2020 D 6.24 15 B
08 June 2020 G 7.119 25 B
08 June 2020 E 6.78 30 B
08 June 2020 F 5.905 12 B
01 July 2020 ABC 5.618 26 B
01 July 2020 BCD 5.391 5.5 B
01 July 2020 C 5.79 10 B
01 July 2020 D 6.24 15 B
01 July 2020 G 7.119 25 B
01 July 2020 E 6.78 30 B
01 July 2020 F 5.905 12 B


It would be great if someone could help me with this.
Many thanks.
Supriya

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi all,

 

I was able to solve the problem by using below measure-

 

SwitchM = IF(COUNTROWS(FILTER(quest,quest[Categories]="A")),CALCULATE(SUM('Whatif-A'[Whatif-A])),IF(COUNTROWS(FILTER(quest,quest[Categories]="B")),CALCULATE(SUM('Whatif-B'[Whatif-B]))))
 
and used this measure to calculate annual charge depending upon the selection.
 
Annual Charge = CALCULATE(SUM(quest[Num]) * [SwitchM ]* 0.01)
 
Regards,
Supriya

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Hi all,

 

I was able to solve the problem by using below measure-

 

SwitchM = IF(COUNTROWS(FILTER(quest,quest[Categories]="A")),CALCULATE(SUM('Whatif-A'[Whatif-A])),IF(COUNTROWS(FILTER(quest,quest[Categories]="B")),CALCULATE(SUM('Whatif-B'[Whatif-B]))))
 
and used this measure to calculate annual charge depending upon the selection.
 
Annual Charge = CALCULATE(SUM(quest[Num]) * [SwitchM ]* 0.01)
 
Regards,
Supriya
Greg_Deckler
Community Champion
Community Champion

@Anonymous - I would check into ISFILTERED or HASONEVALUE. Should be able to use that in a SWITCH(TRUE()...) statement or IF statement.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

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

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.