Case Statement that is not generation a list of values

Question

Hi Need your help, not sure if I have been through his before but can not recall. This is my case statement CASE WHEN "Fact - Country Bookings Non-OU Opportunities"."USD Amount" > 10000000 THEN '1. > $10M' WHEN "Fact - Country Bookings Non-OU Opportunities"."USD Amount" BETWEEN 5000001 AND 10000000 THEN '2. $5M-$10M' WHEN "Fact - Country Bookings Non-OU Opportunities"."USD Amount" BETWEEN 1000001 AND 5000000 THEN '3. $1M-$5M' WHEN "Fact - Country Bookings Non-OU Opportunities"."USD Amount" BETWEEN 500000 AND 1000000 THEN '4. $500K-$1M' WHEN "Fact - Country Bookings Non-OU Opportunities"."USD Amount" BETWEEN 350000 AND 500000 THEN '5. $350-$500K' WHEN "Fact - Country Bookings Non-OU Opportunities"."USD Amount" BETWEEN 200000 AND 350000 THEN '6. $200k-$350k' WHEN "Fact - Country Bookings Non-OU Opportunities"."USD Amount" < 200000 THEN '7. < $200k' ELSE '7. < $200k' END But for some reason, if I try to filter on any of the values, I get this, no drop down with values Which causing also not to see most values when I try to create a prompt with this case statement, any idea? My goal is to create a prompt with this statement and end up with checkboxes where users can check <200k or/and 200k-350k etc Thanks Joe

Joe Choueiri-Oracle · Answer

Hi Christian

Understood, thank you for taking the time to explain.

I will need to talk to my developers because I do not have access to the RPD, I only have edit / answer access at the front end.

Thanks again

Joe

[Deleted User] · Answer

Joe Choueiri-Oracle wrote:So this is a normal behavior when it comes to having a case statement with a measure (with aggregation rule)

Yes. It's a measure. What you want to do i an operation on an attribute.

Joe Choueiri-Oracle wrote:no work around to use it as a prompt and produces values for users to select?

Not "workaround", but "solution": Measures and aggregates are the two core concepts that all dimensional modelling and analytics.

You need dimensional attribute which represents what you want. In the RPD, in a dimension  - even if "only" a degenerate dimension. Not in the front-end.

Joe Choueiri-Oracle · Answer

Hi Gianni

So this is a normal behavior when it comes to having a case statement with a measure (with aggregation rule), no work around to use it as a prompt and produces values for users to select?