cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
akj2784 Member
Member

Create variable to user in Metrics/Reports data

Hi Team,

 

I have a requirement to use a variable to filter the metrics data, or sometimes put filter at the report level too.

e.g If I have a revenue metric, I want to see the revenue untill the data fetched by the variable, And the variable gets the data from the oracle table which keeps changing.

 

How do I store the date in the variable so that I can use it in the metric. When I create a parameter, and select Query, the box is disabled.

Am I doing something wrong here ?

PowerBI.JPG

 

 

What is the other way of doing it.

 

Regards,

Akash

8 REPLIES 8
Moderator v-yuezhe-msft
Moderator

Re: Create variable to user in Metrics/Reports data

@akj2784,

Please take a look at the following similar thread, I provide detailed steps to populate the parameter list based on a query.

http://community.powerbi.com/t5/Desktop/Query-for-parameter-suggested-values-global-variable-for-ent...

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
akj2784 Member
Member

Re: Create variable to user in Metrics/Reports data

Thank you. I am able to create a new parameter. But how do I use it in the filter.

e.g I want to put Project Date < Parameter Value. When I put this filter, basically my revenue should show only until the cut off date of Parameter value.

 And I want to apply it to all the reports and metrics. Sometimes to a specific metric in the report but not at report level.

Meaning, I can have a metric to show the data untill the Parameter Value and another metric independant of Parameter value.

 

Regards,

Akash

 

Moderator v-yuezhe-msft
Moderator

Re: Create variable to user in Metrics/Reports data

@akj2784,

After you set a date value for the parameter, go to Date fields of your tables, then filter the Date fields to be "is before or equal to" parameter, there is an example for your reference.

3.JPG1.JPG2.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
akj2784 Member
Member

Re: Create variable to user in Metrics/Reports data

If I do that at table level, it will always restrict the data untill the parameter value which I don't want. At some place I don't want this filter to be applicable. 

akj2784 Member
Member

Re: Create variable to user in Metrics/Reports data

To give you a context. I am using W_DAY_D joined to Revenue Fact and Forecast Fact. 

Revenue is always untill the parameter date. However Forecast is always for future date. So if I restrict it to parameter value at W_DAY_D table level, I may not be able to see forecast data.

Moderator v-yuezhe-msft
Moderator

Re: Create variable to user in Metrics/Reports data

@akj2784,

In the following screenshot, you can choose different filters(equal to, is before or equal to) for different tables.
1.JPG

If you still have questions, please share sample data of your tables and post expected result. 


Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
akj2784 Member
Member

Re: Create variable to user in Metrics/Reports data

Let me try to explain again.

W_DAY_D is a common day dimension which is joined to Revenue and forecast Fact on some FKs.

Revenue and Forecast fact will not have the date column. 

I want to apply this parameter filter only to Revenue Fact joined to w_day_d and NOT to Forecast joined with w_day_d.

 

With your method, I can apply the parameter filter at table level which may not help in my case.

Moderator v-yuezhe-msft
Moderator

Re: Create variable to user in Metrics/Reports data

@akj2784,

In your scenario, the parameter method may not be suitable for your scenario, you may need to create different measures to return the expected result.

If you have any questions about creating measures, please share sample data of your tables and post desired output so that we can provide you appropriate suggestions.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 35 members 970 guests
Please welcome our newest community members: