Contains Formula

[Nikki]

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

🙂

[harshnathani · Accepted solution]

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]

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]

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.

[AntrikshSharma]

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

[Nikki]

thanks so much !!