topic Re: Creating a Column Multiple Criteria in DAX Commands and Tips

Creating a Column Multiple Criteria

spandy34 — Wed, 08 May 2024 20:55:41 GMT

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

Re: Creating a Column Multiple Criteria

Anonymous — Thu, 09 May 2024 01:45:21 GMT

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.

Re: Creating a Column Multiple Criteria

spandy34 — Thu, 09 May 2024 07:10:30 GMT

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?

Re: Creating a Column Multiple Criteria

Anonymous — Thu, 09 May 2024 07:14:39 GMT

Hi spandy34

Can you share some sample data(exclude sensitive data) which can make us help you better.

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

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.

Re: Creating a Column Multiple Criteria

spandy34 — Thu, 09 May 2024 07:50:11 GMT

Mission

Project ID

Project

Project Checklist

Foundation Status

Foundation Gateway Approved at Mission Board

Foundation Gateway Approved at CMT PoF Board

Discovery & Design Status

Discovery & Design Gateway Approved at Mission Board

Discovery & Design Gatway Approved at CMT PoF Board

Planning Status

Planning Gateway Approved at Mission Board

Planning Gateway Approved at CMT PoF Board

Delivery Status

Delivery Gateway Approved at Mission Board

Delivery Gateway Approved at CMT PoF Board

Closure Status

Closure Gateway Approved at Mission Board

Closure Gateway Approved at CMT PoF Board

RAIDL

Project Delivery Plan

Comms Plan

Risk Assessment

Interdependancies mapped

TOM

Capital Req

Start Date

End Date

Current Gateway

Status

Potential Budget Reduction 24/25

Potential Budget Reduction 25/26

Potential Budget Reduction 26/27

PID

Options Appraisal

RAIDL _1

Project Delivery Plan

Benefits Card

Index

Unique Link

Mission Project

% Progress

Transformation

MT01

Waste Collection

Complete

18/10/2023

24/10/2023

In Progress

########

In Progress

Not Started

In Progress

Yes

########

31/08/2026

GW2 - Discovery & Design

On Schedule

£12.00

£13.00

£14.00

Waste PID

Waste Options Appraisal

Waste RAIDL.xlsx (sharepoint.com)

Waste Project Plan

1 Transformation MT01 Waste Collection

Transformation - Waste Collection

Transformation

MT02

Fleet Management

Complete

16/11/2023

21/11/2023

In Progress

########

In Progress

Not Started

RAIDL not updated

In Progress

########

01/02/2025

GW2 - Discovery & Design

On Schedule

Fleet PID

Not Completed Yet

Fleet RAIDL

Fleet Project Plan

2 Transformation MT02 Fleet Management

Transformation - Fleet Management

Transformation

MT03

Supporting people to live independently

Complete

15/02/2024

28/02/2024

In Progress

Not Started

In Progress

########

01/12/2024

GW1 - Foundation

On Schedule

Intermediate Care PID

Not Completed Yet

Intermediate Care RAIDL

Intermediate Care Project Plan

3 Transformation MT03 Supporting people to live independently

Transformation - Supporting people to live independently

Transformation

MT12

Fees & Charges

Complete

25/03/2024

08/04/2024

In Progress

Not Started

RAIDL not updated

In Progress

24-Jan-24

01/04/2025

GW1 - Foundation

On Schedule

Fees & Charges PID

Not Completed Yet

Not Maintained

Not Completed Yet

12 Transformation MT12 Fees & Charges

Transformation - Fees & Charges

Transformation

MT13

Children in Our Care

Complete

15/02/2024

28/02/2024

In Progress

Not Started

In Progress

Complete

In Progress

Yes

########

01/04/2026

GW2 - Discovery & Design

Behind Schedule

CIOC PID

Not Completed Yet

CIOC RAIDL

CIOC Project Plan

13 Transformation MT13 Children in Our Care

Transformation - Children in Our Care

Transformation

MT14

SEND/High Needs

Complete

15/02/2024

28/02/2024

In Progress

Not Started

In Progress

Not Started

In Progress

Yes

########

01/09/2024

GW2 - Discovery & Design

On Schedule

SEND/High Needs PID

SEND Options Appraisal

SEND RAIDL

SEND Project Plan

14 Transformation MT14 SEND/High Needs

Transformation - SEND/High Needs

Transformation

MT15

Cashiers

Complete

N/A

Complete

In Progress

Complete

In Progress

Complete

In Progress

########

01/09/2023

GW5 - Review

On Schedule

Cashiers PID

Cashiers Financial Appraisal

N/A

15 Transformation MT15 Cashiers

Transformation - Cashiers

Communities

MCOM01

Communications and Engagement

In Progress

01-Jan-24

GW1 - Foundation

On Schedule

16 Communities MCOM01 Communications and Engagement

Communities - Communications and Engagement

Communities

MCOM02

Community Engagement

In Progress

01-Jan-24

GW1 - Foundation

On Schedule

17 Communities MCOM02 Community Engagement

Communities - Community Engagement

Communities

MCOM03

Community Development

In Progress

01-Jan-24

GW1 - Foundation

