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 ho...
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],
    ", "
)
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