Get data from table

[Omelei]

Hi guys, 

Hope you can help me out. 

I have 2 tables

Productconfig

Opportunity

Connection

Now, I'd like to add an additional column in Productconfig Table with the Quantity of the Product from the Previous Opportunity. 

Any idea?

[Samarth_18 · Accepted solution]

HI @Omelei ,

You can create a column as below:-

Column =
LOOKUPVALUE (
    Opportunity[prev opp id],
    Opportunity[Opp id], OpportunityConfig[opportunity id]
)

[Anonymous · Accepted solution]

Hi @Omelei ,

Please try to create a new column with below dax formula:

Column =
VAR cur_oid = [Opportunity ID]
VAR o_id =
    CALCULATE (
        MAX ( Opportunity[Previous Opportunity ID] ),
        Opportunity[Opportunity ID] = cur_oid
    )
VAR cur_pid = [Product ID]
VAR _val =
    CALCULATE (
        MAX ( Productconfig[Quantity] ),
        FILTER (
            ALL ( Productconfig ),
            [Product ID] = cur_pid
                && [Opportunity ID] = o_id
        )
    )
RETURN
    _val

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Samarth_18 · Accepted solution]

HI @Omelei ,

You can create a column as below:-

Column =
LOOKUPVALUE (
    Opportunity[prev opp id],
    Opportunity[Opp id], OpportunityConfig[opportunity id]
)

[Omelei]

Thank you for your reply @Samarth_18 . There can be multiple products in 1 opportunity. 

I made a start now with the concatenate function:

opportunityid + productid

previous opportunityid + product id

Now I need to add another column with the quantity of the product in the previous opportunity. I tried to use the following:

Column = LOOKUPVALUE(Quantity,Merge Opportunity + ProductID,Merge Previous Opportunity + ProductID)

However, LOOKUPVALUE does not work with expressions.

Any thoughts?