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.

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

 

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

4 Replies

  • 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