8 Replies Latest reply on Jan 11, 2017 1:14 AM by Norbert Maijoor

# How to apply a measure filter without changing dimensions

Hi all,

I have the following situation. For the dimension Properties, I want to show customer satisfaction levels regarding the local hypermarket (measure Hyper Satisfaction). The thing is, there is one property which has 2 hypermarkets, and thus the answers are recorded in 2 separate measures (ICA & COOP measures). How can I remove all empty columns from the picture below? Filtering on the non-null values of the measures will remove properties from the view.

Thank you,

• ###### 1. Re: How to apply a measure filter without changing dimensions

Goedemorgen Thijs,

• ###### 2. Re: How to apply a measure filter without changing dimensions

I hoped to receive a solution without it, because I can't share all the data, but I created a new version which I can share; see attached.

• ###### 3. Re: How to apply a measure filter without changing dimensions

Hi Thijs,

For this moment I can only think of a "static" solution what is not desirable and can't get my head around a swift "dynamic" solution.

Would like to ask Simon Runc to the table Simon Runc any idea's?

• ###### 4. Re: How to apply a measure filter without changing dimensions

Hi Thijs;

I think one of your sticking points is you have a single survey element (Satisfaction) spread across three measures.

If you can combine these into two fields: a dimension called SATISFACTION TEST TYPE: {COOP, ICA MAXI, HYPER}, and then a measure called SATISFACTION VALUE, you can add your new dimension and may be home-free.

To be fair, I tried converting your measures to dimensions and constructing a calculated field that would do this, but I kinda wound up banging my head on the wall.

Perhaps this will jar some thoughts loose? It seems like there should be a simple solution for this... perhaps populating null values with the LOOKUP function?

1 of 1 people found this helpful
• ###### 5. Re: How to apply a measure filter without changing dimensions

hi Norbert/Thijs,

So (and I hate to admit defeat!) but I don't think this is possible (I can't even think of a static solution...so you are one up on me!). The problem comes from Measure names not being a "real" dimension, which means it lacks some attributes that "real" dimensions have and as all 3 measures reside in the same row (for a Property) if we exclude NULLs, it filters the entire row (even if one, or more, isn't NULL)

I'll have a think, but I think the only way would be to reshape the data, so it has a single column, called "Satisfaction" with a dimension for Hype, Coop, ICA. Now this might mess up other calculations (eg. if a property name has more than 1 Satisfaction comparison, other numbers will be repeated, so (for example) SUM of Sales would be Doubled/Trippled. Either use LoDs (either in the Viz LoD or actual LoD formulas to get round this)...or (and this would be my preferred way) just create this (long) table as another datasource, and build this Viz from that, and then blend to allow it to talk to other vizes you've built (on the original, wide, datasource).

If anything comes to mind I'll let you know.

• ###### 6. Re: How to apply a measure filter without changing dimensions

Hi Simon,

With "static";) I mean the following.....but for sure not a realistic scenario

a. if [PropertyName]="Elins Esplanad" then [HYPER SATISFACTION] END

b. if [PropertyName]="421" then [HYPER SATISFACTION] END

c. if [PropertyName]="Bergvik Köpcenter" then [COOP SATISFACTION (Bergvik)] END

d. etc.

• ###### 7. Re: How to apply a measure filter without changing dimensions

Thanks all,

The suggestion from Michael should be possible (re-organising the data into a dimenson type + measure). Didn't think of that. Also, thank you Norbert and Simon for the ideas!

Greatly appreciated everybody!

• ###### 8. Re: How to apply a measure filter without changing dimensions

Morgen Thijs,