Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filter table based on another table

I have two tables. For example, Table A contains correct bookings and table B contains incorrect bookings.   TABLE A book_id, price   TABLE B  book_id   I would like to create a filter in my ...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    Your LOOKUPVALUE() calculated column formula is correct.  Infact, if there are no repetitions in TableB, then a RELATED() function should also work provided you can a relationship (Many to One and Single) from TableA to TableB.  Try the RELATED() function.  Also ensure that the spellings of book id should be the same in both tables.

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    5 years ago

    Hi, Anonymous ;

    Here are two methods for your case.

    Method 1:create a measure.

    flag = IF(MAX([book_id]) in DISTINCT('TableB'[book_id]),0,1)

    then apply it into filter.

    Methed 2: create a table.

    Newtable = 
VAR _B=DISTINCT('TableB'[book_id])
return SUMMARIZE(FILTER('TableA',NOT([book_id] ) in _B),[book_id],[ price])

    The final output is shown below:

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-yalanwu-msft's avatar
v-yalanwu-msft
Community Support
5 years ago

Hi, Anonymous ;

Here are two methods for your case.

Method 1:create a measure.

flag = IF(MAX([book_id]) in DISTINCT('TableB'[book_id]),0,1)

then apply it into filter.

Methed 2: create a table.

Newtable = 
VAR _B=DISTINCT('TableB'[book_id])
return SUMMARIZE(FILTER('TableA',NOT([book_id] ) in _B),[book_id],[ price])

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thank youuu!