Forum Discussion
Concatenatex with multiple criteria
Hey folks, I need some help with concatenatex - any help is greatly appreciated.
I have to concatenate the rows from the column "Completed At - Clusters" by all 3 other columns. Ultimatelly, I need the following outcome:
Original:
| User Id | Year | Month | Completed At - Clusters |
| 36059 | 2019 | 11 | 1 |
| 36474 | 2019 | 5 | 2 |
| 36474 | 2019 | 5 | 3 |
| 36474 | 2019 | 5 | 4 |
| 36474 | 2019 | 6 | 1 |
| 36474 | 2019 | 6 | 2 |
| 36474 | 2019 | 6 | 3 |
| 36474 | 2019 | 6 | 4 |
Goal:
| User Id | Year | Month | Completed At - Clusters |
| 36059 | 2019 | 11 | 1 |
| 36474 | 2019 | 5 | 2,3,4 |
| 36474 | 2019 | 6 | 1,2,3,4 |
use
Combined Column = VAR user_id = completed[User Id]VAR month_number = completed[Month]VAR completed_string = calculate(CONCATENATEX(completed,completed[Completed At - Clusters],","),ALL(completed),completed[User Id] = user_id, completed[Month] = month_number)RETURN completed_stringthe difference is that since a calculated column has a built in row context you no longer need to use "max" to get the user_id and month_number but can just refer to them directly
4 Replies
- kentyler
Solution Sage
Here are my results
I used a measure for this
Combined =
VAR user_id =
MAX ( completed[User Id] )
VAR month_number =
MAX ( completed[Month] )
VAR completed_string =
CALCULATE (
CONCATENATEX ( completed, completed[Completed At - Clusters], "," ),
ALL ( completed ),
completed[User Id] = user_id,
completed[Month] = month_number
)
RETURN
completed_string
I used VARIABLES to pull out the user id and month for each row. Then I used CONTATENATEX inside of CALCULATE. CALCULATE let me put filters on the completed table, first I applied ALL() so i would start with the whole table and not just the current row, then i used my variables to filter for month and user idThis is a sample use of VAR as described here https://bislogans.com/6-use-simple-tricks/
Hi kentyler ,
Thank you for the help and thorough explanation. This table, however, will serve as source to other tables and I really need it as a column. How can I convert this measure into a column?
- kentyler
use
Combined Column = VAR user_id = completed[User Id]VAR month_number = completed[Month]VAR completed_string = calculate(CONCATENATEX(completed,completed[Completed At - Clusters],","),ALL(completed),completed[User Id] = user_id, completed[Month] = month_number)RETURN completed_stringthe difference is that since a calculated column has a built in row context you no longer need to use "max" to get the user_id and month_number but can just refer to them directly