Power bi Solution-
Calculated Columns with Dynamic/User-input Selections-
Note – this solution might not applicable to all places where a dynamic/user-input is needed.
Problem Statement—It is a common knowledge that we create Calculated measures and user-input can be used in it .
However if you want to create a calculated column and change the values of that column wrt to user- input or filter selection by users, Then column can’t work like this.
In short a calculated column can’t work on selected values().
Comment- Some might say that what is the need you can create this is calculated measure as well , but keep in mind that measure will only come in the values and if you put that in columns the whole view of the report will change.
Solution-
A work around can be applied to this situation.
Example Scenario –
User selection on View type – Sap View/ Treasury view
Calculated Column = if ( selected value(View type) = “Sap View” , “USD”, else if (selected value = treasure view , “local currency”)
In above scenario is it not possible to create a column.
Follow the below steps –
- Create 2 calculated columns –
- Quote rate for sap = “USD”
- Quote rate for treasury = “LC”
- Create a filed parameter for Quote rate include the above 2 fields
- Change the values of Quote rate column in all row put “Quote Rate” – this will act as the name of column in your matrix , as in field parameter the name of the column change once you change the value , so we are making all the column names as same.
- Add one more column in the field parameter table – value in this column must be same as the selection you want .
Meaning View type has 2 selection values Sap view and treasury view and with selection of sap view you need value of quote rate for sap in your field parameter and so on ,
Provide these selection values in the newly added column of field parameter – Sap View/ Treasury view
- Now you have a field parameter which can act as a field and this will change its value with change in the selection
Only Challenge remains is how to connect this fields parameter with your original selection for View type
- Add slicer for both view type and base currency , hide the base currency slicer
- As you know we have added an extra column in field parameter use this extra column to join with your previous selection of view type and join bi-directional, if already not , make view type as a separate table and create Slicer on it .
You functionality is achieved.
Download the PBIX from here
