Using selected value of what if parameter in a Calculated column

[cyclist007]

I am trying to get a number input from the end user using the What if Paramater.

I would then like to use that number in a calculated column and show the calculated column as a category on a column chart. The calculated column just shows default what if paramter value, it does pick the selected value. See examples below:

Paramter table:

Parameter = GENERATESERIES(3, 60, 1)

Parameter Value = SELECTEDVALUE('Parameter'[Parameter], 12)

Calculated Column Formula: 

Expiration Status = IF(Table[ExpirationDate]=BLANK(),"NA",IF(DATEDIFF(TODAY(),Table[ExpirationDate],DAY)<0,"Expired",IF(DATEDIFF(TODAY(),Table[ExpirationDate],DAY)<'Parameter'[Parameter Value],"Expiring in <"&'Parameter'[Parameter Value] &" days",IF(DATEDIFF(TODAY(),Table[ExpirationDate],DAY)<60,"Expiring in <60 days","Expiring in >=60 days"))))

Current output:

Expected Output:

The second blue column should have been "Expiring in <42 days"  (not "Expiring in 12 days") and also the $ value should have been recalculated accordingly.

If this method is not the right way to do it, would appreciate if you can provide an alternative approach to achieve this. Ideally I would also want to have a 2nd parameter for the "60" value (3rd & 4th category/columns).

Also are there any limitations in doing this on the Power BI report server?

[A_Murray]

Also getting the same issue. I want to have a calculated column with an number (a) times a variable (y) (between 100% & 150% in increments of 5%) which is created via parameter. The column will not recalculate. To make it a measure how could I use the raw table data without average(a)or sum(a)? my formaula has ifs such that 

if(or(isblank("a), "a" = 0), 0, if( "a" > "b" * ("y" - the value from the parameter), 1, 0))

for "y" i have tried replacing it with selectedvalue(parameter[parameter]) both directly and by creating another column.

I guess the question is how do I get "a" into a measure? max("a") doen't work. is there another keyword?

[MonsieurLL]

Similar issue here:

1. I want the user to be able to select a date. I used a measure with SELECTEDVALUE to get this date.
   
   FXDate = SELECTEDVALUE(FX[Date])
2. The date is associated with a number (FX rate). I created another measure with CALCULATE and FILTER to get the associated
   
   FXRate = CALCULATE(MAX(FX[USD/CAD]), FILTER(FX, FX[Date] = FX[FXDate]) )
3. I have another table with numbers. I created a column in this table which multiplies each "Amount USD" by the FXRate measure.
   
   ValueTodayCAD = Transactions[Amount USD] * FX[FXRate]
4.  When the user selects a different date, the measures all update correctly, but the new column does not recalculate using the new measure value for FXRate.

[MonsieurLL]

I found the answer: a calculated column will NOT update dynamically. Instead, we must use a measure.

[v-piga-msft]

Hi @cyclist007 ,

Your issue is caused by that you created the calculated column with parameter value which is not dynamic. If you create the measure with parameter, it will work fine. 

However, we can't use measures as an axis at the moment, just columns are supported for the use on axis. 

So, I don't think that we could achieve your desired X axis in Power BI Currently.

Best  Regards,

Cherry

[cyclist007]

Thanks @v-piga-msft for responding. 'Paramater Value' is a measure (or else what is it?) And I was using the Measure in a Calculated column - now I understand the calculated column would not get recalculate on change in measure/paramter value but was hoping at least clicking on refresh data would help? Is there anyway the calculated column can be forced to recalculate (by the end user)?

[Anonymous]

I am having the same issue. I can not create measure and it has to be a calculated column. Is there a way of having the value I decide in one of the columns so that I can use it in the calculated column?

Or createing measure is only the way?

Thanks

Nagaraj

[cyclist007]

Anyone with any thoughts/answers?