Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
spandy34
Responsive Resident
Responsive Resident

Creating a Column Multiple Criteria

‎05-08-2024 01:55 PM
spandy34_0-1715201714174.png

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 

Labels:
Message 1 of 9
343 Views
0
1 ACCEPTED SOLUTION
v-zhengdxu-msft
Community Support
Community Support
In response to spandy34

‎05-12-2024 10:18 PM

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.

View solution in original post

Creating a Column Multiple Criteria.pbix
Message 8 of 9
144 Views
0
8 REPLIES 8
v-zhengdxu-msft
Community Support
Community Support

‎05-08-2024 06:45 PM

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.

Message 2 of 9
295 Views
0
spandy34
Responsive Resident
Responsive Resident
In response to v-zhengdxu-msft

‎05-09-2024 12:10 AM

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.  

 

spandy34_0-1715238594637.png

 

 

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?

Message 3 of 9
285 Views
0
v-zhengdxu-msft
Community Support
Community Support
In response to spandy34

‎05-09-2024 12:14 AM

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.

Message 4 of 9
283 Views
0
spandy34
Responsive Resident
Responsive Resident
In response to v-zhengdxu-msft

‎05-09-2024 12:50 AM
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 mappedTOM Capital ReqStart Date End Date Current Gateway Status Potential Budget Reduction 24/25Potential Budget Reduction 25/26Potential Budget Reduction 26/27PID Options Appraisal RAIDL _1Project Delivery Plan Benefits Card IndexUnique LinkMission Project% Progress
TransformationMT01Waste Collection Complete18/10/202324/10/2023In Progress######## In Progress  Not Started  Not Started  In ProgressIn Progress  In Progress Yes########31/08/2026GW2 - Discovery & DesignOn Schedule£12.00£13.00£14.00Waste PIDWaste Options AppraisalWaste RAIDL.xlsx (sharepoint.com)Waste Project Plan 11 Transformation MT01 Waste CollectionTransformation - Waste Collection 
TransformationMT02Fleet Management Complete16/11/202321/11/2023In Progress######## In Progress  Not Started  Not Started  RAIDL not updatedIn Progress  In Progress  ########01/02/2025GW2 - Discovery & DesignOn Schedule   Fleet PIDNot Completed YetFleet RAIDLFleet Project Plan 22 Transformation MT02 Fleet ManagementTransformation - Fleet Management 
TransformationMT03Supporting people to live independently Complete15/02/202428/02/2024In Progress  In Progress  Not Started  Not Started  In ProgressIn Progress  In Progress  ########01/12/2024GW1 - FoundationOn Schedule   Intermediate Care PIDNot Completed YetIntermediate Care RAIDLIntermediate Care Project Plan 33 Transformation MT03 Supporting people to live independentlyTransformation - Supporting people to live independently 
TransformationMT12Fees & Charges Complete25/03/202408/04/2024In Progress  In Progress  Not Started  Not Started  RAIDL not updated   In Progress  24-Jan-2401/04/2025GW1 - FoundationOn Schedule   Fees & Charges PIDNot Completed YetNot MaintainedNot Completed Yet 1212 Transformation MT12 Fees & ChargesTransformation - Fees & Charges 
TransformationMT13Children in Our Care Complete15/02/202428/02/2024In Progress  In Progress  In Progress  Not Started  In ProgressIn ProgressIn ProgressCompleteIn Progress Yes########01/04/2026GW2 - Discovery & DesignBehind Schedule   CIOC PIDNot Completed YetCIOC RAIDLCIOC Project Plan 1313 Transformation MT13 Children in Our CareTransformation - Children in Our Care 
TransformationMT14SEND/High Needs Complete15/02/202428/02/2024In Progress  In Progress  In Progress  Not Started  In ProgressIn ProgressNot StartedIn ProgressIn Progress Yes########01/09/2024GW2 - Discovery & DesignOn Schedule   SEND/High Needs PIDSEND Options AppraisalSEND RAIDLSEND Project Plan 1414 Transformation MT14 SEND/High NeedsTransformation - SEND/High Needs 
TransformationMT15Cashiers CompleteN/AN/AComplete  In Progress  Complete  In Progress  CompleteCompleteCompleteCompleteIn Progress  ########01/09/2023GW5 - ReviewOn Schedule   Cashiers PIDCashiers Financial AppraisalN/AN/A 1515 Transformation MT15 CashiersTransformation - Cashiers 
CommunitiesMCOM01Communications and Engagement In Progress                     01-Jan-24 GW1 - FoundationOn Schedule        1616 Communities  MCOM01 Communications and EngagementCommunities  - Communications and Engagement 
CommunitiesMCOM02Community Engagement In Progress                     01-Jan-24 GW1 - FoundationOn Schedule        1717 Communities  MCOM02 Community EngagementCommunities  - Community Engagement 
CommunitiesMCOM03Community Development In Progress                     01-Jan-24 GW1 - FoundationOn Schedule        1818 Communities  MCOM03 Community DevelopmentCommunities  - Community Development 
ColleaguesMC01Organisational Culture In Progress04/04/202425/04/2024 TBCIn Progress                  01-Jan-2401/06/2025GW1 - FoundationOn Schedule   Organisational Culture PIDN/AOrg Culture RAIDLNot Completed Yet 1919 Colleagues  MC01  Organisational CultureColleagues  - Organisational Culture 
ColleaguesMC02Communication & Engagement In Progress04/04/202425/04/24 TBCIn Progress                  01-Jan-2401/06/2025GW1 - FoundationOn Schedule   Comms & Engagement PIDN/AComms & Engagement RAIDLNot Completed Yet 2020 Colleagues  MC02 Communication & EngagementColleagues  - Communication & Engagement 
ColleaguesMC03Smarter Working In Progress04/04/202425/04/24 TBCIn Progress                  01-Jan-2401/06/2025GW4 - DeliveryOn Schedule   Smarter Working PIDN/ASmarter Working RAIDLNot Completed Yet 2121 Colleagues  MC03 Smarter WorkingColleagues  - Smarter Working 
ColleaguesMC04Dunedin/Relocation In Progress04/04/202425/04/24 TBCIn Progress                  01-Jan-2401/08/2024GW4 - DeliveryOn Schedule   N/AN/ADunedin RAIDLDunedin Project Plan 2222 Colleagues  MC04 Dunedin/RelocationColleagues  - Dunedin/Relocation 
ColleaguesMC05Attract & Retain In Progress04/04/202425/04/24 TBCIn Progress                  01-Jan-2401/06/2025GW1 - FoundationBehind Schedule   Attract & Retain PIDN/AAttract & Retain RAIDLAttract & Retain Project Plan 2323 Colleagues  MC05 Attract & RetainColleagues  - Attract & Retain 
ColleaguesMC06Happy and Healthy workforce In Progress04/04/202425/04/24 TBCIn Progress                  01-Jan-2401/06/2025GW1 - FoundationOn Schedule   Happy & Healthy WorkforceN/AHappy & Healthy Workforce RAIDLNot Completed Yet 2424 Colleagues  MC06 Happy and Healthy workforceColleagues  - Happy and Healthy workforce 
ColleaguesMC07Workforce Planning In Progress04/04/202425/04/24 TBCIn Progress                  01-Jan-2401/06/2025GW1 - FoundationBehind Schedule   Workforce Planning PIDN/AWorkforce Planning RAIDLNot Completed Yet 2525 Colleagues  MC07 Workforce PlanningColleagues  - Workforce Planning 
ColleaguesMC08Workforce Development In Progress04/04/202425/04/24 TBCIn Progress                  01-Jan-2401/06/2025GW1 - FoundationBehind Schedule   Workforce Development PIDN/AWorkforce Development RAIDLNot Completed Yet 2626 Colleagues  MC08 Workforce DevelopmentColleagues  - Workforce Development 
PartnershipsMPar01Charter In Progress 08/04/2024                   ######## GW1 - FoundationOn Schedule   Partnership Mission CharterN/APartnerships Mission RAIDLNot Completed Yet 2727 Partnerships  MPar01 CharterPartnerships  - Charter 
PartnershipsMPar02Team Stockton In Progress                     ######## GW1 - FoundationOn Schedule        2828 Partnerships  MPar02 Team StocktonPartnerships  - Team Stockton 
PartnershipsMPar03Bilateral Partnerships In Progress                     ######## GW1 - FoundationOn Schedule        2929 Partnerships  MPar03 Bilateral PartnershipsPartnerships  - Bilateral Partnerships 
PartnershipsMPar04SBC/DBC Shared Services Offer In Progress                     ######## GW1 - FoundationOn Schedule        3030 Partnerships  MPar04 SBC/DBC Shared Services OfferPartnerships  - SBC/DBC Shared Services Offer 
PartnershipsMPar05Business Ambassadors In Progress                     ######## GW1 - FoundationOn Schedule        3131 Partnerships  MPar05 Business AmbassadorsPartnerships  - Business Ambassadors 
Message 5 of 9
275 Views
0
v-zhengdxu-msft
Community Support
Community Support
In response to spandy34

‎05-09-2024 11:17 PM

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)

vzhengdxumsft_0-1715321592622.png

 

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.

Message 6 of 9
235 Views
0
spandy34
Responsive Resident
Responsive Resident
In response to v-zhengdxu-msft

‎05-10-2024 06:14 AM

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

 

spandy34_1-1715346829699.png

 

 

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

 

spandy34_2-1715346829699.png

 

 

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

Message 7 of 9
218 Views
0
v-zhengdxu-msft
Community Support
Community Support
In response to spandy34

‎05-12-2024 10:18 PM

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.pbix
Message 8 of 9
145 Views
0
spandy34
Responsive Resident
Responsive Resident
In response to v-zhengdxu-msft

‎05-14-2024 10:13 AM

Thats great thank you😀

Message 9 of 9
113 Views
0

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

View All
Top Kudoed Authors
View All