Forum Discussion
Helper IIWhat-If parameter used in calculated column appears blank
Hi
I’m trying to build an interest rate calculator using some what-if parameters as inputs. I have decided to use DAX to generate my data virtually (in-memory).
I’m having an issue with a calculated column I’ve created that doesn’t seem to be presenting any data.
Issue background:
A: I have created three ‘What If Parameters’:
Input Interest Rate, Input Savings, Input Month. Power BI created a measure group, Dax List, and a Measure for each.
Data Point | Dax List | Measure |
Input Interest Rate | Input Interest Rate = (GENERATESERIES(0.0005, 0.0110, 0.0005)) | Interest Rate Value = SELECTEDVALUE('Input Interest Rate'[Interest Rate List],0.0005)
|
Input Savings | Input Savings = GENERATESERIES(250, 500, 50) | Savings Value = SELECTEDVALUE('Input Savings'[Savings List]) |
Input Month | Input Months = GENERATESERIES(1, 12, 1) | Months Value = SELECTEDVALUE('Input Months'[Months List], 1) |
B: I created a Dax Table/List called “Interest Accrued Breakdown” with a list of month numbers and a calculated column called “Column_Total Contributions” which multiplies the Deposit Periods (MTHs)+1 by the measure created in the what-if measure “Input Savings”.
Column Name | Dax Formula |
Deposit Periods (MTHs) | Interest Accrued Breakdown = GENERATESERIES(0, 12) |
Column_Total Contributions | Column_Total Contributions = ('Interest Accrued Breakdown'[Deposit Periods (MTHs)]+1)*'Input Savings'[Savings Value] |
Issue:
The “Column_Total Contributions” column within the “Interest Accrued Breakdown” table appears blank.
C: When I create a measure with the same formula it evaluates fine.
Measure_Total Contributions = 'Input Months'[Months Value]*'Input Savings'[Savings Value]
Any idea what I’m doing wrong?
Link to PBIx file and a screenshot of Data tab, below.
PBIx: PWRBI_Savings_Interest_Calc.pbix
Hi Josh_BI_UK ,
Everything you have done that is correct in power bi.
You should know that calculated columns are static while measures are dynamic first. In other words, values of calculated columns would not be changed dynamically by the slicer measure.
In this case, the measure [Saving value] is used selectedvalue() and you have not set the default value for it so it would use blank as the default value and all the results of [Contributions] column return blank.
If you set the default value for it, for example, 5:
Savings Value = SELECTEDVALUE('Input Savings'[Savings List],5)Since there is no 'selected' definition in calculated column, it will use the default value 5 to calculate and return the below result:
Hopes it could help you to understand.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
v-yingjlCommunity Support
Hi Josh_BI_UK ,
Everything you have done that is correct in power bi.
You should know that calculated columns are static while measures are dynamic first. In other words, values of calculated columns would not be changed dynamically by the slicer measure.
In this case, the measure [Saving value] is used selectedvalue() and you have not set the default value for it so it would use blank as the default value and all the results of [Contributions] column return blank.
If you set the default value for it, for example, 5:
Savings Value = SELECTEDVALUE('Input Savings'[Savings List],5)Since there is no 'selected' definition in calculated column, it will use the default value 5 to calculate and return the below result:
Hopes it could help you to understand.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.