Forum Discussion
Combine particular column values from above rows
Hi
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.
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 - __PreviousIn your case, something along the lines of:
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 __Table = EXCEPT(__Table1, __Table2) RETURN CONCATENATEX(__Table,[Text],",")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?
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)
16 Replies
- Greg_Deckler
Community Champion
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 - __PreviousIn your case, something along the lines of:
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 __Table = EXCEPT(__Table1, __Table2) RETURN CONCATENATEX(__Table,[Text],",")- k_mathana
Helper II
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
- gopschennaiRegular Visitor
Thank you
- k_mathana
Helper II
Dear Greg,
If I have re appeared text again, Then what would be the solution, Could you please help?
- Greg_Deckler
Community Champion
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?