Return a concatenated string value based on calculation

[Anonymous]

Greetings everyone!

I need help with the following moment:

 








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:

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!

[tamerj1 · Accepted solution]

@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]

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

[tamerj1]

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

[Anonymous]

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

[tamerj1]

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]

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

[tamerj1]

@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]

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

[Anonymous]

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

[Anonymous]

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....