Filter Function with "Not Contains"

[Anonymous]

Hi All,
Any help would be appreciated
I already have a formula in place , I just want to add third condition to this:
First Condition : - if status =1
Second Condition : - Total time for same A values > 5 min
Third condition is(this is needed)  : - for same values of A column, values of column C does not contain string ACL or BCL  string in it

SelectColumns(

Filter(Table1, status=1 &&  Total Time>5 min && "third condition here" ),
"A", A,
"B", B,
"C", C,
'Status", Status,
"Duration", Duration)

Sample Data:

Result Table:

[Anonymous]

Hi @ahmedoye ,

You should first add column in power query like below, and then modify your formula like below:

FilteredTable = 
SELECTCOLUMNS(
    FILTER(
        Table1,
        [status] = 1 &&  
        [First Characters] > 5 && 
        NOT(
            CONTAINSSTRING([C], "ACL") || 
            CONTAINSSTRING([C], "BCL") 
        )
    ),
    "A", [A],
    "B", [B],
    "C", [C],
    "Status", [status],
    "Duration", [Time]
)

Best Regards,
Adamk Kong

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

[Anonymous]

Hi @Anonymous thanks for replying. Please see my result table. all rows where colum A = 1001, NONE of column C values contain "ACL" or "BCL". Therefore, all of A= 1001 is included. But for A = 1002, there is at least ONE value in C that contains "ACL". Therefore, all of rows where A = 1002 are excluded.
I need a condition where it considers above scenario.

[ahmedoye]

Hi, you can modify the Filter side of your formula as below:

Filter(Table1, status=1 &&  Total Time>5 min &&

(FIND("ACL", Table1[C], 1, 0) = 0 || FIND("BCL", Table1[C], 1, 0) = 0))

If this works for you, kindly mark as solution to make it easier for anyone with similar challenges find the solution.

[Anonymous]

@ahmedoye thanks for replying but I think we need to add one more condition to it saying for same "A" value 

[ToddChitt]

Yoiu will want to investigate the CONTAINSSTRING DAX function:

CONTAINSSTRING function (DAX) - DAX | Microsoft Learn

And wrap it in a NOT (  ) funciton:

Filter(Table1, status=1 &&  Total Time>5 min && NOT ( CONTAINSSTRING ( [C] "ACL" ) ),

(Not 100% sure of the above. Do your own researsh and testing, please.)

Hope this helps

[Anonymous]

Thanks @ToddChitt I tried this one already but I want the condition which says for any C values of Same "A" column value that does not contain "ACL" or "BCL"

so here I should probably use Allexcept but that's not working. 

[ToddChitt]

Sorry, I don't quite understand the comparison logic. Can you explain in plain language the relationship between A and C and what would, or would NOT, make a row acceptable or unacceptable.

[Anonymous]

So A here is ID - 

for same 1002 we have different C column Values, the idea here is if any of the C column for same value of A column has ACL or BCL it should not be in the result.

[ToddChitt]

So if I understand correctly, all rows where colum A = 1001, NONE of column C values contain "ACL" or "BCL". Therefore, all of A= 1001 is included. But for A = 1002, there is at least ONE value in C that contains "ACL". Therefore, all of rows where A = 1002 are excluded.

Did I state that correctly?

[Anonymous]

Yes you are correct.
Apologies if I couldn't explain better before.