Forum Discussion
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 report page that will exclude/include all book_id-s from table B(incorrect bookings).
For example, my idea to solve this is to create a calculated column in table A and insert it in the filtering pane so the user can switch between all bookings(table A) and booking from table A without bookings from B(correct-incorrect).
But this logic does not work with DAX, I have tried to solve this with RELATED function but the function does not work for my case, when I type related Power Bi does not recognise any other table or column. I do not know why (my tables in the data model are related 1:m).
Any advice or help on this will be much appreciated.
Thank you.
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.
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.
8 Replies
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.Hi,
If in the book_id of TableB, there are no duplicate entries, then you should be able to write the LOOKUPVALUE() function in TableA.
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?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.
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.