Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
@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],",")
@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)
@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],",")
Thank you
Dear Greg,
If I have re appeared text again, Then what would be the solution, Could you please help?
@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)
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)
@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_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_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
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?
Dear Greg
Yes I missed add B data next column Corrected in the sample
DateSalesTextExpectedOutcome
01.09.2020 | 1 | A | A |
02.09.2020 | 2 | B | A, B |
03.09.2020 | 3 | XA | B |
04.09.2020 | 4 | C | B, C |
05.09.2020 | 5 | D | B, C, D |
06.09.2020 | 6 | XB | C, D |
07.09.2020 | 7 | B | B, C, D |
08.09.2020 | 8 | XB | C, D |
09.09.2020 | 9 | F | C, D, F |
10.09.2020 | 10 | XF | C, D |
11.09.2020 | 11 | A | C, D, A |
12.09.2020 | 12 | C, D, A | |
13.09.2020 | 13 | C, D, A | |
14.09.2020 | 14 | XA | C, D |
15.09.2020 | 15 | 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
User | Count |
---|---|
42 | |
26 | |
21 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |