1 Reply Latest reply on Dec 16, 2013 7:16 AM by Simon Peacock

    Can you Filter across Multiple, unrelated Dimensions?

    Simon Peacock

      Hi, I wonder if anyone out there can help with this.


      I have a table that contains application names and some details about their strategic use.


      it looks a 'little' like this



      App          Investment     Current     Transition     Strategic

      Bob               S               Y               Y               Y

      Colin              H               Y               Y               N

      Dilbert          D                   Y               N               N

      Ethel               S               N               N                    Y

      Fred               H               N               Y                    N

      George          D               Y               N                    N


      So i have dimensions called App as a text, Invesment as colour and want to filter on where current, transtion or Strategic are Y


      I've built a really cool looking application landscape which maps out the business function these apps support and what the Investment is (Strategic, Hold, Demise)




      And using filters I can select a viewpoint on Current, Transition Phase, and strategic phase. Everyone still with me?




      It works well BUT - in order to switch between the different viewpoints i need to filter, and at the moment the only way i can get it to work is by dragging 'Current' into the filter - and then if i want to switch, dragging it out and replacing it with the Transition filter. ideally what I'd like is a filter at Single Value (dropdown) filter to select Current, transition or Strategic.


      The problem is, if i use an IF statement, the further down i get i lose values - for example


      If [Current] = 'Y' then 'current'      

      ElseIf [Transition] = 'Y' then 'Transition'

          elseif [Strategic] = 'Y' then 'Strategic'



      Using this then Colin doesn't show up in my transition filter as he is being classed as Current. If i go to a strategic view i only see Ethel, because poor Bob is classed as current, - so that's no good.


      I also tried case, string all together, but that's not working either as you obviously can't have 2 outcomes from a case


      CASE [Calculation1]

          WHEN 'YNN' THEN 'Current'

          WHEN 'YYN' THEN 'Current'

          WHEN 'YYY' THEN 'Current'

          WHEN 'YYN' THEN 'Transition'

          WHEN 'YYY' THEN 'Transition'

          WHEN 'NYN' THEN 'Transition'

          WHEN 'YYY' THEN 'Strategic'

          WHEN 'NYY' THEN 'Strategic'

          WHEN 'NNY' THEN 'Strategic'

          WHEN 'NNN' THEN 'Never'



      In summary the issue is that something in Transition can also be in Current, and the IF and CASE are definitive.


      So if anyone is still with me and has some excellent ideas then I'd be very grateful





        • 1. Re: Can you Filter across Multiple, unrelated Dimensions?
          Simon Peacock



          I think I found the answer myself.


          It's quite easy actually, suprisingly so (just trying to justify with myself why i needed to ask in the first place!).


          Basically I made a parameter, with 3 choices that refer to my viewpoints


          like so;



          then i simply created a calculated field called 'Selection Filter' as follows;


          case [Viewpoint to Show]

          WHEN 'Current' then [Current]

          WHEN 'Transition' then [Transition]

          WHEN 'Strategic' then [Strategic]



          where [dimensions] are my dimensions or columns listed way above


          then i simply pulled the calc filed 'Selection Filter' into my filter pill and selected Y.


          And now i have a parm on my viz i can select which viewpoint i need and it works!!


          Yay me!


          Thanks for looking and I hope if you came here looking for similar answers this helped