Forum Discussion

k_mathana's avatar
k_mathana
Icon for Helper II rankHelper II
6 years ago
Solved

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...
  • Greg_Deckler's avatar
    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 - __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],",")
  • Greg_Deckler's avatar
    Greg_Deckler
    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?

  • Greg_Deckler's avatar
    Greg_Deckler
    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)