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
Anonymous
Not applicable

Passing Parameters in measures

Hi,

Thanks everyone for the comments for my previos posts.

I have one more question which I would like to clarify, Is there a way to parameterize values in Measures.

For eg: I have created a measure like below in which I would like to parametrize the Yearmonth value(highlighted), pls let me know even if we have any workarounds for this.

 

Total Sales = CALCULATE(
SUM('Headcount Data'[May-17]),
FILTER(Year_Month,Year_Month[Year_Month]=201705)
)

 

Regards,

1 ACCEPTED SOLUTION
v-caliao-msft
Employee
Employee

@Anonymous,

 

Currently, we cannot use a parameter in a calculated measure directly. To work around this requirement, we could create a query to store parameter value in a dataset, and then use this dataset value in your calculated measure. Please refer to the sample steps below.

  1. Sample parameter.
    Capture.PNG
  2. Create a blank query and edit the query in Advanced Editor
    Capture1.PNGCapture2.PNG
  3. Create a measure in your original table like below.
    Total Sales = CALCULATE(
    SUM('Headcount Data'[May-17]),
    FILTER(Year_Month,Year_Month[Year_Month]=Max(Query2[ParameterDate]))
    )

Regards,

Chalrie Liao

 

 

View solution in original post

5 REPLIES 5
ara0530
Advocate I
Advocate I

None of these are useful.  In all these solutions, you are 'hardcoding' the value.  How can we allow the user to change the parameter value in the report (i.e. like a slicer)?  

 

smrtboy89
New Member

Hi,

 

Does anyone know is the opposite is possible? Can I store the value of a measure into a parameter?

v-caliao-msft
Employee
Employee

@Anonymous,

 

Currently, we cannot use a parameter in a calculated measure directly. To work around this requirement, we could create a query to store parameter value in a dataset, and then use this dataset value in your calculated measure. Please refer to the sample steps below.

  1. Sample parameter.
    Capture.PNG
  2. Create a blank query and edit the query in Advanced Editor
    Capture1.PNGCapture2.PNG
  3. Create a measure in your original table like below.
    Total Sales = CALCULATE(
    SUM('Headcount Data'[May-17]),
    FILTER(Year_Month,Year_Month[Year_Month]=Max(Query2[ParameterDate]))
    )

Regards,

Chalrie Liao

 

 

I would like to learn more about this approch.

Do you have an videos or other material you could suggest?

Phil_Seamark
Employee
Employee

Hi @Anonymous

 

You could always use another measure for this. 

 

eg create a calculated measure like this.  You could even create a measure table called My Parameters to group them all together.

 

My Param = 201705

and then drop this into your other formula

 

Total Sales = CALCULATE(
SUM('Headcount Data'[May-17]),
FILTER(Year_Month,Year_Month[Year_Month]=[My Param])
)
 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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.