How to dynamically filter the whole fact table based upon user selection in Slicer

[HassanAshas]

Hi, I have a dataset like the following, 

Emp Code	Name	Employee Location	Pool	Billed Hours	Total Hours
1	Matt	USA	US Bank	10	60
1	Matt	USA	US State	10	60
2	Avery	USA	US Bank	20	120
3	Aryub	USA	US Bench	10	120
4	Jacob	UK	UK Sports	10	40
4	Jacob	UK	UK Bench	20	40
4	Jacob	UK	CA Bench	30	40
5	Zara	Canada	US State	20	60
5	Zara	Canada	CA Bench	10	60
6	Ali	Canada	CA Sports	20	120
7	John	Australia	US State	50	120
8	Alisha	Australia	US State	50	60
8	Alisha	Australia	US Bench	20	60
9	Novak	Canada	CA Bench	100	120
10	Alex	USA	CA Bench	100	120
11	Emma	USA	UK Bench	30	120

I have another Table which is as given below, 

Employee Location	Pool
USA	US Bench
USA	US State
UK	UK Bench
Canada	CA Bench

These tables can be related with each other using Employee Location (however it would create a Many-to-Many Relation)

I have a report like the following,

It has two Slicers on Employee Location and Pool, and three very simple measures. 

What I want to do is actually with the help of Employee Location Slicer, I want to filter the complete data. Using the second table given, if any Employee Location is selected then the corresponding Pools against that Employee Location, must be excluded from the whole report. 

So, for example, If I select USA, I don't want to include any rows that have Pool in {"US State", "US Bench"}. These US State and US Bench information is coming from the Other table. 

That is, following highlighted rows (Pool is highlighted) must be excluded from the table and the three measures (or from the whole dataset) 

Similarly, if I select "UK" from the Employee Location, then following rows should be excluded from the whole report, 

This should also work with Multi-Select in the Slicer. So, If I select UK and USA from the Slicer, then the following rows must be excluded from the Final Result 

and if no slicer is selected (that is Employee Location is ALL then no row should be excluded. This can be very easily achieved using ISFILTERED function but I can't seem to get how can I achieve the other conditions) 

I have tried to apply a Measure at Page Level or on All Pages level but that doesn't work. Can anyone help me out in this, please? Thank you. 

If you would like to download the Power BI File, then you may do so from here: https://drive.google.com/file/d/1kmyw9SxKvzy7-QQDLl5nJ7xsJSwBpQKh/view?usp=sharing

[CNENFRNL · Accepted solution]

[HassanAshas]

@CNENFRNL  Thank you! This works so perfectly well! I just got two questions if you can answer, 

First, I was able to achieve somewhat of same result but with a slightly different DAX, 

Total Billed Hours = 

VAR selected_locations = VALUES(Employees[Employee Location])

VAR Filtered_Locations_Table = 
    FILTER(
        'Location Pool Mapping', 
        'Location Pool Mapping'[Employee Location] in selected_locations
    )

var _FilteredTable = 
    FILTER(
        Employees, 
        IF(
            ISFILTERED(Employees[Employee Location]), 
            NOT(
                Employees[Pool] in 
                SUMMARIZE(Filtered_Locations_Table, [Pool])
            ),
            TRUE()
        )
    )

var _FilteredBilledHrs = 
    SUMX(
        _FilteredTable, 
        Employees[Billed Hours]
    )


var _NonFilteredBilledHrs = SUM(Employees[Billed Hours])

var _Result = 
    IF(
        ISFILTERED(Employees[Employee Location]), 
        _FilteredBilledHrs, 
        _NonFilteredBilledHrs
    )

return _Result

I believe that using TREATAS is more optimized than my approach, can you tell me if I am right to believe that?

The main question, however, that now I have three measures. My problem is I need to copy paste this FILTERING CODE in each measure and then use this Filtered Table for my calculations,

VAR selected_locations = VALUES(Employees[Employee Location])

VAR Filtered_Locations_Table = 
    FILTER(
        'Location Pool Mapping', 
        'Location Pool Mapping'[Employee Location] in selected_locations
    )

var _FilteredTable = 
    FILTER(
        Employees, 
        IF(
            ISFILTERED(Employees[Employee Location]), 
            NOT(
                Employees[Pool] in 
                SUMMARIZE(Filtered_Locations_Table, [Pool])
            ),
            TRUE()
        )
    )

This is very repetitive and bad practice. That is, if I want to change one thing, I will have to change in all the measures and etc.

I also don't have the option to use Measure to return my filtered table (because measure can't return a table). Is it any possible way to make it cleaner? Like I could create one function/measure and then call that measure in all my DAX Measures? (just like I create a measure to return my filtered table and use that measure in all my DAX Codes) 

[CNENFRNL]

1. performance
   Propagating filters using TREATAS in DAX - SQLBI
   Here's a detailed comparison among major ways of filtering for your reference.
2. repetition
   AFAIK, only scalar result is permitted for a measure. 

[tamerj1]

Hi @HassanAshas 

place the following measure in the filter pane of the table visual. Select "is not blank" then apply the filter 

FilterMeasure =
COUNTROWS ( FILTER ( Table1, NOT ( Table1[Pool] IN VALUES ( Table2[Pool] ) ) ) )

[HassanAshas]

Hi @tamerj1 

Thanks a lot for your answer. 

Unfortunately it didn't quite solve the issue. Not sure what's the problem, but for some reason, I am not able to place this measure as visual level filter on my Card Visuals 

When I place it, it doesn't allow me to apply the filter on Card,

It did work on the Table Visual but I needed to show all the records if nothing is selected in the Slicer. So, I added an IsFiltered condition at the top of measure, but after adding that, my filter did not work for some reason 😕 Not sure where I went wrong. 
I used the following Measure, 

Filter Measure = 
IF(
    ISFILTERED('Location Pool Mapping'[Employee Location]),
    COUNTROWS(FILTER(Employees, NOT(Employees[Pool] in VALUES('Location Pool Mapping'[Pool])))), 
    1
)

[tamerj1]

@HassanAshas 

No it doesn't work with card visuals. Each measure needs to include the filter code within its DAX. Fr simplicity CALCULATE - FILTER arrangement will do. 

[HassanAshas]

How can I include the filter code within the each measure DAX? Can you please help out in this? 
I tried to do it but it didn't work for me. 

I tried something like this (only considering Billed Hours Sum Measure for now), 

Total Billed Hours = 

var _FilteredTable = 
    FILTER(
        Employees, 
        NOT(Employees[Pool] in VALUES('Location Pool Mapping'[Pool]))
    )

var _filteredSUM = 
    SUMX(
            _FilteredTable, 
            Employees[Billed Hours]
        )
var _NonFilteredSUM = SUM(Employees[Billed Hours])

var _BilledHours = 
    IF(
        ISFILTERED(Employees[Employee Location]), 
        _filteredSUM, 
        _NonFilteredSUM
    )

return _BilledHours

The result it gave me was this (Please check Total Billed Hours) 

The problem with the result is it has now excluded all the pools in my other table, rather than only excluding the pools which were against USA. 

That is, only US State and US Bench were supposed to be excluded from the result, but it is also excluding CA Bench and UK Bench whicch are against the "Canada" and "UK" Locations, respectively
I tried to apply a Filter within the VALUES Command, but again it did not work for me. 

Not sure how can I do it with CALCULATE Function, I haven't tried it and I usually try to avoid it as it most often causes more problems than I should be getting. More of a follower of @Greg_Deckler  on this, lol. Haven't really used CALCULATE for quite a while, and that's why I am hesitant to use it now for this as well 😕