Help with formula

[Nikki]

hi i have a table with 2 fields for each purpose.

each field below has the same drop downs (NA, Yes or No) -  can only choose one

initiative brief required      initiative brief status

business case required      business case status

pmp required                    pmp status

schedule plan required     schedule plan status

i need to then get a value either pass fail or query for the status for each field.

Where required is yes and completed is no – display     Fail          

where required is yes and completed is yes – display   Pass       

where required is yes and completed is   NA – display    Fail

Where required is no and completed is no – display     Pass        

Where required is no and completed is yes – display     Query           

Where required is no and completed is NA – display     Pass          

Where required is NA and completed is no – display      Pass        

Where required is NA and completed is yes – display      Query         

Where required is NA and completed is NA – display      Pass

how to tackle this, 

all help aprpeciated...

[parry2k · Accepted solution]

@Nikki add a custom column to calculate the status, you can do it in PQ or DAX, here is a DAX version

Status = 
SWITCH ( TRUE(),
( Table[Required] = "Yes" && Table[Completed] = "Yes" ) ||
( Table[Required] = "No" && Table[Completed] = "No" ) ||
( Table[Required] = "No" && Table[Completed] = "NA" ) ||
( Table[Required] = "NA" && Table[Completed] = "No" ) ||
( Table[Required] = "NA" && Table[Completed] = "NA" ), "Pass",
( Table[Required] = "Yes" && Table[Completed] = "No" ) ||
( Table[Required] = "Yes" && Table[Completed] = "NA" ), "Fail",
( Table[Required] = "No" && Table[Completed] = "Yes" ) ||
( Table[Required] = "NA" && Table[Completed] = "Yes" ), "Query"
)

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[Nikki]

thankyou so much !!!!!