Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Deepansh_
Regular Visitor

Calculated Column with User_input

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

1 REPLY 1
v-shex-msft
Community Support
Community Support

HI @Deepansh_,

Thank for sharing your insight of this scenario. 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.