1 2 Previous Next 15 Replies Latest reply on Mar 7, 2018 3:50 PM by Shinichiro Murakami

    Help needed!!! - A dynamic dimension based on Count Distinct

    edward.alexogiannopoulos

      Hi everyone,

       

      In my head this one was straight forward, however I have hit a brick wall pretty early on.

      I'm hoping that someone here will be smarter than me and able to suggest a solution.

       

      I have data at different levels in a hierarchy. These are like Departments, Business Units, Cost Centres:

       

      I want to create a calculated field called Dir x which can be Dir 2, Dir 3 or Dir 4 depending on the filters applied.

       

      in terms of simple logic, my original idea was something like:

       

      If           Total (countD ( [ Dir 2 ] ) >1 Then  [Dir 2] 

      Elseif    Total (countD ( [ Dir 3 ] ) >1 Then  [Dir 3]

      Else       [Dir 4]

      End

       

      With this, if a Viz only had 1 bar at Dir 3 level, it would drill down automatically to Dir 4 - amazing, right?

       

      My issue is that I can't combine Aggregations  with "If" functions.

      I feel like the solution is in LoD calcs but I haven't been able to find one.

       

      If anyone can suggest a work around it would reduce my number of sheets by 66% so there is a lot at stake for here !!!!!

       

      As always - I look forward to your ideas.

       

      Many thanks,

       

      Ed.

        1 2 Previous Next