On Schedule

18 Communities MCOM03 Community Development

Communities - Community Development

Colleagues

MC01

Organisational Culture

In Progress

04/04/2024

25/04/2024 TBC

In Progress

01-Jan-24

01/06/2025

GW1 - Foundation

On Schedule

Organisational Culture PID

N/A

Org Culture RAIDL

Not Completed Yet

19 Colleagues MC01 Organisational Culture

Colleagues - Organisational Culture

Colleagues

MC02

Communication & Engagement

In Progress

04/04/2024

25/04/24 TBC

In Progress

01-Jan-24

01/06/2025

GW1 - Foundation

On Schedule

Comms & Engagement PID

N/A

Comms & Engagement RAIDL

Not Completed Yet

20 Colleagues MC02 Communication & Engagement

Colleagues - Communication & Engagement

Colleagues

MC03

Smarter Working

In Progress

04/04/2024

25/04/24 TBC

In Progress

01-Jan-24

01/06/2025

GW4 - Delivery

On Schedule

Smarter Working PID

N/A

Smarter Working RAIDL

Not Completed Yet

21 Colleagues MC03 Smarter Working

Colleagues - Smarter Working

Colleagues

MC04

Dunedin/Relocation

In Progress

04/04/2024

25/04/24 TBC

In Progress

01-Jan-24

01/08/2024

GW4 - Delivery

On Schedule

N/A

Dunedin RAIDL

Dunedin Project Plan

22 Colleagues MC04 Dunedin/Relocation

Colleagues - Dunedin/Relocation

Colleagues

MC05

Attract & Retain

In Progress

04/04/2024

25/04/24 TBC

In Progress

01-Jan-24

01/06/2025

GW1 - Foundation

Behind Schedule

Attract & Retain PID

N/A

Attract & Retain RAIDL

Attract & Retain Project Plan

23 Colleagues MC05 Attract & Retain

Colleagues - Attract & Retain

Colleagues

MC06

Happy and Healthy workforce

In Progress

04/04/2024

25/04/24 TBC

In Progress

01-Jan-24

01/06/2025

GW1 - Foundation

On Schedule

Happy & Healthy Workforce

N/A

Happy & Healthy Workforce RAIDL

Not Completed Yet

24 Colleagues MC06 Happy and Healthy workforce

Colleagues - Happy and Healthy workforce

Colleagues

MC07

Workforce Planning

In Progress

04/04/2024

25/04/24 TBC

In Progress

01-Jan-24

01/06/2025

GW1 - Foundation

Behind Schedule

Workforce Planning PID

N/A

Workforce Planning RAIDL

Not Completed Yet

25 Colleagues MC07 Workforce Planning

Colleagues - Workforce Planning

Colleagues

MC08

Workforce Development

In Progress

04/04/2024

25/04/24 TBC

In Progress

01-Jan-24

01/06/2025

GW1 - Foundation

Behind Schedule

Workforce Development PID

N/A

Workforce Development RAIDL

Not Completed Yet

26 Colleagues MC08 Workforce Development

Colleagues - Workforce Development

Partnerships

MPar01

Charter

In Progress

08/04/2024

########

GW1 - Foundation

On Schedule

Partnership Mission Charter

N/A

Partnerships Mission RAIDL

Not Completed Yet

27 Partnerships MPar01 Charter

Partnerships - Charter

Partnerships

MPar02

Team Stockton

In Progress

########

GW1 - Foundation

On Schedule

28 Partnerships MPar02 Team Stockton

Partnerships - Team Stockton

Partnerships

MPar03

Bilateral Partnerships

In Progress

########

GW1 - Foundation

On Schedule

29 Partnerships MPar03 Bilateral Partnerships

Partnerships - Bilateral Partnerships

Partnerships

MPar04

SBC/DBC Shared Services Offer

In Progress

########

GW1 - Foundation

On Schedule

30 Partnerships MPar04 SBC/DBC Shared Services Offer

Partnerships - SBC/DBC Shared Services Offer

Partnerships

MPar05

Business Ambassadors

In Progress

########

GW1 - Foundation

On Schedule

31 Partnerships MPar05 Business Ambassadors

Partnerships - Business Ambassadors

Re: Creating a Column Multiple Criteria

Anonymous — Fri, 10 May 2024 06:17:29 GMT

Hi spandy34

Please try this:

In my previous post, I reversed the order:

= Table.AddColumn(#"Changed Type", "Custom", each 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)

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.

Re: Creating a Column Multiple Criteria

spandy34 — Fri, 10 May 2024 13:14:08 GMT

I created a new custom column and pasted in the formula you included. I

I selected Okay and then got the column below

I expanded the Table Icon deselected all the colums apart from the Custom column listed at the bottom

I selected Ok and then got the following – The values are either 0 or 90

My column has different values than yours yet I used the formula you suggested.

Re: Creating a Column Multiple Criteria

Anonymous — Mon, 13 May 2024 05:18:53 GMT

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.

Re: Creating a Column Multiple Criteria

spandy34 — Tue, 14 May 2024 17:13:43 GMT

Thats great thank you😀