topic Re: Calculated column with concatenated values of column in single rows in DAX Commands and Tips

Calculated column with concatenated values of column in single rows

Anonymous — Tue, 28 Jul 2020 00:08:38 GMT

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

Re: Calculated column with concatenated values of column in single rows

amitchandak — Tue, 28 Jul 2020 04:08:55 GMT

Anonymous ,

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

if required use distinct

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

Re: Calculated column with concatenated values of column in single rows

AntrikshSharma — Tue, 28 Jul 2020 04:48:20 GMT

Try this:

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

Re: Calculated column with concatenated values of column in single rows

Anonymous — Tue, 28 Jul 2020 06:34:25 GMT

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)

Re: Calculated column with concatenated values of column in single rows

Anonymous — Tue, 28 Jul 2020 16:18:28 GMT

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! Anonymous AntrikshSharma amitchandak

Re: Calculated column with concatenated values of column in single rows

AntrikshSharma — Tue, 28 Jul 2020 16:35:10 GMT

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

Re: Calculated column with concatenated values of column in single rows

Anonymous — Tue, 28 Jul 2020 19:31:14 GMT

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!

Re: Calculated column with concatenated values of column in single rows

amitchandak — Wed, 29 Jul 2020 17:35:27 GMT

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