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

[jamielim]

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?

[tresesco · Accepted solution]

Set analysis in qv is very powerful. Try implicit intersection  like:

Sum({<DEPT * = {'DeptA', 'DeptB'}>} NoStaff)

[shraddha_g]

Try

if(wildmatch(getfieldselection(DEPT),'DeptA','DeptB'),sum({<DEPT={'DeptA', 'DeptB'}>}NoStaff),0)

[jamielim]

Thanks!

[jamielim]

Thank you!