Forum Discussion

romovaro's avatar
romovaro
Responsive Resident
3 years ago
Solved

CONCATENATE different rows

HI Pbix community and Happy Friday,

 

I need help with a Formula. I have a customer table and every customer has small entitites based in different countries with different CUID number.

 

CID Client Name Country CUID
011050 Client A Germany 025679
011050 Client A Great Britain 025682
011050 Client A Hong Kong 025686
011050 Client A Indonesia 034152
011050 Client A Indonesia 057145
011050 Client A India 025687
011050 Client A India 382279
011051 Client B Singapore 025723
011052 Client C United Arab Emirates 025727
011052 Client C China 025728
011052 Client C Egypt 025729

 

I currently show the Count of Countries and Count of CUIDs included in the Client Name but I have been asked to provide in detail de Countries and CUIDS

 

 

In that case, add an extra column with "Countries Included" and another one with "CUIDs included"

Example (From the first table),

Client A  should show

Countries included Germany, GB, Hong Kong, Indonesia & India 

Cuids included: 025679, 025682, 025686, 034152, 057145, 025687 & 382279

 

Client B  should show

Countries included Singapore 

Cuids included: 025723

 

I currently have a formula that provides the result I need but all the Services are in one row. In the Country and CUID case I have some issues with data in different rows.

 

Distinct Services =
CONCATENATEX(
FILTER(
SUMMARIZE('Sales File','Sales File'[Client_Name],'Sales File'[ServiceName]),'Sales File'[Client_Name]=EARLIER('Sales File'[Client_Name])
),
'Sales File'[ServiceName],
" & "
)
 
Thanks for your help,
 
Need Help
  • romovaro's avatar
    romovaro
    3 years ago

    HI jgeddes,

     

    NO worries. Tried again with the formulas and now is working. 

    Thanks,

     
    Distinct Countries =
    CONCATENATEX(
    FILTER(
    SUMMARIZE('Weekly Slippage','Weekly Slippage'[Client Name],'Weekly Slippage'[Country]),'Weekly Slippage'[Client Name]=EARLIER('Weekly Slippage'[Client Name])
    ),
    'Weekly Slippage'[Country],
    " & "
    )
     
     
    Distinct CUIDs =
    CONCATENATEX(
    FILTER(
    SUMMARIZE('Weekly Slippage','Weekly Slippage'[Client Name],'Weekly Slippage'[CUID]),'Weekly Slippage'[Client Name]=EARLIER('Weekly Slippage'[Client Name])
    ),
    'Weekly Slippage'[CUID],
    " & "
    )
     

     

     

4 Replies

  • jgeddes's avatar
    jgeddes
    Super User
    3 years ago

    Good Morning,

    Using your formula I end up with

    I am not sure that I understand where your issue is occuring. Can you elaborate as to what issue(s) you are seeing?

     

    • romovaro's avatar
      romovaro
      Responsive Resident
      3 years ago

      HI Jgeddes

       

      I was just trying to provide an example of what I am looking for. The last formula is one you gave to me and it works perfectly. The concatenate formula is proving with all the services (all the services are in one row).

       

      My problem now is that CUIDs and Countries are in different rows and the formula is not working.

      You can forget about the last part with the "concatenate formula". it was just an example

       

      Thnaks

  • jgeddes's avatar
    jgeddes
    Super User
    3 years ago

    Can you show me what you mean by the countries and CUIDs are in different rows?

    • romovaro's avatar
      romovaro
      Responsive Resident
      3 years ago

      HI jgeddes,

       

      NO worries. Tried again with the formulas and now is working. 

      Thanks,

       
      Distinct Countries =
      CONCATENATEX(
      FILTER(
      SUMMARIZE('Weekly Slippage','Weekly Slippage'[Client Name],'Weekly Slippage'[Country]),'Weekly Slippage'[Client Name]=EARLIER('Weekly Slippage'[Client Name])
      ),
      'Weekly Slippage'[Country],
      " & "
      )
       
       
      Distinct CUIDs =
      CONCATENATEX(
      FILTER(
      SUMMARIZE('Weekly Slippage','Weekly Slippage'[Client Name],'Weekly Slippage'[CUID]),'Weekly Slippage'[Client Name]=EARLIER('Weekly Slippage'[Client Name])
      ),
      'Weekly Slippage'[CUID],
      " & "
      )