Forum Discussion

Plamen's avatar
Plamen
Frequent Visitor
5 years ago
Solved

Group by / Filter on agregate level based on row-level multiple criteria

Hello,

 

I am facing the following case (it's a simplified version to be clearer): 

 

Here is the table sample:

 

I have a table consisting of row-level Tasks recorded. Each task is associated with a specific Client. 

Every task has a Status and Ageing bucket group.

 

So, I want to visualize ONLY these "CLIENTS" who have no "OPEN" status tasks & "COMPLETED" "90+ days"  

 

I want to aggregate/group by/filter only these clients on multiple criteria based on row-level context. In the attached example, only Client 2 should be the result if applied properly to the above-mentioned logic.

 

Any thoughts?

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Plamen 

     

    I have this quick one, use the UI a little bit. You can also use Generate Row

     

    test = 
VAR T1 = ADDCOLUMNS(yourTable,"check", IF([Task Status]="Completed"&&[Task ageing]="90+",0,1))
VAR T2 =GROUPBY(T1,yourTable[Client Name],"checksum",SUMX(CURRENTGROUP(),[check]))
RETURN
MAXX(T2,[checksum])

     

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Plamen 

     

    I have this quick one, use the UI a little bit. You can also use Generate Row

     

    test = 
VAR T1 = ADDCOLUMNS(yourTable,"check", IF([Task Status]="Completed"&&[Task ageing]="90+",0,1))
VAR T2 =GROUPBY(T1,yourTable[Client Name],"checksum",SUMX(CURRENTGROUP(),[check]))
RETURN
MAXX(T2,[checksum])

     

    • Plamen's avatar
      Plamen
      Frequent Visitor
      5 years ago

      Hello Vera, 

       

      Thank you for your response!

      But after applying your solution to the real model it still counts Clients with Open 0-90 days Status

       

      I need to visualize all clients with "none Open tasks & Completed tasks 90+ days"

       

      Do you know how to adjust the DAX measure to get it? 

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Plamen 

         

        I was using filter in the UI to filter the measure = 0, have you applied the filter as well?

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

    Create a Table visual and drag Clients to that visual.  Write this measure

    Measure1 = calculate(countrows(Data),Data[Task Status]<>"Open"&&Data[Task Ageing]="90+")

    In the filter pane, apply a criteria on measure1 of >0.

    Hope this helps.