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 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 - __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],",")
  • 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's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity 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 - __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],",")
    • k_mathana's avatar
      k_mathana
      Icon for Helper II rankHelper 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

    • k_mathana's avatar
      k_mathana
      Icon for Helper II rankHelper II

      Dear Greg,

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

       

       

       

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity 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?