Forum Discussion

spandy34's avatar
spandy34
Responsive Resident
2 years ago
Solved

Creating a Column Multiple Criteria

I have a table called RAG Monitoring that monitors the progress of projects.  I want to create a column called % Progress .  I don’t know how to represent what I want but looking at the above table I need a column which calculates the following :-

 

If Foundation Status shows In Progress then 10%

If Foundation Status shows Complete then 20%

If Discovery & Design Status shows In Progress then 30%

If Discovery & Design Status shows Complete then 40%

If Planning Status shows In Progress then 50%

If Planning Status shows Complete then 60%

If Delivery Status shows In Progress then 70%

If Delivery Status shows Complete then 80%

If Closure Status shows In Progress then 90%

If Closure Status shows Complete then 100%

 

How is the best way to do this?  I tried to create a Conditional Column in Query Editor but I think I need to consider multiple columns.  Can someone advise on the best way to do this.

 

Thank you

 

@danextian 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi spandy34 

     

    If you click the custom column, you can paste this code: 

    if [#"Closure Status "] = "Complete" then 100 else if [#"Closure Status "] = "In Progress" then 90 else if [#"Delivery Status "] = "Complete" then 80 else if [#"Delivery Status "] = "In Progress" then 70 else if [#"Planning Status "] = "Complete" then 60 else if [#"Planning Status "] = "In Progress" then 50 else if [#"Discovery & Design Status "] = "Complete" then 40 else if [#"Discovery & Design Status "] = "In Progress" then 30 else if [#"Foundation Status "] = "Complete" then 20 else if [#"Foundation Status "] = "In Progress" then 10 else 0)

    Here's the PBIX file for your reference.

     

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    Creating a Column Multiple Criteria.pbix76 KB

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi spandy34 

     

    Please try this:

    You can add a custom column in power query:

    if
if [Foundation Status] = "Complete" then 20 else
if [Foundation Status] = "In Progress" then 10 else
if [Discovery & Design Status] = "Complete" then 40 else
if [Discovery & Design Status] = "In Progress" then 30 else
if [Planning Status] = "Complete" then 60 else
if [Planning Status] = "In Progress" then 50 else
if [Delivery Status] = "Complete" then 80 else
if [Delivery Status] = "In Progress" then 70 else
if [Closure Status] = "Complete" then 100 else
if [Closure Status] = "In Progress" then 90 else
0

    If you encounter any specific errors or need further assistance with the formula, please contact me.

     

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • spandy34's avatar
      spandy34
      Responsive Resident
      2 years ago

      Hi

      I put the code you suggested into a custom column and didnt get any results so I changed equals to, to contains as below.  This is still not working as I am only getting 10 and 20 in all of the columns.  The column is not taking into account the remaining columns.  

       

       

       

      Maybe I need the column to say 

       

      if [Foundation Status] contains "Complete" then 20 else
      if [Foundation Status] contains "In Progress" then 10 else
      or

      if [Foundation Status] contains "Complete"
      && [Discovery & Design Status] = "Complete" then 40 else
      or
      if [Foundation Status] contains "Complete"
      && [Discovery & Design Status] = "In Progress" then 30 else
      or
      if [Foundation Status] contains "Complete"
      && Discovery & Design Status] = "Complete" then 40 else
      or
      if[Foundation Status] contains "Complete"
      && [Discovery & Design Status] = "Complete"
      && [Planning Status] = "Complete" then 60 else
      or
      if[Foundation Status] contains "Complete"
      && [Discovery & Design Status] = "Complete"
      && [Planning Status] = "In Progress" then 50 else
      0

       

      Can I have your opinion please?