concatenating values from multiple rows

[wilson_smyth]

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

[Anonymous · Accepted solution]

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

[wilson_smyth]

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]

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

[Zubair_Muhammad · Accepted solution]

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

[Anonymous · Accepted solution]

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