Calculated Column with User_input

[Deepansh_]

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 –

1. Create 2 calculated columns –
   1. Quote rate for sap = “USD”
   2. Quote rate for treasury = “LC”
2. Create a filed parameter for Quote rate include the above 2 fields
   1. 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.
   2. 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

1. 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

1. Add slicer for both view type and base currency , hide the base currency slicer

1. 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

[Anonymous]

HI @Deepansh_,

Thank for sharing your insight of this scenario. 

Regards,

Xiaoxin Sheng