Applying filter in dimension column formula

Question

Hello,

I am trying to achieve following logic from two tables in a single subject area. The goal is to have column restrict data using filter in a column formula involving 2 tables from the same subject area. Obviously, I tried using below syntax but in vain as expected throwing syntax error. Any other approach is very much appreciated.

case when "table1"."column1"='A' then "table2"."column2" LIKE '%~ABC' else "table2"."column2" end

Thanks.

OBIEE_Drool · Answer

Thanks @Gianni Ceresa. I think it works. Let me test in higher environments with better data. I will close this as answered then if it works.

Gianni Ceresa · Answer

Ok, I guess I see what you try to do and you try to do it in the wrong place .... You can't filter your data in a column formula, because the column formula isn't a filter and you have a real filter area in the criteria tab of your analysis. So first of all you must add this as a filter if you want it to work. What I understand is: - if column1 = A then you want to see only data of column2 like %~ABC - if column1 <> A then you don't really care about the value of column2 and so you want all the data right? If yes then add the 3 conditions to your analysis : (column1 = A AND column2 like '%~ABC) OR column1 <> A If it isn't right just tell use what must happen when column1 isn't A ....

OBIEE_Drool · Answer

Dan - When I use your logic, I get an error because I don't have any measure columns in discussion: Function FILTER requires at least one measure column in its first argument. (HY000). Both the tables are dimension tables in same subject area. So, here's what I am trying to achieve: When "table1"."column1"='A' then I should be able to filter data in Column 2 that ends with '~ABC', hence I do not want to use = operator. Instead, I am using LIKE '%~ABC'. I am not sure if OBIEE understands an operator in THEN clause like the way I tried initially @Gianni Ceresa, my explanation below per what you said: CASE WHEN THEN ELSE END case when "table1"."column1"='A' then "table2"."column2" LIKE '%~ABC' else "table2"."column2" end Please advise.