How to get values for relationship between 3 tables

[james_andrade]

The Comparison Table

Project_ID	Project_Plan_ID
Project_A_2021	Project_Plan_A_2020
Project_A_2021	Project_Plan_A_2021
Project_B_2021	Project_Plan_B_2020
Project_B_2021	Project_Plan_B_2021

From this table the user will select 1 Project and 1 Project_Plan to compare it to

The Project Table

Project_ID	Activity Name	Actual
Project_A_2021	Pick Apples	1
Project_A_2021	Pick Oranges	2
Project_B_2021	Pick Apples	3
Project_B_2021	Pick Oranges	4

The Project_Plan Table

Project_Plan_ID	Activity Name	Planned
Project_Plan_A_2021	Pick Apples	5
Project_Plan_A_2021	Pick Oranges	6
Project_Plan_A_2020	Pick Apples	7
Project_Plan_A_2020	Pick Oranges	8
Project_Plan_B_2021	Pick Apples	9
Project_Plan_B_2021	Pick Oranges	10
Project_Plan_B_2020	Pick Apples	11
Project_Plan_B_2020	Pick Oranges	12

Result I'm looking for:

Project filtered to: Project_B_2021

Project Plan filtered to: Project_Plan_B_2020

Output

Activity Name	Planned	Actual
Pick Apples	11	3
Pick Oranges	12	4

Note: I don't want to display output until the Project and Project Plan have not been selected

The joins in this relationship are:

'Project' table to 'Comparison' table using 'Project_ID'

'Project_Plan' table to 'Comparison' table using 'Project_Plan_ID'

'Project' table to 'Project_Plan' table using 'Activity_Name'

Can anyone tell me how to do this? Everything I've tried so far does not work.

[v-yuaj-msft · Accepted solution]

Hi @james_andrade ,

Based on your description, you could do some steps as follows.

1. create a relationship between "The Project Table" and "The Project_Plan Table" and make it active

2. create two measures

Actual =
CALCULATE (
    MAX ( 'Project'[Actual] ),
    FILTER ( 'Project', [Project_ID] = SELECTEDVALUE ( Comparison[Project_ID] ) )
)

Planned =
CALCULATE (
    MAX ( 'Project_Plan'[Planned] ),
    FILTER (
        'Project_Plan',
        [Project_Plan_ID] = SELECTEDVALUE ( Comparison[Project_Plan_ID] )
    )
)

3. create a table visual.

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

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

[v-yuaj-msft]

Hi @james_andrade ,

My test result is here.

Due to the one-to-many relationship between "Project_ID" and "Project_Plan_ID", this result will occur if only the relationships are established in three tables.

Do you just want to display the output?

Best Regards,

Yuna