4 Replies Latest reply: Dec 2, 2016 1:27 AM by Jamie Lim RSS

    If filter selection by user exists in list, then use a certain expression, else 0

    Jamie Lim

      Hi, I have a filter on this field DEPT that user can select from a list of values (e.g. DeptA, DeptB, DeptC, DeptD)

       

      In a measure, i want to set up an expression that calculates NoStaff which depends on what DEPT is selected by the user.

       

      If user select DeptA, the measure returns value using NoStaff in DeptA

      If user select DeptB, the measure returns value using NoStaff in DeptB

      if user select DeptC &/or DeptD, the measure returns '0'

      If user select DeptA & DeptC, the measure returns value using NoStaff in DeptA only

      If user select DeptA, DeptB & DeptC, the measure returns value using NoStaff in DeptA & DeptB only

      ...

       

      Based on the above logic, how should I set up my expression?

       

      I know I can use If, then, else. But this seems to suggest that I need to list every possible permutation of choice by the user. Is there an easier way, similar to the way SQL allows

      if (DEPT in ('DeptA', 'DeptB'),  sum({<DEPT={'DeptA', 'DeptB'}>}NoStaff),0)--> but I'm not sure if this will handle case where user selects DeptA and DeptC.

       

      Any advice?