Forum Discussion

cyclist007's avatar
cyclist007
Icon for Responsive Resident rankResponsive Resident
7 years ago

Using selected value of what if parameter in a Calculated column

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?
 
Need Help
Show and Tell
Tips and Tricks

7 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    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's avatar
      cyclist007
      Icon for Responsive Resident rankResponsive Resident
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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

  • MonsieurLL's avatar
    MonsieurLL
    Regular Visitor
    3 years ago

    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's avatar
      MonsieurLL
      Regular Visitor
      3 years ago

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

  • A_Murray's avatar
    A_Murray
    New Member
    3 years ago

    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?