Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |