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

[Anonymous]

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.

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.

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

[Anonymous · Accepted solution]

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]

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