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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nitinrungta
Frequent Visitor

Field Parameters in Measures

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

13 REPLIES 13
C4YNelis
Advocate II
Advocate II

Hi @nitinrungta , @deviln ,

 

did either of you ever manage to actually solve this issue?

 

I think in almost all cases you can solve this issue in different ways too (for instance by using calculationgroups), but there is actually one specific scenario where you really cannot do without (afaik).

 

I need to "interpret" the Field Parameter (rather than retreiving its name), because I'm using this to model OLS into the report, which actually deletes (hides) the normal measures from the report, breaking visuals. Refering to an actual measure, will only result in deleting the referring measure too. However, I've come to the point where I believe this simply is not possible.

 

I've seen the information from Alberto Ferrari, where he's describing the technical implementation of Field Parameters (basically leveraging extended properties and grouped columns). The takeaway from this is that Field Parameters seem to depend on the client application to handle this, effectively making it impossible to retreive the actual value in another measure.

 

I wanted to vote for this suggestion, but that link seems to fail?

 

Cheers

deviln
Frequent Visitor

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

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
Community Champion
Community Champion

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Anonymous
Not applicable

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.

Hi,

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

Anonymous
Not applicable

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 

@Anonymous 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] ) )

    )

@Anonymous 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
Solution Supplier
Solution Supplier

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! 🙂

Anonymous
Not applicable

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

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

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?






Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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