Advanced Grouping in unstructered set - StringAggregation

[BI-Geniuz]

Hi fellow BI-enthusiastics,

I'm struggeling with a complex issue and hopefully someone can help me out.
I'm trying to create groups based on a unstructed set that have some self defined events.

Definition of event = 
If Index-1 or Index-2 has a value then it's an event and
if Index+1 or Index+2 has a value then it's an event. 

Basically I want to scan and group the indexes together to make an Unique event. Hopefully the screenshot explains what I need



I have came up with the following DAX, but that doesn't quite give me the results I need.

IF([EXCEEDED_LIMIT]<>BLANK();

		CALCULATE(CONCATENATEX(Tabel1;[Index];"|";[Index];ASC);
            		FILTER(Tabel1;
                                   Tabel1[Index]>=EARLIER(Tabel1[Index])-1 &&
                                   Tabel1[Index]<=EARLIER(Tabel1[Index])+1)
                                   ))

Hopefully someone posses' more skills then I do and can help me out. This would be highly appreciated!!

Sample file is here : Table1.xlsx

[Icey]

Hi @BI-Geniuz ,

According to my test, I can only get the result below at present. This is my PBIX file. I will let you know as soon as I have other solutions.

Best Regards,

Icey Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BI-Geniuz]

Hi Icey,

thanks for helping out this far! highy appreciated and it's a tough one indeed 😛

Update:
I also got the flagging code wrapped in here. Maybe that helps a little bit?

var Index1 = LOOKUPVALUE([EXCEEDED_LIMIT];[Index];[Index]+1)
var Index2 = LOOKUPVALUE([EXCEEDED_LIMIT];[Index];[Index]-1)

return  

IF([EXCEEDED_LIMIT]<>BLANK();1;
     IF([EXCEEDED_LIMIT]=BLANK();
                      IF(Index1<>BLANK() && Index2<>BLANK();1;BLANK())))

[GetResponsive · Accepted solution]

Hi Nico,

one solution could be to use (various) custom columns to get to unique identifiers.I have for testing purposes now split it into several columns, but most probably can be done in much more condensed way.

1) Group Ind

Group Ind =

VAR PreviousRow =

TOPN (

1;

FILTER (

Blad1;

Blad1[index] < EARLIER ( Blad1[index] )

);

[index]; DESC

)

VAR PreviousIndex =

MINX ( PreviousRow; [index] )

VAR PreviousExceeded =

MINX ( PreviousRow; [exceeded_limit])

RETURN

IF(OR(AND(Blad1[exceeded_limit]<>BLANK();PreviousExceeded=BLANK());AND(Blad1[exceeded_limit]<>BLANK();PreviousExceeded<>BLANK()));1;0)

2) New Group Ind

New Group Ind =

VAR PreviousRow =

TOPN (

1;

FILTER (

Blad1;

Blad1[index] < EARLIER ( Blad1[index] )

);

[index]; DESC

)

VAR PreviousGroupInd =

MINX ( PreviousRow; [Group Ind] )

RETURN

IF(AND(Blad1[Group Ind]=1;PreviousGroupInd=0);"Ja";"Nee")

3) Group Number

Group Number =

10000+CALCULATE(COUNT(Blad1[New Group Ind]);FILTER(Blad1;Blad1[New Group Ind]="Ja");FILTER (Blad1;Blad1[index] <= EARLIER ( Blad1[index] )))

4) Event Index

Event Index = IF(Blad1[Group Ind]=1;Blad1[Group Number];Blad1[index])

Hope this helps

BR

Jeroen Heerschop

Get Responsive

[BI-Geniuz]

Hi Jeroen, thanks a lot this is exactly what I needed. I followed your logic and understood the principle. 
It works like a charm and for my own practice I have replicated your logic and make some DAX statements myself just for practicing. 

Again thanks a lot Jeroen and I have attached your PBIX with some replicated code as well

PBIX