Filter by two dimension combinations

Question

My (heavily simplified) data model contains of:

• a fact table with FTE values and two foreign keys for 'Country' and 'Process'
• two dimension tables for 'Country' and 'Process' with relationships to the fact table

I also have a table of combinations of 'Country' and 'Process' that I want to filter my data on:

Country     Process      Country-Process
= = = = = = = = = = = = = = = = = = = =
France      process 1    France process 1
France      process 2    France process 2
Australia   process 1    Australia process 1
Australia   process 3    Australia process 3
USA         process 2    USA process 2
USA         process 3    USA process 3

The goals is to filter my FTE, to show only FTE for these country-process combinations. However, it is not possible to have the Combinations table filter both Country and Process tables through relationships.

If I filter on Country, I get all processes for those countries. If I filter on Process, I get all countries for those processes. If I filter on these countries (there are more) and these processes (there are more), I get all these processes for all these countries. But I just want the combinations listed.

I have succeeded in making a measure with a SUMX on a virtual table that only contains the listed combinations (works in DAX Studio), but the Entity relationship somehow got lost and all countries showed the aggregated value per process.

The measure is set-up as follows:

SUMX(
    FILTER(
        CROSSJOIN(
            ADDCOLUMS(
                CROSSJOIN(VALUES(Country[country]), VALUES(Process[process]) ),
                "Country_Process", Country[country] & " " & Process[process]
            ),
            VALUES(Combination[Country-Process]
        ),
        [Country_Process] = Combinations[Country-Process]
    ),
    [FTE]
)

Help is much appreciated!

Answer 1

Assuming your tables and relationships look like this:

Combinations

Country	Process
France	Process 1
France	Process 2
Australia	Process 1
Australia	Process 3
USA	Process 2
USA	Process 3

Countries

Country
France
Australia
USA

Process

Process
Process 1
Process 2
Process 3

FTE

ID	Country	Process
15	Australia	Process 3
3	Australia	Process 1
5	Australia	Process 3
11	Australia	Process 3
13	France	Process 3
14	France	Process 2
12	France	Process 1
1	France	Process 1
7	France	Process 3
8	France	Process 2
2	USA	Process 3
4	USA	Process 3
6	USA	Process 1
9	USA	Process 3
10	USA	Process 1

Relationships

DAX Calculation:

You can use TREATAS to create a virtual relationship. The measure can be either a visual column or a filter in the table.

Filter FTE = 
        CALCULATE (
            COUNTROWS ( FTE ),
            TREATAS (
                SUMMARIZE ( Combinations, Combinations[Country], Combinations[Process] ),
                FTE[Country],
                FTE[Process]
            )
        )
    

Output