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...
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.

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.