Forum Discussion
Combine particular column values from above rows
- 6 years ago
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],",") - 6 years ago
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?
- 6 years ago
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)
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)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 |
- k_mathana6 years ago
Helper II
Dear Greg
Thank you so much for the solution given. Thats real wow.
- Greg_Deckler6 years ago
Community Champion
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) - Greg_Deckler6 years ago
Community Champion
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:
- https://community.powerbi.com/t5/Community-Blog/Performance-Tuning-DAX-Part-1/ba-p/976275
- https://community.powerbi.com/t5/Community-Blog/Performance-Tuning-DAX-Part-2/ba-p/976813
These are also good articles:
- https://maqsoftware.com/expertise/powerbi/dax-best-practices
- https://www.sqlgene.com/2019/09/27/a-comprehensive-guide-to-power-bi-performance-tuning/
- Video: https://www.sqlbi.com/tv/optimizing-analyzing-dax-query-plans-sqlbits-xii/#
- https://docs.microsoft.com/en-us/power-bi/guidance/power-bi-optimization
Sorry for the link spam but it's a broad topic
- k_mathana6 years ago
Helper II
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? - k_mathana6 years ago
Helper II
Noted Mr. Greg Greg_Deckler Thank you!