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


      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]



      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,



        1 2 Previous Next