Using CONCATENATEX to combine multiple rows by project ID

[mstone]

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 approach I can take to the DAX formula?  Can I do this with a measure called "Combined Risks".  My goal is to have one row per project in the final report.

Mike

[Fowmy]

@mstone 

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

[mstone]

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

[Ashish_Mathur]

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.

[hnguy71]

hi @mstone ,

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

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

[mstone]

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]

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

[hnguy71]

Hi @mstone ,

that's interesting, it should group them out on it's own. Try this instead:

Risks := CALCULATE(CONCATENATEX(Risks, Risks[Risks], UNICHAR(10)), ALLEXCEPT(Risks, Risks[ProjectID]))

Btw, ProjectID and Risks both needs to be in the same visual for this to work.

[mstone]

Just tried that and received the following error message:  

They are in the same visual.  Perhaps I have made a sytax error.  I may not see your reply until tomorrow morning.  I do appreciate your help and patience!  

[hnguy71 · Accepted solution]

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

[mstone]

Confirmed that the second formula is working perfectly.  Thank you so much!!

[mstone]

I tried both.  The first seemed to produce duplicate entries but the second appeared to work perfectly.  Due to a save issue with the file I had to set up the simple table again and it seems to be functioning differently than before.  Just troubleshooting the difference but I think the second formula may have been the solution.  🙂  If I can verify that, I will definitely click "Accept as a Solution".  Thank you for your help!

[mstone]

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!