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!

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.