Urgent! please help. This multi-condition DAX formula is not working correctly

[Anonymous]

I am trying to do the following:  I want all associated "AcctNumbers" to  SAY "REMOVE", if the columns 4A, 4B, 5A and 5B, ALL read "Complete" in any of the rows associated with that "Acc Number" group. If any of those rows at all, but any of those rows associated with that "AcctNumber", then it should say "REMOVE" ELSE "KEEP"  for example below, this should say "KEEP" because NOT all the the columns associated in the group of AcctNumbers; 4A, 4B, 5A and 5B DO NOT say Complete

and...this should say "REMOVE" because all for categorys of 4A, 4B, 5A and 5B, have "COMPLETE" in the AcctNumber field in the group

Any assistance would be so appreciated!

[talespin]

Hi @Anonymous ,

One way to accomplish this is below.

I am considering both Number and Machine ID, Final Status is for this combination, you can remove Machine ID from measure, if you want status by Number only. Please replace tables/columns in measure with yours.

Measure

Final Status =

VAR _Status4A = CALCULATE( MIN(Machine[4AStatus]), REMOVEFILTERS(Machine), SUMMARIZE(Machine, Machine[Number], Machine[MachineID]))

VAR _Status4B = CALCULATE( MIN(Machine[4BStatus]), REMOVEFILTERS(Machine), SUMMARIZE(Machine, Machine[Number], Machine[MachineID]))

VAR _Status5A = CALCULATE( MIN(Machine[5AStatus]), REMOVEFILTERS(Machine), SUMMARIZE(Machine, Machine[Number], Machine[MachineID]))

VAR _Status5B = CALCULATE( MIN(Machine[5BStatus]), REMOVEFILTERS(Machine), SUMMARIZE(Machine, Machine[Number], Machine[MachineID]))

RETURN IF(_Status4A = "Complete" && _Status4B = "Complete" && _Status5A = "Complete" && _Status5B = "Complete", "REMOVE", "KEEP")

[lbendlin]

This is a public forum where users help users.  Try to avoid using words like "urgent".

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Anonymous]

Received.  Thank you for the tip.