topic Re: Combine particular column values from above rows in DAX Commands and Tips

Combine particular column values from above rows

k_mathana — Tue, 01 Sep 2020 13:39:49 GMT

I need your urgent help to solve this issue, from the below table I need to accumulate the Text column values in Cumulated Text column and if Text column value contains X then I need to remove it from the cumulated text column, Could you please kindly help. Thanks in advance.

Re: Combine particular column values from above rows

Greg_Deckler — Tue, 01 Sep 2020 14:46:16 GMT

k_mathana - See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __Previous = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Value])
RETURN
__Current - __Previous

In your case, something along the lines of:

Re: Combine particular column values from above rows

k_mathana — Tue, 01 Sep 2020 16:15:34 GMT

Dear Greg, Thank you so much, you have saved my days. Mean Time Between Failure (MTBF) is on of the fantastic solution. Thank you so much for the solution

Re: Combine particular column values from above rows

k_mathana — Tue, 01 Sep 2020 17:40:03 GMT

Dear Greg,

If I have re appeared text again, Then what would be the solution, Could you please help?

Re: Combine particular column values from above rows

Greg_Deckler — Tue, 01 Sep 2020 17:53:29 GMT

k_mathana - I'll have to take a bit of a deeper look at this as I foresaw some potential problems in how I was doing this but I also need to avoid the recursive trap. I'll take a look with the sample data provided. Is there any way you could post all of your sample data with your expected result so that I can check a more complete set of data? Posted as an Excel file link or PBIX or text in a table so that I can copy and paste it easily?

Re: Combine particular column values from above rows

k_mathana — Tue, 01 Sep 2020 17:56:10 GMT

Dear Greg

Yes I missed add B data next column Corrected in the sample

DateSalesTextExpectedOutcome

01.09.2020	1	A	A
02.09.2020	2	B	A, B
03.09.2020	3	XA	B
04.09.2020	4	C	B, C
05.09.2020	5	D	B, C, D
06.09.2020	6	XB	C, D
07.09.2020	7	B	B, C, D
08.09.2020	8	XB	C, D
09.09.2020	9	F	C, D, F
10.09.2020	10	XF	C, D
11.09.2020	11	A	C, D, A
12.09.2020	12		C, D, A
13.09.2020	13		C, D, A
14.09.2020	14	XA	C, D
15.09.2020	15		C, D

Re: Combine particular column values from above rows

Greg_Deckler — Tue, 01 Sep 2020 18:11:30 GMT

k_mathana - See if this works:

Cumulated Text = VAR __Table1 = SELECTCOLUMNS(FILTER(FILTER('Table (8)',[Date]<=EARLIER([Date])),[Text]<>BLANK() && LEFT([Text],1)<>"X"),"Text",[Text]) VAR __Table2 = SELECTCOLUMNS(ADDCOLUMNS(SELECTCOLUMNS(FILTER(FILTER('Table (8)',[Date]<=EARLIER([Date])),[Text]<>BLANK() && LEFT([Text],1)="X"),"Text1",[Text]),"Text",RIGHT([Text1],1)),"Text",[Text]) VAR __Text = IF(LEFT([Text],1)="X" || [Text]="",BLANK(),[Text]) VAR __Table = UNION(EXCEPT(__Table1, __Table2), { __Text }) VAR __Cumulative = CONCATENATEX(DISTINCT(__Table),[Text],",") RETURN IF(RIGHT(__Cumulative,1)=",",LEFT(__Cumulative,LEN(__Cumulative)-1),__Cumulative)

Re: Combine particular column values from above rows

k_mathana — Tue, 01 Sep 2020 18:32:09 GMT

Wow, Thanks for your great response. It helped me a lot. Once again thanks a lot.

Re: Combine particular column values from above rows

Greg_Deckler — Wed, 02 Sep 2020 01:08:05 GMT

k_mathana - One last version that I think covers all of the boundary cases.

Cumulated Text = VAR __Table1 = FILTER(FILTER('Table (8)',[Date]<=EARLIER([Date])),[Text]<>BLANK() && LEFT([Text],1)<>"X") VAR __Table2 = ADDCOLUMNS(FILTER(FILTER('Table (8)',[Date]<=EARLIER([Date])),[Text]<>BLANK() && LEFT([Text],1)="X"),"TextX",RIGHT([Text],1)) VAR __LastInclude = MAXX(FILTER(__Table1,NOT(ISBLANK([Text]))),[Date]) VAR __LastExclude = MAXX(FILTER(__Table2,NOT(ISBLANK([TextX]))),[Date]) VAR __Text = SWITCH(TRUE(), __LastExclude > __LastInclude,BLANK(), MAXX(FILTER(__Table1,[Date]=__LastInclude),[Text]) ) VAR __Table = UNION(EXCEPT(SELECTCOLUMNS(__Table1,"Text",[Text]), SELECTCOLUMNS(__Table2,"Text",[TextX])), { __Text }) VAR __Cumulative = CONCATENATEX(DISTINCT(__Table),[Text],",") RETURN IF(RIGHT(__Cumulative,1)=",",LEFT(__Cumulative,LEN(__Cumulative)-1),__Cumulative)

