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
Icon for Community Support rankCommunity Support
5 years ago

Hi, Anonymous ;

Is the above answer helpful to you? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

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.