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