cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
k_mathana
Helper II
Helper II

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.

 

Sample (2).png

 
3 ACCEPTED SOLUTIONS
Greg_Deckler
Super User
Super User

@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/3395....
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:

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],",")

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

@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?


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

@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)

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

16 REPLIES 16
Greg_Deckler
Super User
Super User

@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/3395....
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:

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],",")

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

Thank you

Dear Greg,

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

k_mathana_0-1598981934941.png

 

 

 

@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)

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

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

@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)

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

@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)

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

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?

DateOrderSalesText
01.09.2020Order11A
02.09.2020Order12B
03.09.2020Order13XA
04.09.2020Order14C
05.09.2020Order15D
06.09.2020Order16XB
07.09.2020Order17B
08.09.2020Order18 
09.09.2020Order19F
10.09.2020Order110XF
11.09.2020Order211A
12.09.2020Order212C
13.09.2020Order213B

@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)

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

@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_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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

Noted Mr. Greg @Greg_Deckler  Thank you!

Dear Greg

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

@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?


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

Dear Greg

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

 

DateSalesTextExpectedOutcome

01.09.20201AA
02.09.20202BA, B
03.09.20203XAB
04.09.20204CB, C
05.09.20205DB, C, D
06.09.20206XBC, D
07.09.20207BB, C, D
08.09.20208XBC, D
09.09.20209FC, D, F
10.09.202010XFC, D
11.09.202011AC, D, A
12.09.202012 C, D, A
13.09.202013 C, D, A
14.09.202014XAC, D
15.09.202015 C, D

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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!