topic Re: Filter matched unique values in DAX Commands and Tips

Filter matched unique values

rdstone230 — Fri, 30 Jun 2023 12:08:46 GMT

Hi - I have two tables where both tables contain a column with a unique ID number. Table 1 will contain a single or mutiple value of a unique ID number while table 2 will contain multiple values of the unique ID number. I would like to filter out all the unique ID numbers found in table 1 from table 2. How would I perform this step using a DAX command? Thank you.

Re: Filter matched unique values

tamerj1 — Fri, 30 Jun 2023 12:58:33 GMT

Hi rdstone230
Not sure what you are up to but you can try this filter

FILTER ( Table2, Table2[Truck ID] IN VALUES ( Table1[Truck ID] ) )

Re: Filter matched unique values

rdstone230 — Fri, 30 Jun 2023 13:57:05 GMT

Hi tamerj1

I am a novice analyst for a logistics company and I want to know how many of our trucks have delivered on time and to do so, I need to subrtact out trucks that have been rescheduled from trucks that have not. So my data is represented like this:

-Table 1 represents trucks that have been rescheduled for a variety of reasons. Each row of data represents a single truck that has been rescheduled. If two rows of the same Truck ID appears, then that truck has been rescheduled two times.

-Table 2 lists ALL and every truck and each row of the same Truck ID represents a single delivery point. If there are five rows of the same Truck ID then the truck has five different delivery points.

I want to subtract the Truck ID's in table 1 from those in table 2 so that I'm left with only the columns that represent Trucks minus Rescheduled Trucks.

BTW, I ran your solution and received the following error. I do have two columns in table 2 with the truck ID, but they aren't identical:

Thank you for your help with this!

Re: Filter matched unique values

tamerj1 — Fri, 30 Jun 2023 14:34:30 GMT

Thank you rdstone230

The information that you have provided is not sufficient to produce a concrete solution. I can understand the subtraction shall be curried out for each stop dependently. The data model/relationships must be clear, the data structure of each table must be clear and the nature of the report that clarifies the context of evaluation must be clear. I can suggest the following but I can also guess that probably it won't provide correct results within the evaluation context of your report.

CountMeasure = COUNTROWS ( EXCEPT ( VALUES ( Table2[Truck ID] ), VALUES ( Table1[Truck ID] ) ) )

Re: Filter matched unique values

rdstone230 — Fri, 30 Jun 2023 16:40:24 GMT

Thank you tamerj1

My apologies for the insufficent information. It speaks to my inexperience.

It took time, but I learned to use a 'LOOKUP' with the help of resources found at Power BI documentation - Power BI | Microsoft Learn I then filtered on only the 'blank' matches resulting in a solution. I really appreciate your time and, thank you again.

Rescheduled Shipments =

LOOKUPVALUE (

'Table1' [Truck ID], 'Table1 '[Truck ID], 'Table2 [Truck ID]

)