Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Return a concatenated string value based on calculation

Greetings everyone!

I need help with the following moment:

default.jpg










There are 2 columns:
1) Say the first column is a name of product
2) Second column is a part that belongs to a certain product 

A particular part can be assigned to any product:

2.jpg


What I want: 
To create a measure, where:
        - DAX checks IF:
                  - part belongs to only one project - it returns the name of the project (i.e. 1st column's value)
                  - part belongs to a couple of projects - it returns concatenated string where project names are concatenated via comma

The result will be in the third column. Thank you!

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

@Anonymous 
Please try

=
VAR CurrentProject =
    SELECTEDVALUE ( TableName[Name] )
VAR CurrentPartTable =
    CALCULATETABLE ( TableName, ALLEXCEPT ( TableName, TableName[Part #] ) )
RETURN
    IF (
        COUNTROWS ( CurrentPartTable ) = 1,
        CurrentProject,
        CONCATENATEX ( CurrentPartTable, TableName[Name], ", ", TableName[Name], ASC )
    )

View solution in original post

10 REPLIES 10
tamerj1
Super User
Super User

@Anonymous 
Please try

=
VAR CurrentProject =
    SELECTEDVALUE ( TableName[Name] )
VAR CurrentPartTable =
    CALCULATETABLE ( TableName, ALLEXCEPT ( TableName, TableName[Part #] ) )
RETURN
    IF (
        COUNTROWS ( CurrentPartTable ) = 1,
        CurrentProject,
        CONCATENATEX ( CurrentPartTable, TableName[Name], ", ", TableName[Name], ASC )
    )
Anonymous
Not applicable

I am sorry, silly me. Your solution actually works, it was me who didn't make it work right. THANK YOU!

@Anonymous 
Happy to hear that. Thank you for marking my reply as acceptable solution

Anonymous
Not applicable

Would You explain your solution to me so I could get it right, please? Like I am 5 yo would be more preferrable...

Hi @Anonymous 
Here you go

=
VAR CurrentProject =
    -- This retreives the project name in the current filter context.
    SELECTEDVALUE ( TableName[Name] )
VAR CurrentPartTable =
    -- This removes the all filters from the table except the one coming from Part # column
    -- When iteracted with the current filter context it will leave a table composed of 
    -- all the rows that contains the Part # available in the current filter context.
    CALCULATETABLE (
        TableName,
        ALLEXCEPT ( TableName, TableName[Part #] )
    )
RETURN
    IF (
        COUNTROWS ( CurrentPartTable ) = 1,
        -- If this table contains only one row that means that the Part # available in the 
        -- current filter context is not repeated (in other words it is associated with only one project name.
        CurrentProject,
        -- Then return the project name avialable in the current filter context. ( this is only to avoid displying the delimiter)
        CONCATENATEX (
            CurrentPartTable,
            TableName[Name],
            ", ",
            TableName[Name], ASC
        ) -- Otherwise (meaning if more than one project is associated to this Part #), concatenate all the names ordered ascending 
        -- by project name seperated by ", "
    )
Anonymous
Not applicable

PreacherBaby_0-1658230474133.png

It return the list of every occurence now, but that's a good way to start?

@Anonymous 
Didn't know they are repeated. Please use

=
VAR CurrentProject =
    SELECTEDVALUE ( TableName[Name] )
VAR CurrentPartTable =
    CALCULATETABLE ( TableName, ALLEXCEPT ( TableName, TableName[Part #] ) )
VAR ProjectNames =
    DISTINCT ( SELECTCOLUMNS ( CurrentPartTable, "@Name", TableName[Name] ) )
RETURN
    IF (
        COUNTROWS ( ProjectNames ) = 1,
        CurrentProject,
        CONCATENATEX ( ProjectNames, [@Name], ", ", [@Name], ASC )
    )
tamerj1
Super User
Super User

Hi @Anonymous 
Do you want to create a measure or a calculated column? Can you share the same sample data as copy/paste?

Anonymous
Not applicable

but there are only 2 columns that are being shown on the screenshot

Anonymous
Not applicable

A measure would be more preferrable. I guess it's going to take a couple of variables to make it. Unfortunately, I can't send the data because I have no rights....

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.