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.
Hello All:
I have the following tables Teams and Projects, they are related in a one to many relationship by Team Name:
Teams Table:
Team Name | Team Type |
A team | Regional |
B team | Department |
Projects Table:
Team Name | Project Name | Qualified Project |
Team A | Project A | Yes |
Team A | Project B | No |
Team B | Project C | No |
Team B | Project D | No |
What I want is to have a table visual that will have one row per team, with a custom column or measure that shows "Yes" if there is at least one project with "Yes" in the qualified column or "No" if there are no projects with "Yes". See below:
Team Name | Team Type | Qualified Projects? |
A Team | Regional | Yes |
B Team | Department | No |
If I pull the Qualified Projects field (which is a custom column based on multiple criteria using the Switch function) I would get two rows for Team A, representing the two projects. I know I need some sort of If function probably.
How would you solve this in the best way?
Thanks!
Solved! Go to Solution.
In this case you can get away with a relatively simple measure:
Qualified Project Measure =
MAX ( Projects[Qualified Project] )
This relies on the ranking of text where "Yes" > "No"
Alternatively something like this that doesn't rely on the text sort order:
Qualified Project Measure =
IF (
CALCULATE(
NOT ISEMPTY ( Projects ),
Projects[Qualified Project] = "Yes"
),
"Yes",
"No"
)
Regards,
Owen
Hi,
Drag Team Name and Team Type fields from the Teams Table and write this measure
=if(calculate(countrows(Teams),'Projects'[Qualifies projects])>=1,"Yes","No")
Hope this helps.
In this case you can get away with a relatively simple measure:
Qualified Project Measure =
MAX ( Projects[Qualified Project] )
This relies on the ranking of text where "Yes" > "No"
Alternatively something like this that doesn't rely on the text sort order:
Qualified Project Measure =
IF (
CALCULATE(
NOT ISEMPTY ( Projects ),
Projects[Qualified Project] = "Yes"
),
"Yes",
"No"
)
Regards,
Owen
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |