Forum Discussion

BI-Geniuz's avatar
BI-Geniuz
Advocate I
6 years ago
Solved

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. ...
Need Help
  • GetResponsive's avatar
    GetResponsive
    6 years ago

    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

Icey's avatar
Icey
Community Support
6 years ago

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's avatar
BI-Geniuz
Advocate I
6 years ago

Hi Icey,

 

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

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's avatar
    GetResponsive
    Regular Visitor
    6 years ago

    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's avatar
      BI-Geniuz
      Advocate I
      6 years ago

      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