Forum Discussion

timward10's avatar
timward10
Icon for Helper II rankHelper 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...
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.

timward10's avatar
timward10
Icon for Helper II rankHelper 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.