Column filters with measures that come from expressions

Question

Hi everybody,

I’m facing a problem with column filters in OBIEE 11.1.1.7 . My situation is as follows:

I have an analysis with two measures coming from different fact tables. Then I have two filters -  one of them is used for both measures, but the other filter only applies to one of them. In this situation I guess that I should use the “Edit column formula > Filter” option and create there the filter, so that I would be able to use the filter only for the specified measure. But the issue is that the mentioned measure is a calculated field in the rpd (I mean, calculated using a formula with “derived from existing columns using an expression” option), and according to what I’m seeing, in these cases the columnfilter is not applied to the query, which means that the filter will actually do nothing. I thought that a work around could be applying that expression formula in the analysis instead of in the rpd, but I’m struggling to enter the expression due to the case sentence.

Any idea about this? Am I doing something wrong?

Thanks in advance. Regards,

Hibai.

Hibai_ · Answer

Hello @JustTheFacts,

Thank you very much for your support. It was nice to see your explanations, even though it wasn’t exactly what I was looking for - that’s probably because I didn’t explain my problem properly

Fortunately, I have found a solution for my problem in the following link:

http://www.clearpeaks.com/blog/oracle-bi-ee/combining-measure-with-filter-on-an-unrelated-dimension

And as I have checked, you were totally right when you said that the use of expressions had nothing to do with my concern

So I guess I can consider the issue solved.

Thanks again and regards,

Hibai.

JustTheFacts · Answer

Hello Hibai,

Any filter applied to a measure will be applied after  aggregation to the level-of-detail (LOD) of the analysis. So for example, if I have this analysis:

Customer Name, Year, Revenue, Volume

And I put a filter of "Volume >= 1000" on the request, OBIEE will first calculate Volume by Customer and Year, and then filter out any Customer+Year combinations with <1000 Volume. It should not matter if Revenue or Volume is a logical expression ("derived using existing columns") or a physical expression. OBIEE will calculate the measure at the LOD of the analysis, and then filter it.

The reason I mentioned that is that if you try to filter on a measure, but don't want to do it at the LOD of the analysis, then it can appear as if the filter isn't working (or that it's working incorrectly). So in my example above, let's say that what I wanted to do was filter Individual Orders where the Revenue was >$1000, but still my analysis aggregates by Customer and Year.

Customer Name, Year, Revenue, Volume

FILTER: Revenue > $1000

What I actually will get back is all Customer+Year combinations with total Revenue > $1000. OBIEE will not look at individual orders at all - it will add up total Revenue by Customer+Year, and only then will it apply the filter. And it will look like my filter isn't working at all. (Again, it shouldn't matter If the measure is a logical expression.)

Is that any help? If not, can you provide a bit more detail about what your measures and dimensions are, and what filters you are trying to apply?