Forum Discussion

timward10's avatar
timward10
Helper II
1 year ago
Solved

Return multiple values

Hi, 

 

I have two tables that I am using from Salesforce objects. 

 

Table 1 = Opportunties

Table 2 = Opportuntiy products

 

Table 1 is all of the opportunties available, and Table 2 shows any products that are against the opportunties. 

 

Table two can contain more than one product per opportunity, for example 

 

Opportunity ID - 12345 - Product A

Opportunity ID - 12345 - Product B 

Opportunity ID - 12345 - Product C

 

And so on. 

 

There will also be opportunities that have no products, that I still want to display. 

 

Each table has got an Opportunity ID, so a unique identifier between the two, and I have added a relationship here. 

 

How do I return the multiple values from Table 2 into Table 1? As Table one is the master opportunity owner. 

 

Its my first time using objects, as Salesforce report imports are limited to 2k rows (which has meant I can't just pull the data through from the report due to the restrictions) so having to use objects as a work around. 

 

Any help is appreciated. 

 

Thanks

Need Help
Show and Tell
  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    1 year ago

    Hello timward10,

     

    Can you please try this approach to display all products as a single concatenated string:

    OpportunityProductsList = 
VAR Products = 
    CONCATENATEX(
        RELATEDTABLE('Opportunity Products'),
        'Opportunity Products'[Product Name],
        ", " -- Separator
    )
RETURN 
    IF(ISBLANK(Products), "No Products", Products)
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi ,

    Based on the information, try using the following DAX formula to split products column across three lines or using power query editor to transform the column.

    OpportunityProductsExpanded =
   GENERATE(
    Opportunities,
    IF(
     COUNTROWS( RELATEDTABLE('Opportunity Products') ) > 0,
      SELECTCOLUMNS(
       RELATEDTABLE('Opportunity Products'),
       "OpportunityID", [Opportunity ID],
       "Product", [Product Name]
      ),
     ROW("OpportunityID", Opportunities[Opportunity ID], "Product", "No Products")
    )
   )

    GENERATE 函数（DAX） - DAX | Microsoft Learn

     

    Best Regards,

    Wisdom Wu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    1 year ago

    Hello timward10,

     

    Can you please try this approach to display all products as a single concatenated string:

    OpportunityProductsList = 
VAR Products = 
    CONCATENATEX(
        RELATEDTABLE('Opportunity Products'),
        'Opportunity Products'[Product Name],
        ", " -- Separator
    )
RETURN 
    IF(ISBLANK(Products), "No Products", Products)
    • timward10's avatar
      timward10
      Helper II
      1 year ago

      Hi Sahir_Maharaj, 

       

      Thank you, that formula works great! 

       

      Is there a way I can split that out? 

       

      For example, one opportuntiy that has three products all shows on one line. Could I split that out across three lines? So instead of the oppotunity count being one it would be three? 

       

      Thanks again! 

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi ,

        Based on the information, try using the following DAX formula to split products column across three lines or using power query editor to transform the column.

        OpportunityProductsExpanded =
   GENERATE(
    Opportunities,
    IF(
     COUNTROWS( RELATEDTABLE('Opportunity Products') ) > 0,
      SELECTCOLUMNS(
       RELATEDTABLE('Opportunity Products'),
       "OpportunityID", [Opportunity ID],
       "Product", [Product Name]
      ),
     ROW("OpportunityID", Opportunities[Opportunity ID], "Product", "No Products")
    )
   )

        GENERATE 函数（DAX） - DAX | Microsoft Learn

         

        Best Regards,

        Wisdom Wu

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.