cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BI-Geniuz
Frequent Visitor

Advanced Grouping in unstructered set - StringAggregation

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

table1.png

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
GetResponsive
Regular Visitor

Re: Advanced Grouping in unstructered set - StringAggregation

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])
 
 
event-index.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Hope this helps

 

BR

 

Jeroen Heerschop

Get Responsive

View solution in original post

4 REPLIES 4
Highlighted
Community Support
Community Support

Re: Advanced Grouping in unstructered set - StringAggregation

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.

Advanced Grouping in unstructered set - StringAggregation.PNG

 

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
Frequent Visitor

Re: Advanced Grouping in unstructered set - StringAggregation

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
Regular Visitor

Re: Advanced Grouping in unstructered set - StringAggregation

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])
 
 
event-index.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Hope this helps

 

BR

 

Jeroen Heerschop

Get Responsive

View solution in original post

BI-Geniuz
Frequent Visitor

Re: Advanced Grouping in unstructered set - StringAggregation

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

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors