Lookup in another table and then net results if applicable

[Hayashi123]

Hi. New to Power BI and would appreciate any advice and guidance on this.

I am trying to lookup the Buy User Ids in Table 1 in Table 2 and if the Buy User ID is in Table 1 matches the Sell User ID in Table 2 then net the Amounts and gives the "Exp Results" in Table 1.

[Dangar332 · Accepted solution]

HI, @Hayashi123 

your information is not appropriate but try below

first make sure sellerid and user id tables is connected with eachother
and use below code for new column

Column = 
var a = LOOKUPVALUE(seller[amount],seller[sellerid],user[userid])
return
user[amount]-a

[Dangar332 · Accepted solution]

HI, @Hayashi123 

your information is not appropriate but try below

first make sure sellerid and user id tables is connected with eachother
and use below code for new column

Column = 
var a = LOOKUPVALUE(seller[amount],seller[sellerid],user[userid])
return
user[amount]-a

[Hayashi123]

Thank you for your reply.  It worked however, I wanted to do a sum lookup.  So in the Seller table there could be multiple rows with a Seller User ID and varying amounts.  

[Anonymous · Accepted solution]

HI @Hayashi123,

Perhaps you can try to use the IN operator to compare the selected value and calculate with current values.
Measure formula sample :

formula =
VAR currAmount =
    SUM ( User[amount] )
VAR selected =
    CALCULATE (
        SUM ( Seller[Amount] ),
        FILTER ( ALLSELECTED ( Seller ), [User ID] IN VALUES ( User[User ID] ) )
    )
RETURN
    currAmount - selected

The IN operator in DAX - SQLBI

Regards,

Xiaoxin Sheng

[StrategicSavvy]

hi @Hayashi123 

I think you need provide more context because I cannot understand the logic of expected results. But if you would like to lookup you will have 2 options:

1. Create one to one relationship between buer and seller - my assumtions is they have 1-1 relationship based on your case. Then you can create either create measure that will calculate lookup value or create column in your table A with RELATED formula

2. Use merge operation in Power Query to join two datasets and name new column as Exp Results.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: www.linkedin.com/in/lukasz-kozdron