cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
james_andrade
New Member

How to get values for relationship between 3 tables

The Comparison Table

Project_IDProject_Plan_ID
Project_A_2021Project_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 NameActual
Project_A_2021

Pick Apples

1
Project_A_2021Pick Oranges2
Project_B_2021Pick Apples3
Project_B_2021Pick Oranges4

The Project_Plan Table

Project_Plan_ID

Activity NamePlanned
Project_Plan_A_2021

Pick Apples

5
Project_Plan_A_2021Pick Oranges6
Project_Plan_A_2020Pick Apples7
Project_Plan_A_2020Pick Oranges8
Project_Plan_B_2021 Pick Apples9
Project_Plan_B_2021Pick Oranges10
Project_Plan_B_2020 Pick Apples11
Project_Plan_B_2020Pick Oranges12

 

Result I'm looking for:

Project filtered to: Project_B_2021

Project Plan filtered to: Project_Plan_B_2020

Output

Activity NamePlannedActual
Pick Apples113
Pick Oranges124

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.

1 ACCEPTED SOLUTION
v-yuaj-msft
Community Support
Community Support

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

v-yuaj-msft_0-1619601081067.pngv-yuaj-msft_1-1619601102832.png

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:

v-yuaj-msft_2-1619601285965.png

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.

 

View solution in original post

2 REPLIES 2
v-yuaj-msft
Community Support
Community Support

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

v-yuaj-msft_0-1619601081067.pngv-yuaj-msft_1-1619601102832.png

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:

v-yuaj-msft_2-1619601285965.png

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.

 

View solution in original post

v-yuaj-msft
Community Support
Community Support

Hi @james_andrade ,

 

My test result is here.

v-yuaj-msft_0-1619428367650.png

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

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors