Forum Discussion

Omelei's avatar
Omelei
Frequent Visitor
3 years ago
Solved

Get data from table

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?

Need Help
  • Samarth_18's avatar
    Samarth_18
    3 years ago

    HI Omelei ,

     

    You can create a column as below:-

     

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

     

  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

3 Replies

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    3 years ago

    HI Omelei ,

     

    You can create a column as below:-

     

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

     

    • Omelei's avatar
      Omelei
      Frequent Visitor
      3 years ago

      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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.