Forum Discussion

jasonwq's avatar
jasonwq
Icon for Helper I rankHelper I
5 years ago

How to check if values exist in another table's column?

I have these two tables

 

Item_Purchase
A 
B 

 

Item_NoEntry_TypeQTY
ASale10
APurchase10
BSale10

 

I made a new column to change the second table into this:

_PurchFlag = if(ItemLedgerEntries[Entry_Type] = "Purchase", ItemLedgerEntries[Item_No])
 
Item_NoEntry_TypeQTY_PurchFlag
ASale10 
APurchase10A
BSale10 

 

I'd like to get the first table to look like this

Item_Purchased
ATRUE
BFALSE

 

I want to ask the Table,

Which ITEMS exist in the _PurchFlag field?

and for every item that exists in the field, change it to TRUE in the other table.

Need Help

1 Reply

  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    5 years ago

    jasonwq This looks like it might run into circular reference errors at some stage. Do you have a DimItem table? If you do, use that in a table visual in Power BI and you should be able to easily combine the two tables with measures rather than columns. What else is in the first table? Is Item unique in the first table? It looks to me like _Purchase should be a MEASURE: 

     

    _Purchase = IF( COUNTROWS ( FILTER ( Table2, Table2[Entry] = "Purchase") > 1, TRUE, FALSE) 

    https://excelwithallison.blogspot.com/2020/08/its-complicated-relationships-in-power_11.html