Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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], ", " )
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], ", " )
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
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], ", " )
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
101 | |
78 | |
75 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |