Forum Discussion
Remove rows which contains entries from another Table
Hi shinney ,
You can create a calculated column as below, please find the details in the attachment.
Approved supplier = VAR _match = FIRSTNONBLANK ( FILTER ( VALUES ( 'B'[Approved Supplier List] ), SEARCH ( 'B'[Approved Supplier List], 'A'[Supplier], 1, 0 ) ), 1 ) RETURN IF ( NOT(ISBLANK(_match)), 'A'[Supplier] )In addition, you can refer the content in the following link to get it.
DAX – “CONTAINSX” Revisited: What WAS the Match?
Best Regards
You filter TableA[Supplier] with the rule that if you look through the column TableB[Approved] there must be at least one match where TableB[Approved] is a substring of TableA[Supplier].
To implement this, add a step where you filter TableA[Supplier]. This will generate a set with M code like
Table.SelectRows(#"Changed Type", each ([Supplier] = "xyz"))We want a more complex condition, so replace each (...) with a new rule to get
Table.SelectRows(#"Changed Type", (r) => List.MatchesAny(TableB[Approved], each Text.Contains(r[Supplier], _)))
There's some nested evaluation context here making the syntax a bit odd if you aren't used to M functions but it should be clear what List.MatchesAny and Text.Contains are doing.
Would there be a way to do this without changing the original Suppliers table? I would need to still use my full dataset, as the "Approved" visual is only a few of many pages in my report. Any chance there's an equivalent for List.MatchesAny and Text.Contains with a new measure or new column?
Thanks