topic Re: Filter Table based on Condition from Other Table Column in DAX Commands and Tips

Filter Table based on Condition from Other Table Column

joshs444 — Sat, 21 Jan 2023 18:03:55 GMT

I have two tables:

Current Inventory:

Container ETA:

I would like to filter out values in "Container ETA" that are the have the same "Load Name" as "Load" from "Current Inventory" table.

So if there is a load name in "Container ETA" that is also in "Current Inventory", I would like all rows removed from "Container ETA" with the that "Load" removed.

I am not too farmiliar with FILTER functions, but it seems like this shouldnt be too difficult.

All help is appreciated!

Thanks!

Re: Filter Table based on Condition from Other Table Column

tamerj1 — Sat, 21 Jan 2023 19:04:57 GMT

joshs444

So would you like to have a new calculated table?

Table =
VAR InventoryLoads =
VALUES ( 'Current Inventory'[Load] )
RETURN
FILTER ( 'Container ETA', NOT ( 'Container ETA'[Load Name] IN InventoryLoads ) )

Re: Filter Table based on Condition from Other Table Column

joshs444 — Sun, 22 Jan 2023 17:41:44 GMT

Ideally I would filter the initial Container ETAs table, but if that doesnt work then this is a great solution.

Thanks!

Re: Filter Table based on Condition from Other Table Column

tamerj1 — Sun, 22 Jan 2023 17:58:20 GMT

joshs444
You can place the following measure in the filter pane of the visual and select "is not blank" then apply the filter

FilterMeasure = VAR InventoryLoads = VALUES ( 'Current Inventory'[Load] ) RETURN COUNTROWS ( FILTER ( 'Container ETA', NOT ( 'Container ETA'[Load Name] IN InventoryLoads ) ) )

Re: Filter Table based on Condition from Other Table Column

Anonymous — Tue, 24 Jan 2023 06:36:00 GMT

Hi joshs444 ,

If you only want to get the Load Names in the table 'Container ETA' which also exist in the table 'Current Inventory', you can create a calculated column as below in the table 'Container ETA' to get it.

Column = CALCULATE ( MAX ( 'Current Inventory'[LOAD] ), FILTER ( 'Current Inventory', 'Current Inventory'[LOAD] = EARLIER ( 'Container ETA'[Load Name] ) ) )

And if you want to remove the rows in the table 'Container ETA' which exist the same Load Name with the LOAD in the table 'Current Inventory', you can follow the below steps to get it in Power Query Editor...

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldA7EsIwDATQu7jOTLSrT5wb0FBThByD+2NBkxQYp5LG8rMkb1t5mIeUqXAmZgq15bfnS4Rxb6lBkdXPnbJP/4ExgUo9guU3QIi14OcOPeAV18CKyMA4AO+AVZhvQ05L9EStkSd0GQRWLZeIhYMAwRyGtNEOCs8OdhhJ0QFOzQ8KfEfa3w==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Load Name" = _t, #"ETA to Port" = _t, Warehouse = _t, #"Weruva SKU" = _t, Quantity = _t, #"Delivery date to H&M" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Load Name", type text}, {"ETA to Port", type date}, {"Warehouse", type text}, {"Weruva SKU", Int64.Type}, {"Quantity", Int64.Type}, {"Delivery date to H&M", type text}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Load Name"}, #"Current Inventory", {"LOAD"}, "Current Inventory", JoinKind.LeftOuter), #"Expanded Current Inventory" = Table.ExpandTableColumn(#"Merged Queries", "Current Inventory", {"LOAD"}, {"LOAD"}), #"Filtered Rows" = Table.SelectRows(#"Expanded Current Inventory", each ([LOAD] = null)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"LOAD"}) in #"Removed Columns"

Best Regards

Re: Filter Table based on Condition from Other Table Column

joshs444 — Tue, 24 Jan 2023 13:01:02 GMT

This seems like the best solution. Create a calculated column and then sort in power query to remove values in both tables.

Thanks!