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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
charles_g
Advocate I
Advocate I

Is it possible to use the selected Field Parameter in a DAX formula?

Hi,

I'm using the new Field Parameters feature shipped in the latest May 22 version of Power B, and I need to use the selected measure in other calculations. 

 

My (simplified) use case is as follows:

I have different calculations for Revenue : inc. taxes, excl. taxes...

I let the user select one of them with a slicer based on a Field Parameter.

The report is updated with the selected measure wherever Revenue is displayed.

But I would also like to update all the measures that are based on Revenue like Profit for example.

 

I've tried using SELECTEDVALUE() to grab the measure, but this only returns the name of the measure as a string.

SELECTEDMEASURE doesn't work either, it returns blank.

 

Thanks for any help!

1 ACCEPTED SOLUTION
charles_g
Advocate I
Advocate I

Here's the best solution I found based on your suggestions which is pretty close to what I wanted to achieve. I created a new measure whose value is conditional on the selected field parameter. I can then calculate other measures based on this value.

It is  important to make sure that a field parameter is in the filter context anytime a measure based on Revenue is used, otherwise it returns no value.

 

Revenue =
SWITCH(
SELECTEDVALUE(Revenue[Revenue Fields]),
"'Key mesaures'[Revenue with taxes]", [Revenue with taxes],
"'Key measures'[Revenue without taxes]", [Revenue without taxes]

 

My field parameter table looks like this:

RevenueRevenue FieldsRevenue Order
Revenue with taxes
'Key measures'[Revenue with taxes]0
Revenue without taxes'Key measures'[Revenue without taxes]1

 

View solution in original post

13 REPLIES 13
oduc78
Regular Visitor

Hello, I've got a similar issue but I haven't been able to find a solution. I created a new post: SELECTEDVALUE doesn't work to add a dynamic header to Field Parameters. Maybe comparing similar problems can help us solve them, let me know.

Olivier.

MaxingerZ
New Member

Has anyone tried this using a date filter in the measure? This method of using field parameters worked for me in a couple of measures, but I wanted to use it with a date filter to see information from last year, and it does not matter what I do; the date filter within the measure does not seem to work. Does anyone have an answer for this 

charles_g
Advocate I
Advocate I

Here's the best solution I found based on your suggestions which is pretty close to what I wanted to achieve. I created a new measure whose value is conditional on the selected field parameter. I can then calculate other measures based on this value.

It is  important to make sure that a field parameter is in the filter context anytime a measure based on Revenue is used, otherwise it returns no value.

 

Revenue =
SWITCH(
SELECTEDVALUE(Revenue[Revenue Fields]),
"'Key mesaures'[Revenue with taxes]", [Revenue with taxes],
"'Key measures'[Revenue without taxes]", [Revenue without taxes]

 

My field parameter table looks like this:

RevenueRevenue FieldsRevenue Order
Revenue with taxes
'Key measures'[Revenue with taxes]0
Revenue without taxes'Key measures'[Revenue without taxes]1

 

Hi Charles

I am trying to achieve the same thing you describe so I have created a new measure exactly as you have detailed but I get an error message that says "Function SWITCH does not support comparing values of type Text with vallues of type number.  Whenever I try to convert the data type from text to whole number I get another error. Any ideas? Thank you.

Hi @NMEG

Make sure the first argument in the SWITCH function refers to the "Revenue Fields" column in the Field Parameter table which is a string.
The SWITCH function is comparing the selected "Revenue Fields" string, with each of the strings that are in the 2nd, 4th... parameters.

If you show me your measure and you field parameter table it would be easier to help you.

Thank you so much charles_g. After reading your response and knowing that the fields should be text and staring at your solution for what seemed like hours, it finally clicked and I realised I was missing the "" which is what would make my field text. The solution has worked perfectly thank you 🙂

Where should you create this measure (like in which table)? Everywhere I've tried to make it, it doesn't recognize the columns in the 3rd and 5th fields of the SWITCH function ([Revenue with taxes], [Revenue without taxes] in your example).

 

And then, does this allow a user to change what calculation is being used in the view? I have a very similar goal where I want the user to be able to change the denominator of a fraction in a caluclated measure between two columns

I'm not sure I understand your question.
The SWITCH measure can be anywhere. [Revenue with taxes], [Revenue without taxes] are the target measures, they are not columns.
In the 2nd, and 4th field of the SWITCH measure you need to refer to them with their table and column names, same as in the Field Parameter table. Maybe that's why in can be a little confusing.
In my case, I put my measures in a 'Key measures' table. Also pay attention to nested quotes if your table name has spaces.

Thank you sooo much for this solution!! very helpful!!!

charles_g
Advocate I
Advocate I

@Whitewater100  @Fowmy 

 

Thank you for your replies,

I'm already using dynamic measures and calculation groups, but since Field Parameters allow to select a measure, it would be great if we could grab that selected measure in DAX to reuse it.

Definitely. I hope I answered your question satisfactorily! Thanks..

Fowmy
Super User
Super User

@charles_g 

I would suggest you to look at Calculation Groups and Dynamic measures in TABULAR EDITOR 


Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Whitewater100
Solution Sage
Solution Sage

Hi:

You can use multiple measures in field parameters. You alos can combine measures and fields in same table. Here is an example of multiple measures. One other thing is you can put a slicer like Year to chane values of your field parameter results. The image below is one example. I've been unable to use the table created in any simple table function like COUNTROWS. You could look at calculation groups as an alternative.

 

 

 

 

Whitewater100_0-1653309831607.pngWhitewater100_1-1653309884048.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors