Use table rows as a multiple filter criteria

Hello M Experts,

I hope someone could help me to resolve the following issue.

I have a table, where I would like to exclude rows (or flag for exclusion) based on the criteria defined in another table. Different combination of columns can be used as criteria. The original table, table with criteria and the output table look like this:

By looking at the community posts, I have found a function from ImkeF (posted here: Merging queries based on multiple conditions ), however it uses each of the values as a separate filter, and then does an Outer Join on the tables. Can someone help to transform the function to consider multiple criteria, as well as wildcards/patterns?

It is important to resolve this via M Query, and not DAX Calculated Table, as I'm planning to use the table in other queries.

Need Help

Anonymous

5 years ago

a more compact version, in case filter and tabel to be filtered have teh same fields:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8stMTy3KTFTSUfJzNzQEUqHBrkEKjkCGc2JxqoKhUqxOtJJbUWJecipQzC3I0ACmxgmmxghDjRFMjTNuNXBzXGBqjMFqQoMdwTKGpjAFbjAFJmAF7hmJeSD3unsYmIGUFKcWKbijutc9Nb8oHewpd1cjA5giD7giAzyqPJFVxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Market = _t, Company = _t, User = _t, Case = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Market", type text}, {"Company", type text}, {"User", type text}, {"Case", type text}}),
    match=(tab, filter)=>
    let 
    res=  filter="*" or tab=filter or (  
    let 
    betStars= Text.BetweenDelimiters(filter,"*","*"),
    partOf= if Text.Length(betStars)>0 then  Text.Contains(tab,betStars) else false,
    startWith= if (Text.Start(filter,1)<>"*" and Text.End(filter,1)="*") then Text.StartsWith(tab, Text.BeforeDelimiter(filter,"*")) else false
    in partOf or startWith
    )
    in res,  

    #"Added Custom" = Table.AddColumn(#"Changed Type", "to be filtered out", each Table.Contains(Filter, _, (x,y)=> 
    List.Accumulate(Record.FieldNames(x),true, (s,c)=> s and match(Record.Field(y,c),Record.Field(x,c))) ))   

in
    #"Added Custom"

7 Replies

Anonymous
Not applicable
5 years ago
I could try to do some think close to what you ask.
But ... I need some tables (in a copiable format, may be a linked excel file) with as many cases as possible you want to manage.
explain better what you mean by matching pattern and wild card

Anonymous

Not applicable

5 years ago

Waiting for your exhaustive explanations, I can try to guess the expected result.

This is an attempt

Filter table

Filtered table:

Anonymous

Not applicable

5 years ago

second attempt (which generalizes the control function to all filter tables.
There is no need to create it from time to time)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WylPSUcozBBKJQJxsqBSrE62UBmSmgcSSQGJGCDEjEB9NzBhIpIDEjMFipUBmKUgvSDLZBCEG0pvkWAISNUUTTQSLminFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Market = _t, Company = _t, User = _t, Case = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Market", type text}, {"Company", type text}, {"User", type text}, {"Case", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "to be filtered out", each Table.Contains(Filter, _, (x,y)=> if   
        (y[Market]=x[Market] or x[Market]="any")  and
        (y[Company]=x[Company] or x[Company]="any") and
        (y[User]=x[User] or x[User]="any") and
        (y[Case]=x[Case] or x[Case]="any") 
        then true else false))
in
    #"Added Custom"

To take a further step towards a more extensive generalization, we can replace strict equality with a function that takes into account wildcards and patterns

Anonymous

Not applicable

5 years ago

Thank you, Anonymous

Your solution is close to what I'm looking for, however I would like to provide some examples on the usage of wildcards/patterns, which would allow to ignore certain filter values:

Original Table

Market	Company	User	Case
Nigeria	NG11	USER A	Case 1
France	FR10	USER B	Case 2
France	FR12	USER C	Case 2
France	FR10	USER D	Case 3
USA	US15	USER F	Case 4
Ghana	GH06	User G	Case 1
Georgia	GE20	User H	Case 10
Georgia	GE20	User I	Case 10

Filter Criteria

Market	Company	User	Case	Comment
Nigeria	*	*	*	All values where market = "Nigeria"
France	*	*	Case 2	All values where market is "France" and Case is "Case 2"
*	*	USER F	*	All values where User is "USER F"
*	*	A	*	All values where User contains "A"
*	G*	*	Case 1	All values where Company starts with "G" and Case is "Case 1"

Output

Market	Company	User	Case	Exclude
Nigeria	NG11	USER A	Case 1	Yes
France	FR10	USER B	Case 2	Yes
France	FR12	USER C	Case 2	Yes
France	FR10	USER D	Case 3
USA	US15	USER F	Case 4	Yes
Ghana	GH06	User G	Case 1	Yes
Georgia	GE20	User H	Case 10
Georgia	GE20	User I	Case 10

Forum Discussion

Use table rows as a multiple filter criteria

7 Replies

Related content

DAX FILTER with multiple criteria

Multiple Criteria Filter

Dynamic Filter creation based on multiple criteria

Sequential Order column based on multiple criteria

DAX filter with multiple criteria