Field Parameters in Measures

[nitinrungta]

Is there a way to use the Field Parameters in Measures for different calculations?

[deviln]

Hi @nitinrungta ,

Were you able to proceed further with this?

We need to read the field measure dynamically and its value dynamically, based on the field paramter selected in the slicer. I am also stuck with reading the measure and its value dynamically.

Would like to connect and discuss this further.

TIA

[nitinrungta]

Hi,

You can reach out to me at nitin@purpleme.in for any help.

Someone has posted this idea, would be great if you could vote on this, as it addresses our issue.

Microsoft Idea (powerbi.com)

[PaulDBrown]

Can you please share non-confidential data or a link to a sanitized PBIX file?

[_30Merl]

Hello @jcalheir ,my requirement is similar to @nitinrungta . I need to create a field parameter called 'Month Year'  (Eg: May 2022) and need to use this parameter in my YTD calc. This parameter needs to act as a slicer and every new selection should be reflecting in the YTD. If I select June 2022, then YTD calc will take upto June 2022. By default it should show YTD till current month.

[nitinrungta]

Hi,

You don't need a field parameter for this. Just a clever Measure should take care of this.

[_30Merl]

Okay, so this is the base calc :

Measure_to_get_total = CALCULATE ( SUM ( sales_table[Revenue] ), FILTER(sales_table, YEAR( sales_table[date])=2022 and month <= selected month))

Slicer selected : May 2022  (I need to get sum of sales from Jan 2022 to May 2022)

No slicer selected : Current Month (I need to get sum of sales from Jan 2022 to current month)

How can we achieve this with measure? The highlighted red part is what needs to be dynamic.

I tried by using 'selectedvalue' but it does not seem to be working 

[nitinrungta]

@_30Merl try this calculation, and probably put the year filter in the measure or slicer as you see best fit. The month also you can connect at a slicer level or a MMYYY slicer should also work. 

The thing is you don't need to get too attached to the month being a measure. Let the measure be free-flowing then you can control it anyhow.

Cumulative  = 

CALCULATE (

    [Revenue],

    FILTER (

        ALLSELECTED(  'Date'[Date] ),

        'Date'[Date]<= MAX ( 'Date'[Date] ) )

    )

[nitinrungta]

@_30Merl Yeah, Field Parameters might be an easy option but it can be done through measure and a slicer slider also. I have done similar work before. 

Probably it is very easy or I am not understanding it.

But why don't you use a Total YTD function and A slider of Month Numbers? Since it is the month that you are wanting to keep dynamic. And also keep a slicer (or slider) or a year too.

[jcalheir]

It depends on what you want to do.

Field Parameters create a Custom table, and you can reference the columns from this table in your measures and work with their values.

Can you provide a more specific example you want to achive?

Kind regards,
José
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

[_30Merl]

@jcalheir , Let's consider that I create a field parameter with 2 measures A and B. Now could you please tell me what syntax is apt to reference the measure A in another new measure calculation?

[nitinrungta]

@jcalheir I understand what you are trying to say, but I really don't think that really helps in any way. Thanks, anyway.

I will really consider you a Guru if you can tackle this for me.

I have a field parameter table of three measures:
vs PY
vs PM
vs PW

I also have a separate Color Measure

ColorMeasure PM = 

VAR myEventResult =
[vs PM]

RETURN
SWITCH (
TRUE (),

myEventResult > 0.05, "#73B761",
myEventResult > 0 && myEventResult <= 0.05  , "#f7db03",
myEventResult <= 0, "#CD4C46"
)

Now, this is what I want to achieve. Very short and simple -

When I select vs PY then I want the color measure to change from [vs PM] to [vs PY]

How do I do this? @Greg_Deckler @amitchandak  please could you provide your valuable insight.

[data-wrangler]

Hi,

As per your original questions; "Is there a way to use the Field Parameters in Measures for different calculations?"... I'm not sure if this is totally necessary to achieve the dynamic colour of bars in your graph you've refered to as your actual challenge, based on your selected measure in your field parameter slicer. With that said, I could be interpreting the challenge incorrectly.

You may be able to refer to the measure itself?

Try this calculation:

ColourMeasure =

VAR _SelectedValue = // This Variable is a workaround for using selectedvalue() with the new field parameters functionality

CONCATENATEX (

    SELECTCOLUMNS (

        SUMMARIZE ( 'Measure Selector', 'Measure Selector'[Measure], 'Measure Selector'[Parameter Fields]),

        'Measure Selector'[Measure]

    ),

    'Measure Selector'[Measure], ", " )

VAR _ColorPY =

SWITCH (

TRUE (),

[vs PY] > 0.05, "#73B761", -- Change to whatever desired value threshold and color

[vs PY] > 0 && [vs PY] <= 0.05  , "#f7db03",

[vs PY] <= 0, "#CD4C46"

)

VAR _ColorPM =

SWITCH (

TRUE (),

[vs PM] > 0.05, "#73B761",

[vs PM] > 0 && [vs PM] <= 0.05  , "#f7db03",

[vs PM] <= 0, "#CD4C46"

)

VAR _ColorPW =

SWITCH (

TRUE (),

[vs PW] > 0.05, "#73B761",

[vs PW] > 0 && [vs PW] <= 0.05  , "#f7db03",

[vs PW] <= 0, "#CD4C46"

)

RETURN

SWITCH(

    TRUE()

    ,_SelectedValue = "vs PY", _ColorPY

    ,_SelectedValue = "vs PM", _ColorPM

    ,_SelectedValue = "vs PW", _ColorPW

)

The calculation may seem verbose, but seeing as you only have 3 measures in your field parameter, its relatively straight forward and gets the job done.

Let me know if this solves your issue?