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.