cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Help needed for custom Yes/No measure involving a table that has multiple "Yes" and "No" results

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 NameTeam Type
A teamRegional
B teamDepartment

 

Projects Table:

Team NameProject NameQualified Project
Team AProject AYes
Team AProject BNo
Team BProject CNo
Team BProject DNo

 

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 NameTeam TypeQualified Projects?
A TeamRegionalYes
B TeamDepartmentNo

 

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!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I
Super User I

Re: Help needed for custom Yes/No measure involving a table that has multiple "Yes" and &q

Hi @relentless226 

 

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


Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn

View solution in original post

2 REPLIES 2
Highlighted
Super User I
Super User I

Re: Help needed for custom Yes/No measure involving a table that has multiple "Yes" and &q

Hi @relentless226 

 

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


Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn

View solution in original post

Highlighted
Super User III
Super User III

Re: Help needed for custom Yes/No measure involving a table that has multiple "Yes" and &q

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors