3 Replies Latest reply on Jan 22, 2018 6:33 AM by Vinnie Ahuja

    How to filter one dimension, without losing the row related to that dimension.

    Peter Goett

      I can't really post a twbx because this is sensitive, but I am having a filtering issue and I am not sure how to resolve it. I have a crosstab of annual budgets, and spend against those budgets. If "Is Active" = 1, that means the budget or spend is valid; if it = 0, that means the budget or spend was rejected for some reason. There is an "Is Active" field for Budget and an "Is Active" Field for Spend.

       

      1.png

      If you look at 2016 for example. There was a $120,000 budget approved (Is Active (Budget) = 1), but there was no valid spend. The vendor submitted $9,522, but we rejected it. So the line should read:

       

      Phase YearApproved BudgetFees
      2016120,000NULL ( or 0, or blank)

       

      But if I filter "is active (Spend)" to only show 1, the entire 2016 row disappears. How do I make it so it always shows the Budget if = 1, but not spend if it = 0.

      2.png

      You'll also notice I lose $1,992,000 from 2015 that should be added to the $9,960,000 that remains.