cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JP_Caterpillar
New Member

True/False based on Multiple Column Conditions

Good afternoon all, 

 

Was hoping for some assistance. I am currently working on a Power Bi project where I need to specify whether an action is past due based on if any of the other steps are past due. There are 3 steps ICA, RCCA, and PCA for one document number. That can show past due "Not Complete" and "Past Due". Filtering does not seem to calculate correctly. 

 

I have tried a few suggestions that I have found and nothing seems to be working. This is what I am working with right now. 

 

NCR Past Due = Calculate(True(),('Audit Findings Data','Audit Findings Data'[ICA Completed]= "Complete",'Audit Findings Data'[ICA PAST DUE]= "Past Due",'Audit Findings Data'[RCCA Completed]="Complete",'Audit Findings Data'[RCCA Past Due]= "Past Due",'Audit Findings Data'[PCA Submitted ?]= "Complete", 'Audit Findings Data'[PCA Submission PAST DUE]= "Past Due")
 
 

2021-02-19_12-08-29.png

 
1 ACCEPTED SOLUTION
v-henryk-mstf
Community Support
Community Support

Hi @JP_Caterpillar ,

You can add a new column in the power query, try the following formula: 

= Table.AddColumn(#"Replaced Value", "Custom1", each if [ICA Completed] = "Complete" and [ICA PAST DUE] = "Pass Due" and [RCCA Completed] = "Complete" and [RCCA PAST DUE] = "Pass Due" and [PCA Submitted] = "Complete" and [PAC SUBMISSION PAST DUE] = "Pass Due" then "Ture" else "False")

 

Capture.PNG


If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.


Best Regards,
Henry


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

4 REPLIES 4
v-henryk-mstf
Community Support
Community Support

Hi @JP_Caterpillar ,

You can add a new column in the power query, try the following formula: 

= Table.AddColumn(#"Replaced Value", "Custom1", each if [ICA Completed] = "Complete" and [ICA PAST DUE] = "Pass Due" and [RCCA Completed] = "Complete" and [RCCA PAST DUE] = "Pass Due" and [PCA Submitted] = "Complete" and [PAC SUBMISSION PAST DUE] = "Pass Due" then "Ture" else "False")

 

Capture.PNG


If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.


Best Regards,
Henry


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

Thank you Henry, I did not get a chance to try this but it does seem to work. We actually might have achieved the same goal just more complicated by creating 3 new columns and collecting the columns if they had value to get the count using the the Switch to function to get the card to populate the number based off the 6 conditions being met.  I am going to mark this as Accept as Solution because it seems like a lot less steps to get the job done. Appreciate your Help! 

 

2021-02-26_14-37-28.png

 

Then the switch to

 

2021-02-26_14-39-30.png

 

Then I was able to perform a count on the ones that were past due. 

 

Hi @JP_Caterpillar ,

 

Very honored to be able to help you.😊

 

Best Regards,

Henry

Vera_33
Solution Sage
Solution Sage

Hi @JP_Caterpillar 

 

So it is a calculated column with OR logic? Are you trying to do it?

NCR Past Due =
'Audit Findings Data'[ICA Completed] = "Complete"
    || 'Audit Findings Data'[ICA PAST DUE] = "Past Due"
    || 'Audit Findings Data'[RCCA Completed] = "Complete"
    || 'Audit Findings Data'[RCCA Past Due] = "Past Due"
    || 'Audit Findings Data'[PCA Submitted ?] = "Complete"
    || 'Audit Findings Data'[PCA Submission PAST DUE] = "Past Due"

 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors