Return multiple values

[timward10]

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

[Sahir_Maharaj · Accepted solution]

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)

[Sahir_Maharaj · Accepted solution]

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]

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 · Accepted solution]

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.