Forum Discussion

mstone's avatar
mstone
Helper I
4 years ago
Solved

Using CONCATENATEX to combine multiple rows by project ID

I have a list of projects and for each project there can be more than one risk.  My goal is to combine the risks by project rather than combine all of the risks into a single entry.  Is there a basic...
Need Help
  • hnguy71's avatar
    hnguy71
    4 years ago

    Hey mstone , 

    Can you try to copy and paste one of the following expressions?

     

    Combined column = CALCULATE(CONCATENATEX(DIM_PROJECT_RISK, [RiskTitle], UNICHAR(10)), ALLEXCEPT(DIM_PROJECT_RISK, DIM_PROJECT_RISK[ProjectId]))

     

    AsMeasure = CALCULATE(CONCATENATEX(VALUES(DIM_PROJECT_RISK[RiskTitle]), [RiskTitle], UNICHAR(10)), ALLEXCEPT(DIM_PROJECT_RISK, DIM_PROJECT_RISK[ProjectId]))

     

    Here is a link you can download to a sample: https://1drv.ms/u/s!An8CCFsOzw0uhQYExSCu5Hj9NDon?e=7B3enE

Fowmy's avatar
Fowmy
Super User
4 years ago

mstone 

Can you paste here a screenshot of dummy data that represent your data table?


mstone's avatar
mstone
Helper I
4 years ago

Sorry for the delay.  Here is an example of what it looks like now and how I would like it to look:  

ProjectID

Risks

PRJ6319

Risk 1

PRJ6320

Risk 1

PRJ6320

Risk 2

PRJ6320

Risk 3

PRJ6321

Risk1

PRJ6321

Risk 2

 

 

 

 

ProjectID

Risks

 

PRJ6319

Risk 1

 

PRJ6320

Risk 1

Risk 2

Risk 3

 

PRJ6321

Risk 1

Risk 2

 

  • mstone's avatar
    mstone
    Helper I
    4 years ago

    The tables didn't paste as desired but the idea is to combine the risks in a single cell so the result is a single project number entry.  Thank you!

  • hnguy71's avatar
    hnguy71
    Super User
    4 years ago

    hi mstone ,

     

    You can use the CONCATENATEX function. Please see the measure below with expected output:

    Risks := CONCATENATEX(Risks, Risks[Risks], UNICHAR(10))

     

     

    • mstone's avatar
      mstone
      Helper I
      4 years ago

      Thank you, hnguy71!  Will that parse them by projectID?  The orginal data is not in sequence.  It consists of different risk statements.  I'll give it a try.

       

      • mstone's avatar
        mstone
        Helper I
        4 years ago

        Using that syntax, I ended up with all entries from all projects combined in one list rather than parsed by project.  Is there something I can do to modify the formula?

        Combined column = CONCATENATEX(DIM_PROJECT_RISK,DIM_PROJECT_RISK[RiskTitle],unichar(10))

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    Hi,

    Try this measure

    All risks = CONCATENATEX(VALUES(Data[Risks]),Data[Risks],",")

    Drag Project ID and the All Risks measure to the Table visual.

    Hope this helps.