Forum Discussion

wilson_smyth's avatar
wilson_smyth
Post Patron
7 years ago
Solved

concatenating values from multiple rows

I have multiple projects and multiple users.
There is a fact table linking projects to users.

I need to create a single column that contains all user names associated with a project. Im unsure how to even start at this and would appreciate some expertise.


Below is an example of my model, the data, the required output, and here is a pbix loaded with the sample data in case its useful. Thanks for any help and expertise provided.

 

Raw data & the required outputsimplified version of the model

 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi wilson_smyth ,

     

    follow these steps:

    I merged all the queries like this:

     

    Created a Calculated column

    Column = CONCATENATEX(FILTER(SUMMARIZE(DIM,DIM[DIM Project.Project Name],DIM[Username]),[DIM Project.Project Name]=EARLIER(
    DIM[DIM Project.Project Name])),[Username],",")

    Than in a table column add the fields to get the output like this:

     

     

     

    Let me know if this works for you.

     

    Thanks,

    Tejaswi

     

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    wilson_smyth 

     

    Another way could be to use CROSS FILTER

    This formula works as both Column and a MEASURE

     

    Column/Measure =
CONCATENATEX (
    CALCULATETABLE (
        VALUES ( 'dim user'[username] ),
        CROSSFILTER ( 'fact table'[userid], 'dim user'[userid], BOTH )
    ),
    [username],
    ", "
)

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi wilson_smyth ,

     

    follow these steps:

    I merged all the queries like this:

     

    Created a Calculated column

    Column = CONCATENATEX(FILTER(SUMMARIZE(DIM,DIM[DIM Project.Project Name],DIM[Username]),[DIM Project.Project Name]=EARLIER(
    DIM[DIM Project.Project Name])),[Username],",")

    Than in a table column add the fields to get the output like this:

     

     

     

    Let me know if this works for you.

     

    Thanks,

    Tejaswi

     

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

    wilson_smyth 

     

    Another way could be to use CROSS FILTER

    This formula works as both Column and a MEASURE

     

    Column/Measure =
CONCATENATEX (
    CALCULATETABLE (
        VALUES ( 'dim user'[username] ),
        CROSSFILTER ( 'fact table'[userid], 'dim user'[userid], BOTH )
    ),
    [username],
    ", "
)
  • wilson_smyth's avatar
    wilson_smyth
    Post Patron
    7 years ago

    Thanks for the replies guys, they definately helped me find the correct answer.

    In the end, I used a variation of both  Anonymous & Zubair_Muhammad solutions.

     

    I created a calculated column, but used a variable instead of using EARLIER. 

    I also used values instead of using summarize
    It works in every context i have used it so far & ive learned something from the exercise. 

    Thanks for the help all!

     

    User List = 
VAR projID = 'dim project'[projectid]

return
CONCATENATEX (
    CALCULATETABLE (
        VALUES ( 'dim user'[username] ),
        filter('fact table', 'fact table'[projectid] = projID)
        ,All('dim user')
    ),
    [username],
    ", "
) 

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi wilson_smyth ,

       

      Happy to know!

       

      If we have answered your post can you mark it as a solutions so that it would be helpful for others who have similar issue 

       

      Appreciate it!

       

      Thanks,

      Tejasiw