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

[charles_g]

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!

[mogugu_84]

Hi 

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

[charles_g]

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.

[mogugu_84]

Awesome! this works now! Thank you!

[oduc78]

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]

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 · Accepted solution]

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

[NMEG]

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.

[charles_g]

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.

[NMEG]

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 🙂

[jbarnard2]

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

[charles_g]

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.

[jessica-ko]

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

[charles_g]

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

[Whitewater100]

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

[Fowmy]

@charles_g 

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

[Whitewater100]

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.