Re: Combine particular column values from above rows

Greg_Deckler — Wed, 02 Sep 2020 03:19:11 GMT

k_mathana - Wait, I lied, this is the one!!

Accumulated Text = VAR __Table = ADDCOLUMNS( FILTER( 'Table', [Date]<=EARLIER([Date]) ), "TextX",RIGHT([Text],1), "Keep",IF(LEFT([Text],1)="X",0,1) ) VAR __Table1 = ADDCOLUMNS( GROUPBY( __Table, [TextX], "__Date",MAXX(CURRENTGROUP(),[Date]) ), "FinalKeep",MAXX(FILTER(__Table,[Date]=[__Date] && [TextX] = EARLIER([TextX])),[Keep]) ) VAR __Cumulative = SUBSTITUTE(CONCATENATEX(FILTER(__Table1,[FinalKeep]=1),[TextX],","),",,",",") RETURN IF(RIGHT(__Cumulative,1)=",",LEFT(__Cumulative,LEN(__Cumulative)-1),__Cumulative)

Re: Combine particular column values from above rows

k_mathana — Wed, 02 Sep 2020 07:14:00 GMT

Dear Greg

The last solution runs faster thank you

Last doubt - If suppose I have to add one more criteria along with date as given below sample then how I should do?

Date	Order	Sales	Text
01.09.2020	Order1	1	A
02.09.2020	Order1	2	B
03.09.2020	Order1	3	XA
04.09.2020	Order1	4	C
05.09.2020	Order1	5	D
06.09.2020	Order1	6	XB
07.09.2020	Order1	7	B
08.09.2020	Order1	8
09.09.2020	Order1	9	F
10.09.2020	Order1	10	XF
11.09.2020	Order2	11	A
12.09.2020	Order2	12	C
13.09.2020	Order2	13	B

Re: Combine particular column values from above rows

Greg_Deckler — Wed, 02 Sep 2020 12:02:04 GMT

k_mathana - If you are saying that you want to group these by Order, then you would simply add that to your filter criteria initially, like this:

Accumulated Text = VAR __Table = ADDCOLUMNS( FILTER( 'Table', [Date]<=EARLIER([Date]) && [Order] = EARLIER([Order]) ), "TextX",RIGHT([Text],1), "Keep",IF(LEFT([Text],1)="X",0,1) ) VAR __Table1 = ADDCOLUMNS( GROUPBY( __Table, [TextX], "__Date",MAXX(CURRENTGROUP(),[Date]) ), "FinalKeep",MAXX(FILTER(__Table,[Date]=[__Date] && [TextX] = EARLIER([TextX])),[Keep]) ) VAR __Cumulative = SUBSTITUTE(CONCATENATEX(FILTER(__Table1,[FinalKeep]=1),[TextX],","),",,",",") RETURN IF(RIGHT(__Cumulative,1)=",",LEFT(__Cumulative,LEN(__Cumulative)-1),__Cumulative)

Re: Combine particular column values from above rows

k_mathana — Wed, 02 Sep 2020 13:57:29 GMT

Dear Greg

Thank you so much for the solution given. Thats real wow.

Re: Combine particular column values from above rows

gopschennai — Thu, 03 Sep 2020 07:40:10 GMT

Thank you

Re: Combine particular column values from above rows

k_mathana — Thu, 17 Sep 2020 10:45:03 GMT

Greg_Deckler
Dear Greg, This solution is so useful for me, This saved my lots of time. One performace related question, If I am exceeding more than 10,000 line items it doest take too much time and some times becomes irresponsive. So I am splitting my records to less than 10,000 and does works well. is there any possibilites to optimize the above code?

Re: Combine particular column values from above rows

Greg_Deckler — Thu, 17 Sep 2020 12:46:09 GMT

k_mathana I am not surprised by the performance issue. What is being done is super intensive in terms of calculation but not sure it can be helped due to the requirements. I will noodle on maybe some ways to solve this more efficiently but it is a really weird problem to solve.

In the mean time,

I have some DAX Performance Tuning articles here:

These are also good articles:

Sorry for the link spam but it's a broad topic

Re: Combine particular column values from above rows

k_mathana — Thu, 17 Sep 2020 13:46:49 GMT

Noted Mr. Greg Greg_Deckler Thank you!