NoFilter() function for one specific dimension

[Former Member]

Hi,

In my report I have 3 filters defined in the filter bar (Region, Product, Year). I know that I can use the NoFilter-function for a measure to ignore all those filters. But can I also specify one specific dimension to be ignored. Let's say I want that for my measure the filter Region should be ignored, but not the Product or Year filter. If not, is there any workaround (One would be to insert a second query and don't merge on the Region-dimension I think)?

Thanks!

[former_member201488 · Accepted solution]

You can achieve this with careful use of context operators.

Here's a quick example - a simple E-Fashion query:

...which produces these results:

I've added a column containing the formula at the bottom, and two filters in the filter bar for STATE and YEAR.

If we change the filter for STATE then the Sales Revenue and Formula columns are filtered accordingly:

...but if we then use the Year Filter, only the Sales Revenue is affected:

So, it should be possible using Context Operators; how you apply to your own requirement will be up to you to determine.

HTH

NMG

[Former Member · Accepted solution]

Thanks, looks fine on the screenshots, but I don't understand the Formula. Why you include both dimensions State and Year in the ForEach, somewhere you have to distinguish between dimensions where the filter should be applied and where it should be ignored...

[former_member201488 · Accepted solution]

Apologies, it was just a quick proof of concept showing that you can accomplish what you need via Context Operators - so you don't need multiple queries, etc. You don't need both Dimensions in the Foreach modifier, [Year] alone will work  - the point was, using contexts is one way you can accomplish your request - and how you apply it to your data is up to you. Experiment with your measure contexts until you find the solution.

HTH

NMG

[Former Member · Accepted solution]

Hm still strange, that the second filter is ignored as well.

=Sum(NoFilter([Count ID];All) ForEach([Region]))

When I use the Year filter now, it doesn't work either. Is the formula correct?

[former_member201488 · Accepted solution]

=Sum(NoFilter([Count ID];All) ForEach([Region]))

Check your brackets:

=Sum(NoFilter([Count ID];All)) ForEach([Region])

HTH

NMG

[Former Member · Accepted solution]

I tried all variations with the brackets, but it never works.

=Sum(NoFilter([Count ID];All)) ForEach([Region]) --> second filter is also ignored

=Sum(NoFilter([Count ID];All) ForEach([Region])) --> no filter is ignored

=NoFilter([Count ID];All) ForEach([Region]) --> no filter is ignored

The query is acually very simple.

I am using BI 4.1 SP1 btw.

[former_member201488 · Accepted solution]

Sorry Dude. I'm on 3.1SP5, but I don't see how it would make much difference. I don't know your report so can't really comment.

Have you tried replicating my E-Fashion query, and does that work for you?

(does E-Fashion even ship with 4.x?)

NMG

[Former Member · Accepted solution]

I just saw that also in your example it doesn't show the expected result. The value of the forrmula column is the same in all screenshots. So the second filter is not applied to the formula-column. It's clearer when using objects not from a hierarchy.

The requirement is: The Years filter should not be applied to the Formula, but the Category filter should:

Now filtering for Bermudas:

The Category filter is not applied.

[former_member201488 · Accepted solution]

OK, let's try again and follow your request: Apply the [Category] when filtering but not the [Year]

New formula:

=If(IsError(NoFilter(Sum([Sales revenue]);All) In ([Category];[Store name]));NoFilter([Sales revenue];All);NoFilter(Sum([Sales revenue]);All) In ([Category];[Store name]))

(The formula is specifying the calculation context but this presents a #MULTIVALUE error when the Category is not filtered - hence we check for this error and return the Sales Revenue instead - but there's nothing to stop you substituting another context here if desired).

Results:

Apply Filter to category:

...and apply filter to Year:

...as you can see, the Formula column is changing when the [Category] is filtered, but not when [Year] is filtered.

HTH

NMG

[Former Member · Accepted solution]

I didnt try it but i trust you that it works. I willkeep the solution with 2 queries for now, but good to know that there is a way with context as well. Thanks!

[Former Member · Accepted solution]

Hi Neil,

Thanks for your solutions, it works fine with filter and input control when we select single value in filter.

When I am selecting multiple value using input control, i.e Year inlist("2004";"2005") then getting  #MULTIVALUE error.

Is there any way to avoid  #MULTIVALUE and get correct result?

Thanks in Advance

Kumar

[former_member225163]

Just a thought.. might be helpful..

> First apply NoFilter() function on measure, then it ignores all filter conditions,

> then you use a where condition to restrict to particular Year or Product as follows:

e.g., NoFilter(<measure>) where (Year=2013 and Product="abc")

Thanks,

Bala

[Former Member]

Thanks, but then the other 2 filters couldn't used anymore

[Former Member]

Hi Hans,

what are the other 2 filters which you would like to use..??

If those filters are Year and product you can try to use them in formula using the where clause in the formula as Bala mentioned earlier..

[Former Member]

I don't understand that approach, how do you mean to use it in the where clause? The user should be able to set the filter in the filter bar flexible.

[Former Member]

Something like below for the measure..

=NoFilter([Measure]) where ([Year]=2013 and [Product]="Product1")

[Former Member]

Okay, but then if the user choses Year=2014 in the filter bar, it will show no result.

[sateesh_kumar1]

Hi Hans ,

Yes , bringing the objects from the second query is the only way.

Make sure that Auto merge not happened.

[arijit_das]

It is not possible to control the scope of NoFilter function in webi. It will always ignore all filters applied to that block.