Forum Discussion

relentless226's avatar
relentless226
Regular Visitor
5 years ago
Solved

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!

 

 

  • 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"
    )

     

    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.

     

    Best Regards,

    Community Support Team _ Jing Zhang

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

1 Reply

  • v-jingzhang's avatar
    v-jingzhang
    Community Support

    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"
    )

     

    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.

     

    Best Regards,

    Community Support Team _ Jing Zhang

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