Attempting to filter a table based on values present in another table's column

[TheHawkmeiser]

Hello,

I will provide  simplified example of what I am trying to do. The main goal is to filter table 1 based on table 2 Loc ID values present in table 1. I am still learning BI and may be missing something obvious, but my approach was to generate a new table from table 1 using table 2 loc ID as a filter expression, but it seems you cannot use a table object for this purpose. Table 1 is a relatively large set with more than 10 million rows, Table 2 is smaller(~3000) and has different fields but they share the Loc column.

Table 1

Loc              Cat

1045            A

4555            A

6647            B

9996            C

3325            D

Table 2

Loc

6647    

9996

5555

3333

0009

Expected Outcome

Loc              Cat

6647            B

9996            C

[Ashish_Mathur]

Hi,

Assuming there are no duplicates in the Loc column of Table 2, create a relationship (Many to One and Single) from Table 1 to Table 2.  To your visual, drag Loc from Table 2.  Write this measure

Measure = max('Table 1'[Cat])

Hope this helps.

[Anonymous]

Try this: 

Apply this relationship:

Next, create a table in PBI and add "Loc" and "Cat" as the columns in the table.

After, select a slicer and add "LOC" as the filter for the slicer.