Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Return a concatenated string value based on calculation

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...
  • tamerj1's avatar
    tamerj1
    4 years ago

    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's avatar
Anonymous
Not applicable
4 years ago

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

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

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

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      4 years ago

      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 ", "
    )