Calculated column with concatenated values of column in single rows

[Anonymous]

Hi, I'm trying to reach a new calculated column like this one in the table:

the goal is concatenate the "EE" with their respective "tns" for every "OP" but distinct by "date", so only the same OP's with the same Dates should concatenate, with this calculated column i will be able to later create a visual like this one:

any help will very much appreciated, thanks!!

[AntrikshSharma · Accepted solution]

Try this:

Column =
VAR CurrentOP = Midguel[OP]
VAR MatchingRows =
    FILTER ( Midguel, Midguel[OP] = CurrentOP )
VAR Result =
    CONCATENATEX ( MatchingRows, Midguel[EE] & "(" & Midguel[Tns] & ")", ", " )
RETURN
    Result

 

[Anonymous]

Thank you all for your help, i tried the codes and i found another problem that i wasn't expecting:

turns out my data base has more columns that divide my 'tns' (tons) in more tiny pieces, and the result of the concatenate is the tiny ton with their 'EE', i would like to SUM the tons whenever my EE, OP and Date are the same, and i tried this solution:

concatenate = CONCATENATEX(FILTER('Table','Table'[Date] = EARLIER('Table'[Date]) && 'Table'[OP] = EARLIER('Table'[OP])),'Table'[EE] & "(" & CALCULATE(SUM('Table'[Tons]),ALLEXCEPT('Table','Table'[EE],'Table'[OP],'Table'[Date])) & ")",",")

but then that returned me this LOL:

sums it but repeats the number of times my same EE appears, is there a better solution for this?

Thank you for your time, i really aprecciate your help, thanks! @harshnathani @AntrikshSharma @amitchandak 

[AntrikshSharma]

You could add more conditions to the code Harsh and I provided doesn't that solve it?

[Anonymous]

what kind of condition?

Look, I made this example hoping it clarifies a little better my problem, using only one OP, one EE and 2 dates

the green column would be the goal.

thank you very much for your help!

[amitchandak]

@Anonymous ,

Try a measure like

measure =
var _tab = summarize(Table, table[OP], Table[EE], "_1", sum(Table[tns]))
return
concatenatex(_tab, EE & "(" & _1 & ")")

Display with OP

[AntrikshSharma · Accepted solution]

Try this:

Column =
VAR CurrentOP = Midguel[OP]
VAR MatchingRows =
    FILTER ( Midguel, Midguel[OP] = CurrentOP )
VAR Result =
    CONCATENATEX ( MatchingRows, Midguel[EE] & "(" & Midguel[Tns] & ")", ", " )
RETURN
    Result

 

[amitchandak]

@Anonymous ,

concatenatex(Table,Table[EF] & "(" & Table[Tns] & ")",",")

if required use distinct

concatenatex(distinct(Table[EF],Table[Tns] ),Table[EF] & "(" & Table[Tns] & ")",",")

[harshnathani · Accepted solution]

Hi @Anonymous ,

Create a Calculated Column

CCEPCol = concatenatex(FILTER('Table10',Table10[Date] = EARLIER(Table10[Date]) && Table10[OP] = EARLIER(Table10[OP])),'Table10'[EE] & "(" & Table10[TNS] & ")",",")

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)