Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
43 | |
19 | |
18 |