1 Reply Latest reply on Mar 20, 2016 5:20 PM by Bill Lyons

    Dynamic Calculated field based on column value?

    Amit Misra



      I have a situation, I can't imagine it's unique, where I have a table with Department, Division and count of Total Publications. A publication could be owned by two employees, who could be in two different departments, or the same department but two different divisions within it so in the table, it could be repeated. I need to present a chart to show Total distinct publications by year and have a filter for Department and Division. I am using Dashboard Filter actions using a sheet each for Department and Division on the dashboard to drive the chart.


      So the logic I need to implement is, IF Department selected equals "None" then count total distinct publications ELSEIF Department selected equals "A" then show distinct publications for "A" ELSEIF IF Division selected equals "A1" then show distinct publications for "A1"


      I can create three measures for each scenario but basically, I need a technique to question the driver sheet what the selection and be able to use that to select one of my three measures dynamically. If I have nothing selected in a sheet, I want to use Measure 1 that calculates Total Distinct Publications, but if I select a Department, I want to use Measure 2 which calculates Distinct Publications for a Department and similarly for Divisions with Measure 3.


      Is this doable? Has anyone implemented this in a different way?


      Please let me know if there are any questions. I tried to explain it in the simplest way that I could. Thanks.