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.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Thank you for the suggestion. Yes ,there are no duplicates in table B. I have tried to create calculated column in Table A with this DAX, but the result does not give me only 10 matches insted i should have 1500 matches.

Flag = IF(LOOKUPVALUE('table B'[book_id],'table B'[book_id],table A[book_id])=BLANK(),"not match","match")
 
Can you tell me your idea of using LOOKUPVALUE FUNCTION for this scenario?
Ashish_Mathur's avatar
Ashish_Mathur
Super User
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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    LOOKUPVALUE() works as well.

    Thank you !

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      5 years ago

      You are welcome.