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

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

  • hnguy71's avatar
    hnguy71
    Super User
    4 years ago

    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's avatar
      mstone
      Helper I
      4 years ago

      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's avatar
        hnguy71
        Super User
        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