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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Midguel
Helper I
Helper I

Calculated column with concatenated values of column in single rows

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

CONCA1.PNG

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:

CONCA2.PNG

any help will very much appreciated, thanks!!

2 ACCEPTED SOLUTIONS
AntrikshSharma
Community Champion
Community Champion

Try this:

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

1.PNG 

View solution in original post

Hi @Midguel ,

 

Create a Calculated Column

 

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

 

1.jpg

 

 

Regards,
Harsh Nathani

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

View solution in original post

7 REPLIES 7
Midguel
Helper I
Helper I

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

conca3.PNG

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:

conca4.PNG

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 

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

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

conca5.PNG

the green column would be the goal.

thank you very much for your help!

@Midguel ,

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
Community Champion
Community Champion

Try this:

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

1.PNG 

amitchandak
Super User
Super User

@Midguel ,

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

if required use distinct

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

Hi @Midguel ,

 

Create a Calculated Column

 

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

 

1.jpg

 

 

Regards,
Harsh Nathani

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors