Forum Discussion

Nikki's avatar
Nikki
Helper II
6 years ago
Solved

Help with formula

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

Need Help
  • parry2k's avatar
    parry2k
    6 years ago

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

2 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

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