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.
09-01-2020 19:22 PM - last edited 09-01-2020 21:27 PM
OK, this is admittedly kind of an odd one. It comes to us by way of a problem posed by @k_mathana (original thread)and is essentially a text accumulator between previous rows with an extra special twist that certain rows can negate accumulation of certain text but then subsequent rows can bring that text back. It kind of reminds me a bit of Cthulhu. But it's also got this almost recursive feel to it, you know, like a real shiny blue suitcase, Ark of the Covenant, Maltese Falcon sort of vibe. I assure you it is not, recursive that is, but boy does it kind of act like it! Anyway, it's complex, it's weird, I thought it was cool.
Accumulated Text Measure =
VAR __Date = MAX([Date])
VAR __Table =
ADDCOLUMNS(
FILTER(
ALL('Table'),
[Date]<=__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)
And, sort of like when I created Cthulhu, I have no idea regarding the practical application of it. Although, since it's creation I have referred to Cthulhu on occasion so you never know!
eyJrIjoiZDU3ZjQ0YzctYmNiOC00ZmZjLWIwZTctMGRiMjRkN2M0NzNlIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9&pageName=ReportSection