Filter from various tables

[Kaycee_1]

I am trying to calculate the number of staff that meet certain criteria. I have written a DAX formular but it doesn't give me what i expect. I broke the formular into small bits and i got various numbers of staff but putting it together, and i get just 2. I have used excel to create a pivot table and i get about 600.  This is the formular i wrote

Measure 10 = CALCULATE(COUNT('SIP (2)'[Assignment ID]),

             'SIP (2)'[Assignment Status] IN {"Career Break", "Inactive Not Worked", "Maternity & Adoption", "Out on External Secondment - Paid"},

            'Org L3'[Org L3] IN {"880 Business Intelligence & PM Bank L3", "880 Information Technology Bank L3", "880 Flexible Resourcing Bank L3", "880 Business Services Bank L3", "880 Clinical Services Bank L3", "880 Procurement Bank L3", "880 Health Care Solutions Bank L3"},'SIP (2)'[Months in Post] < 12,

        'Leavers Detail'[Termination Date] )

[Anonymous · Accepted solution]

Hi @Kaycee_1 ,

Pleasemake sure whether the conditons in your code are AND logic or OR logic. As far as I know, your code above will run as AND logic, so only data meet all conditions will be counted. You can try Filter and OR() function to achieve your goal.

Here I create a sample to show you And and Or logic.

Or = CALCULATE(COUNT('SIP (2)'[Assignment ID]),FILTER('SIP (2)','SIP (2)'[Assignment Status] IN {"A","B","C"}||MAX('Org L3'[Org L3]) in {"O1","O2"}))

And = CALCULATE(COUNT('SIP (2)'[Assignment ID]),'SIP (2)'[Assignment Status] IN {"A","B","C"},'Org L3'[Org L3] in {"O1","O2"})

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.