Selected value of parameter

[Anonymous]

Hi everyone,

I'm struggling to use a parameter value to calculate a new column based on an existing column with year values.

The basic idea is to have the user select a certain year "GrantYear" and the difference between this grant year and 2023 needs to be added to the column. In the example below, you can see there should be 3 years added.

Any help more than welcome!

[Anonymous]

So I have a table with the years between 2023 and 2035 called "YearCal", which should serve as the x-axis of my simulation visuals

However, I want the user to be able to let the simulation start at a later period (and shift the full period), by change the paramater "GrantYear". 

GrantYear = GENERATESERIES(2023, 2040, 1) 

I have a measure "Year Diff" calculated that says 

Year diff = 'GrantYear'[GrantYear Value]-2023 

Than I have a column calculated, "UpdatedCal" in the same table as where my original input column "YearCal" is, which is basically :

UpdatedCal = 'Table'[YearCal]+ [Year diff] 

Then if all goes well, I can use "UpdatedCal" for my x-axis in my visual.

However, I see that the measure "YearDiff" calculates correctly, but when it calculates the column "UpdatedCal", it seems to take the default value of the parameter and not the selected value.

[gmsamborn]

Hi @Anonymous 

It is not a good practice to base a calculated column on a measure.  Calculated columns are set during refresh whereas measures are calculated 'on-demand' when filters (or slicers...) change.  I hope this makes sense.

I would try to avoid that by making [UpdatedCal] a measure instead of a calculated column. 

There are ways to get around the problem of trying to use a measure in an axis.  (You can generate a table that is simply a list of all possible years and make the comparison in the measure instead of via a relationship.)

I would suggest working on the measures at this point and tackle the axis-problem afterwards.

Let me know if you have any questions.

[Anonymous]

Thanks for the reply... I've taken out the measure out of the equation, by determining the calculated column [UpdatedCal] by directly referring to the parameter:

UpdatedCal = 'Table'[YearCal]+GrantYear[GrantYear Value]-2023

However, still not the desired effect. 

[gmsamborn]

It should still take the default value for the parameter since the calculated column is created before any user interaction.

[eliasayyy]

hello @Anonymous can you please explain in more details maybe the emasures your goal and some sample data.