Allexcept filter bewteen tables

[Kraftfood]

Hello the community,

I have an issue with using allexcept in DAX.

I have 3 tables, 1 fact table lists the "tickets" raised, this table is related to the 2 others that are the list of active employee and their location and the list of all employee and their location since creation of the company.

I'm counting the number of "ticket" that have been raised and filtering by location.

There are others fields that I haven't included in my test power bi, but I use the location of the "active employee" table for other graphs, however, I would like the total ticket number to show the total ticket raised by all employee, not all employee. (I use roles and row level security).

I tried using allexcept within a calculate formula, by itself, tried allselected and all as well. I always have either the same number, either no filters are taken.

Please see the excel tables bases: https://1drv.ms/x/s!AkGwzt_UiPRCg3Ii-h7WJ3Bpd4zJ?e=cewQVR

Power bi demo: https://1drv.ms/u/s!AkGwzt_UiPRCg3Eoq6Dv81IfE6oh?e=s6xBnD

Do not hesitation to ask if anything is unclear.

Thanks in advance!

[v-henryk-mstf]

Hi @Kraftfood ,

Based on your description, is RLS also part of your screening criteria? Also are you able to provide a screenshot of the expected results and describe them so that I can answer them for you as soon as possible.

Looking forward to your reply.

Best Regards,
Henry

[Kraftfood]

Hi Henry, thanks for taking the time to help me. Yes I use RLS to share the information and that changes the information displayed on my graphs and tables. 

Ok so I'll take the example of Central Europe. The expected result is 38 tickets raised, you obtain that when filtering the "Cluster All Employee", but when filtering it with the "Cluster Active Employee" only you obtain 20, and when filtering with both Clusters the answer is also 20. I would to be able to filter by both clusters filters and obtain 38. Hence the utilisation of a allexcept or allselected filter formula but I can't figure out how to write it correctly. See screenshot