Dynamic What If Paramter

[Jayaselvan]

Hello All,

I have to create a simulation page based on user inputs. I have used 4 What if paramter for that. The value range will be from 0 to 40,000. As What if paramter takes 1000 values only. It incremanets as 40. To avoid that I used dynamic upper range by using measure. The measure will change based on filter selction. When I change the filter it won't dynamically change the upper limit of What if Paramter.

Please let me a way to dynamically allocate upper limit for What If Paramter which needs to change based on filter selection.

What If Paramter:

Qty_City_A = GENERATESERIES(0, [CityAQty], 1]

Measure:

CityAQty = 

var _qty = ROUND(CALCULATE(SUMX(FILTER(CityQty, CityQty[City]="A"), CityQty[Qty])),0)

return

IF(DISTNICTCOUNT(CityQty[BranchType]) > 0, _qty,0)

CityAQty is a single value as I have used SUMX. 

Filters will be branch type. 

Issue 2:

For the first time as well the upper range of the what if paramter is approxiamtely 35 times the actual value. To understand it in detail please look the following lines.

For certain branch selection CityAQty = 1000.

But the What if Paramter Max range is 35,000 approxiametly. 

After I change the other filter the max range doesn't change but the CityAQty value is changing. I have confirmed the change by displaaying thr value in Card.

[amitchandak]

@Jayaselvan ,You can not use slicer value in calculated table or column . 

Try like

Qty_City_A = GENERATESERIES(0, Max(CityQty[Qty])  , 1 )

[Jayaselvan]

Thanks for the point out. Actually I have used the single value measure only. There is typo in measure formula, I have rectified the same in query as well.

Measure:

CityAQty = 

var _qty = ROUND(CALCULATE(SUMX(FILTER(CityQty, CityQty[City]="A"), CityQty[Qty])),0)

return

IF(DISTNICTCOUNT(CityQty[BranchType]) > 0, _qty,0)

CityAQty is a single value as I have used SUMX. 

[amitchandak]

@Jayaselvan , In generateseries if execute at grand total level, no row context unless you force one. Also no slicer filter will be use

example

Measure:

CityAQty =

var _qty = ROUND(CALCULATE(SUMX(FILTER(CityQty, CityQty[City]="A"), CityQty[Qty])),0)

return

maxx(values(CityQty[City]), calculate(  IF(DISTNICTCOUNT(CityQty[BranchType]) > 0, _qty,0) )

[Jayaselvan]

Thanks for your response. 

To clarify, Change in filter doesn't impact the What If Parameter Upper bound right?

I have tried the code you have suggested which I mention below. The output of CityAQty is same if I view in the card with previos code I have used.

The Sum of total Quantity without filter is 250K. That's what taken as upper bound in What If paramter. 

But I need upper bound filter only for City A which is 18K. Is there is any way to achieve this. 

Measure:

CityAQty =

var _qty = ROUND(CALCULATE(SUMX(FILTER(CityQty, CityQty[City]="A"), CityQty[Qty])),0)

return

maxx(values(CityQty[City]), calculate(  IF(DISTNICTCOUNT(CityQty[BranchType]) > 0, _qty,0) )