10 Replies Latest reply on Jan 10, 2019 12:51 PM by ForSly

# How to use filter function with group by

I am trying to create a metric using filter function with a  group by. I am not sure whether the filter function even works with group by in OBIEE answers.   I need something like this ( Filter (#ex.XYZ Fatct group by a,b,c). my expected results is a count of  all cases of yyFatct  grouped by a,b,c

• ###### 1. Re: How to use filter function with group by

Hi,

Sorry but not sure to understand what you are after as your example isn't really OBIEE: you talk about the FILTER(.... USING ....) function?

Or you mean "filter" as a WHERE clause in a query?

• ###### 2. Re: How to use filter function with group by

Is this what you are looking for ->

• ###### 3. Re: How to use filter function with group by

yes I am talking about FILTER(.... USING ....) function? can we use group by with this function? if so do you have an example?

• ###### 4. Re: How to use filter function with group by

No, I am looking for something like this FILTER(.... USING ....)

• ###### 5. Re: How to use filter function with group by

You're not providing much usable context, so here's gerard's wiki on FILTER:

https://gerardnico.com/dat/obiee/obips/filter_function

And the official documentation:

https://docs.oracle.com/cd/E23943_01/bi.1111/e10544/appuir.htm#BIEUG847

• ###### 6. Re: How to use filter function with group by

So you want to filter a measure by some values and in the same time group the result at a different level than the one of the analysis itself? Did you try it?

Can't the same be achieved with normal analysis filtering? (sometime FILTER USING is abused for no real reason)

• ###### 7. Re: How to use filter function with group by

I finally figured it out.  using this (sum(count (distinct "Fact - XXY"."# Applicants" by "XXY"."Job ")) )

• ###### 8. Re: How to use filter function with group by

What does that have to do with filtering?!

• ###### 9. Re: How to use filter function with group by

Sum if versus filter by aside; if you have access to the rpd then you can duplicate your existing count of applicants measure, call it Count of Applicants by Job, pin (copy and paste) it to the job level (detail probably) of your associated dimension Job, then the number will be invariant for each job and you will have a measure that all of your users can use with simple logic, rather than a complex once over piece of logic that only you understand...