Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Filter Function with "Not Contains"

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:



 

 

 

 

Need Help

10 Replies

  • ToddChitt's avatar
    ToddChitt
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      Anonymous
      Not applicable
      1 year ago

      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's avatar
        ToddChitt
        Icon for Super User rankSuper User
        1 year ago

        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.

  • ahmedoye's avatar
    ahmedoye
    Icon for Responsive Resident rankResponsive Resident
    1 year ago

    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's avatar
      Anonymous
      Not applicable
      1 year ago

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


  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      Anonymous
      Not applicable
      1 year ago

      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.