3 Replies Latest reply on Aug 20, 2018 7:20 PM by meenu choudhary

# Lookup certain values in a dimension without filtering out the whole dimension.

NameItem
APen
ACar
BBike
BDrum
BCar
CWater
CPillow

My crosstab looks like the above table. I want all those Names which bought Car as well as other Items that person bought :

Desired Output -

NameItem
APen
ACar
BBike
BDrum
BCar

I also have a show/hide parameter applied on 'Item' dimension, just fyi.

Any help will be much appreciated.

• ###### 1. Re: Lookup certain values in a dimension without filtering out the whole dimension.

Hello Sargam,

Here it is:

Thanks

Deepak

If it Helps, Pl mark it Helpful and CORRECT to Close Thread

• ###### 2. Re: Lookup certain values in a dimension without filtering out the whole dimension.

Hi Sangam,

{ FIXED [Name]: sum(if [Item]='Car' then 1 else 0 END)}

Drag this to filter and exclude 0

Thanks,

Ritesh

Please mark  the  answer as CORRECT & HELPFUL if it really helped you so that it can help others.

• ###### 3. Re: Lookup certain values in a dimension without filtering out the whole dimension.

HI Sargam,

You can try below logic:

i have divide the cal in 3 variables for better understanding.

1.  If the name has car or not.

car? = { FIXED [Name]:sum(if[Item]='Car' then 1 else 0 END)}

2.  Number of Item associated with each name.

count : { FIXED [Name]:COUNTD([Item])}

3.  Flag Name, where Car is there in the item list along with other values.

Flag: if [car?]=1 and [count]>1 then 'True' else 'False' END