Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
I have Projects which are deployed with assets and I need to record for each project which assets have been sent to site. Sometimes the Project and Asset are repeated. I need to concatenate the assets and add to that list as long as the project is the same and the asset is new - the 3rd column below is what I am trying to achieve.
I can only do this calcualtion in the table view, not in the Query editor since this is a new table created to obain the first 2 columns of projects and assets.
Tried lots of things and going nowere. Any help would be greatly apprreciated.
Solved! Go to Solution.
Hi @dguibert ,
You can achieve this using Power BI's DAX with a calculated column and a bit of row context logic. Since you're working in the table view and not in Power Query, here's a way to do it:
Try adding a calculated column like this:
Result Desired = VAR CurrentProject = [PROJECT] VAR CurrentAsset = [ASSET] RETURN CALCULATE( CONCATENATEX( FILTER( 'YourTable', 'YourTable'[PROJECT] = CurrentProject && 'YourTable'[Index] <= EARLIER('YourTable'[Index]) ), 'YourTable'[ASSET], ", " ) )
Important: You’ll need an Index column to preserve row order. You can add it in Power BI using "Add Index Column" in Power Query before loading the data.
This will concatenate the assets for each project up to the current row, giving you the cumulative list you're looking for.
Let me know if you need help adapting this to your exact table name or structure.
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.
If you cannot access the query editor at all, either to do this in M or to add an index, you can try something like this as a calculated column...
Result =
var _project =
[PROJECT]
var _vTable =
SUMMARIZE(
assetTable,
assetTable[PROJECT],
"__value", CONCATENATEX(DISTINCT(assetTable[ASSET]), [ASSET], ", ")
)
RETURN
MINX(
FILTER(_vTable, [PROJECT] = _project),
[__value]
)
It should look like this...
Proud to be a Super User! | |
If you cannot access the query editor at all, either to do this in M or to add an index, you can try something like this as a calculated column...
Result =
var _project =
[PROJECT]
var _vTable =
SUMMARIZE(
assetTable,
assetTable[PROJECT],
"__value", CONCATENATEX(DISTINCT(assetTable[ASSET]), [ASSET], ", ")
)
RETURN
MINX(
FILTER(_vTable, [PROJECT] = _project),
[__value]
)
It should look like this...
Proud to be a Super User! | |
Whoa! Solved it right away after 3 hours of trying to find out on my own. You saved my day, Thanks!
Power BI aggregates by default, so your desired result is not achievable unless you add an index column that makes each data row unique.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
Hi @dguibert ,
You can achieve this using Power BI's DAX with a calculated column and a bit of row context logic. Since you're working in the table view and not in Power Query, here's a way to do it:
Try adding a calculated column like this:
Result Desired = VAR CurrentProject = [PROJECT] VAR CurrentAsset = [ASSET] RETURN CALCULATE( CONCATENATEX( FILTER( 'YourTable', 'YourTable'[PROJECT] = CurrentProject && 'YourTable'[Index] <= EARLIER('YourTable'[Index]) ), 'YourTable'[ASSET], ", " ) )
Important: You’ll need an Index column to preserve row order. You can add it in Power BI using "Add Index Column" in Power Query before loading the data.
This will concatenate the assets for each project up to the current row, giving you the cumulative list you're looking for.
Let me know if you need help adapting this to your exact table name or structure.
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
72 | |
71 | |
37 | |
31 | |
27 |
User | Count |
---|---|
91 | |
49 | |
45 | |
38 | |
36 |