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)

Brief (result is either pass fail or query)

 

I need to then write a formula that tells me the following:

 

Overall Result = if any result of the 4 comes back a fail - its fail, if any result comes back a query - its query else Pass.

 

can you please help me? what is the best option for this formula?

thanks

🙂

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!

5 Replies

  • AntrikshSharma's avatar
    AntrikshSharma
    Community Champion
    6 years ago

    Maybe try something like this:

    Status =
VAR FinalString =
    CONCATENATEX (
        FILTER ( Nikki, Nikki[Num] = EARLIER ( [Num] ) ),
        [Concept] & [Initiative] & [Project] & [Brief]
    )
VAR LowerString =
    LOWER ( FinalString )
VAR PassIncluded =
    SEARCH ( "pass", LowerString, 1, 0 )
VAR QueryIncluded =
    SEARCH ( "query", LowerString, 1, 0 )
VAR Result =
    SWITCH ( TRUE (), QueryIncluded > 0, "Query", PassIncluded > 0, "Pass", "Fail" )
RETURN
    Result

  • 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.