Adding filter in a Derived Metric

Hello there!

Today let’s talk about an excellent trick with derived metrics.

There is a way to insert a filter in your derived metric!

Let me show you an example:

1. Create a filter with Year = 2010 and save your filter as “2010“:



2. Create a blank report with Year and the metric Cost:


As you can see, I am fetching all years from database and displaying the Cost for each year.

2. Create a new derived metric:


3. Use a Sum formula, for example, like this:

Sum(Cost) {~ }


4. Insert <[name_of_the_filter]; @x; -> after the dimensionality of the metric. Where @x is the embedding method number:

In my case, I’m going to write: <[2010]; @2; -> 


In x you can use:

1 – Merge into new;

2– Merge report filter into metric;

3– Merge metric condition into report;

When you edit your metric and select Condition there is an Advanced… button where you can see those options.

You can also use + instead of the :

<[2010]; @3; +> – That means that you will “Remove related report filter elements”

<[2010]; @3;> – That means that you will not “Remove related report filter elements”


5. Now, edit your “New metric” and use the Fact Cost instead of the Metric Cost in the formula:

Was: pic2

Now: pic2

6. Save your metric;

7. Re-run your report:


As you can see, we have filtered only 2010 to the “New Metric“! So, you don’t need to create a lot of metrics with different filters to use in just 1 report, you can create a local filtered metric and reduce the amount of metrics created in your project…

Hope it helps!

God bless you!




10 thoughts on “Adding filter in a Derived Metric

  1. Philippe

    Hi, I know this article is a bit older, but nonetheless good 🙂 The only problem I have is that instead of getting what you get under 7., the lines that would return 0 for the filter are simply removed from the report. It’s as if the lines 2011 and 2012 were gone from your report, even though there are values for other metrics.

    I’ve used the following formula: Sum([myAttribute]){~}

    Instead of MSTR Desktop I’m adding the values in MSTR web, though.




      1. Philippe

        Do you have any idea why this could be? Is there some other setting I am missing?

        I tried setting all the options I could do “outer join”, but still nothing, the lines just disappear.


      2. Philippe

        My problem is that I don’t know for sure, but I’m pretty sure it’s a Sybase IQ.


      3. Philippe

        Sadly the base attributes themselves are out of my reach, but maybe I can convince someone to help me out there 😉 But no matter the outcome, thanks a lot for your great help; much appreciated!


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.