Forum Discussion

rdstone230's avatar
rdstone230
Regular Visitor
3 years ago
Solved

Filter matched unique values

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.

  • rdstone230's avatar
    rdstone230
    3 years ago

    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]
    )

4 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    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] ) )

    • rdstone230's avatar
      rdstone230
      Regular Visitor
      3 years ago

      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!

      • tamerj1's avatar
        tamerj1
        Community Champion
        3 years ago

        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] ) )
)
  • rdstone230's avatar
    rdstone230
    Regular Visitor
    3 years ago

    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]
    )