Forum Discussion

Nikki's avatar
Nikki
Helper II
6 years ago
Solved

Contains Formula

Hi there I have a series of custom columns example:   Concept (result is either pass fail or query) Initiative (result is either pass fail or query) Project (result is either pass fail or query) ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Nikki ,

     

     

    Create a Calculated Column

     

    Status =
SWITCH (
    TRUE (),
    'Table'[Concept] = "Fail"
        || 'Table'[Initiative] = "Fail"
        || 'Table'[Project] = "Fail"
        || 'Table'[Brief] = "Fail", "Fail",
    'Table'[Concept] = "Query"
        || 'Table'[Initiative] = "Query"
        || 'Table'[Project] = "Query"
        || 'Table'[Brief] = "Query", "Query",
    "Pass"
)

     

     

     

     


    Regards,

    Harsh Nathani


    Appreciate with a Kudos!! (Click the Thumbs Up Button)

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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Nikki ,

 

 

Create a Calculated Column

 

Status =
SWITCH (
    TRUE (),
    'Table'[Concept] = "Fail"
        || 'Table'[Initiative] = "Fail"
        || 'Table'[Project] = "Fail"
        || 'Table'[Brief] = "Fail", "Fail",
    'Table'[Concept] = "Query"
        || 'Table'[Initiative] = "Query"
        || 'Table'[Project] = "Query"
        || 'Table'[Brief] = "Query", "Query",
    "Pass"
)

 

 

 

 


Regards,

Harsh Nathani


Appreciate with a Kudos!! (Click the Thumbs Up Button)

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

  • Nikki's avatar
    Nikki
    Helper II
    6 years ago

    Hi Again

     

    my formula worked:

     

    Concept Stage Status =
    SWITCH (
    TRUE (),
    'Projects'[Concept Status] = "Fail"
        || 'Projects'[P20 Status] = "Fail"
    || 'Projects'[Pact Status] = "Fail"
    || 'Projects'[SPOE Status] = "Fail"
        || 'Projects'[Seed Funding Artefact Status] = "Fail",
         "Fail",
    'Projects'[Concept Status] = "Query"
    || 'Projects'[P20 Status] = "Query"
        || 'Projects'[Pact Status] = "Query"
        || 'Projects'[Seed Funding Artefact Status] = "Query"
    || 'Projects'[SPOE Status] = "Query", "Query",
    "Pass"
    )
     
    but now i have another challenge. I need to only show a result (pass, query or fail) only if the project is in the concept stage. or it has already been thru that stage.
     
    I have another table that flags the stage  which is concept, evaluate, design, build , close
     
    i reapted the above formula for each stage result. but if i am in evaluate i shoudl see a result for concept and evaluate but not for design build close.
     
    i have another table that keeps track of what stage the project is in called projectstage (it has either in progress or completed. for each 5 stages)
     
    what is best way to achieve this ?
    • AntrikshSharma's avatar
      AntrikshSharma
      Community Champion
      6 years ago
      You could use RELATED or LOOKUPVALUE to bring the value from other tables virtually for the duration of the calculation and check if it matches a condition.