topic Re: Is it possible to use the selected Field Parameter in a DAX formula? in DAX Commands and Tips

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

charles_g — Mon, 23 May 2022 12:18:38 GMT

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!

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

Whitewater100 — Mon, 23 May 2022 12:51:20 GMT

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.

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

Fowmy — Mon, 23 May 2022 12:55:47 GMT

charles_g

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

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

charles_g — Mon, 23 May 2022 13:31:21 GMT

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.

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

Whitewater100 — Mon, 23 May 2022 14:22:25 GMT

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

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

charles_g — Mon, 30 May 2022 07:07:26 GMT

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:

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

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

jessica-ko — Tue, 28 Jun 2022 12:39:24 GMT

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

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

jbarnard2 — Wed, 16 Aug 2023 19:52:11 GMT

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

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

NMEG — Tue, 03 Oct 2023 04:24:48 GMT

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.

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

charles_g — Tue, 03 Oct 2023 10:38:02 GMT

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.

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

charles_g — Tue, 03 Oct 2023 10:53:29 GMT

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.

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

NMEG — Tue, 03 Oct 2023 22:54:53 GMT

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 🙂

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

Anonymous — Wed, 10 Jan 2024 21:38:06 GMT

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

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

oduc78 — Fri, 12 Jan 2024 13:11:54 GMT

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.

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

mogugu_84 — Thu, 28 Nov 2024 00:48:00 GMT

I feel like my issue is very similar to this one so i followed this approach suggested by another super user in my post,

i have this table showing growth vs PY for different period, and would like to be able to switch between volume and value easily

one of the formulas for calculation for example:

YTD Retail =

CALCULATE ( [Retail Volume], DATESYTD ( 'Calendar'[Date], "03/31" ) )

And i have replicated this SWITCH formula like this:

Retail =

SWITCH(

SELECTEDVALUE('Test Parameter'[Test Parameter Fields]),

"'Def market'[Retail Value]", [Retail Value],

"'Def market'[Retail Volume]", [Retail Volume]

)

Table for my parameter:

after all that, the numbers just won't change using the parameter....anyone know where i got wrong? Thanks

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

charles_g — Fri, 29 Nov 2024 15:52:16 GMT

Hello mogugu_84,

The 'YTD Retail' measure should use the 'Retail' measure instead of 'Retail Volume'.
When you toggle the value of the field parameter 'Def market', the underlying indicator will switch between Retail Value and Retail Volume in the 'YTD Retail' measure.

It's a chain reaction : Field parameter (Def market) > Retail > YTD Retail.

Actually the toggle doesn't have to be a field parameter, it could be a table with one column and 2 values, as long as you can put it in a slicer.

You could also get rid of the intermediary measure 'Retail' and use a variable instead.

YTD Retail = VAR retail = SWITCH( SELECTEDVALUE('Test Parameter'[Test Parameter Fields]), "'Def market'[Retail Value]", [Retail Value], "'Def market'[Retail Volume]", [Retail Volume] ) RETURN CALCULATE ( retail, DATESYTD ( 'Calendar'[Date], "03/31" ) )

I hope this helps.

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

mogugu_84 — Sat, 30 Nov 2024 03:23:31 GMT

Awesome! this works now! Thank you!

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

ranagutgut — Sat, 15 Feb 2025 07:07:02 GMT

I am stuck at a similar problem which I cannot seem to solve even with ChatGPT's help (my experience with ChatGPT and Power BI has not been remotely good 😕 ).

I have added a field parameter which takes 2 columns sales and volume - so that my matrix visual could hopefully switch between these 2 columns based on what I choose as my parameter.

This is what my parameter looks like:

I have added a measure which looks like this:

Selected Net Sales =

VAR chosenMeasure =

SWITCH(

SELECTEDVALUE('Parameter'[Parameter Fields]),

"'Sales'[Revenue]", 'Sales'[Total Revenue],

"'Sales'[Volume]", 'Sales'[Total Volume]

)

VAR SelectedYear =

SELECTEDVALUE('Year'[Year])

RETURN

CALCULATE(

chosenMeasure,

'Sales'[Year Scenario] = SelectedYear & " Procurement BT"

)

But this throws blank value. When I take the sum of the total column as the field parameter - I get the value in the matrix but then I cannot filter anything - which defeats the purpose altogether.

I cannot use the year scenario as another filter - since this is a fixed entry in the matrix.

P.S. - I have been struggling so much with Power BI lately, that I swear I just want to go back to Excel dashboards sometimes or not do anything smart on Power BI - do all transformations outside of it and just do basic visuals on it.

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

andinh — Fri, 21 Feb 2025 16:05:42 GMT

I have a similar issue, I tried to regenerate your code, but when creating measure "Revenue" using switch and Selectedvalue, I got the error message that "The value for Revenue with taxes" can not be determined. Either the column does not exist, or there is no current row for this column.". Please help!

Here is the field parameter

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

VaibhavR2196 — Thu, 13 Mar 2025 18:08:39 GMT

Try something like this:

Selected Net Sales =

VAR SelectedYear =

SELECTEDVALUE('Year'[Year])

RETURN

CALCULATE(

SWITCH(

SELECTEDVALUE('Parameter'[Parameter Fields]),

"'Sales'[Revenue]", 'Sales'[Total Revenue],

"'Sales'[Volume]", 'Sales'[Total Volume]

)

'Sales'[Year Scenario] = SelectedYear & " Procurement BT"

)