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

Custom column help- custom column based on multiple conditions

Hello All:

 

I am new to PowerBI but have some basic programming/SQL skills WAYY back in my career.  I am trying to make a custom column called QualityProject using PowerBI that will show either a "Yes" or "No".  Here's a representation of my table and the columns that would be involved:

Project NameProjectStartDateProjectStatusProjectResultsCountofTOC
     

 

Here's my psuedocode:

IF ProjectStartDate>=12/1/2019 AND ProjectStatus=Complete AND ProjectResults=Target OR Stretch AND CountofTOC>=1

THEN QualityProject = Yes 

ELSE QualityProject = No

 

I would then like to make a table visual with this that would show the Team Name and a count of the projects where QualityProject = Yes (a team could have multiple projects, some would have Yes in the QualityProject column and some would have No depending on the criteria of the project).  I have a Team table that is already related to the Project table in a one to many relationship.  

 

What would be the easiest way to go about all of this in PowerBI?  Thanks for any help/advice in advance!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Custom column help- custom column based on multiple conditions

@relentless226 

You can create a new column with the following measure: 

QualityProject =
IF (
    Projects[ProjectStartDate] >= DATE ( 2019, 12, 1 )
        && Projects[ProjectStatus] = "Complete"
        && Projects[ProjectResults] IN { "Target", "Stretch" }
        && Projects[CountofTOC] >= 1,
    "Yes",
    "No"
)

v-jingzhang_0-1601286263832.png

 

Then, create a measure to get the count of projects with QualityProject = Yes:

Count = COUNTROWS ( FILTER ( Projects, Projects[QualityProject] = "Yes" ) )

 

And finally use the table visual to display them.

v-jingzhang_1-1601286263834.png

 

Best Regards,

Community Support Team _ Jing Zhang

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

View solution in original post

1 REPLY 1
Highlighted
Microsoft
Microsoft

Re: Custom column help- custom column based on multiple conditions

@relentless226 

You can create a new column with the following measure: 

QualityProject =
IF (
    Projects[ProjectStartDate] >= DATE ( 2019, 12, 1 )
        && Projects[ProjectStatus] = "Complete"
        && Projects[ProjectResults] IN { "Target", "Stretch" }
        && Projects[CountofTOC] >= 1,
    "Yes",
    "No"
)

v-jingzhang_0-1601286263832.png

 

Then, create a measure to get the count of projects with QualityProject = Yes:

Count = COUNTROWS ( FILTER ( Projects, Projects[QualityProject] = "Yes" ) )

 

And finally use the table visual to display them.

v-jingzhang_1-1601286263834.png

 

Best Regards,

Community Support Team _ Jing Zhang

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

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

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
Users online (891)