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 assoc...
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])

     

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?

      • Plamen's avatar
        Plamen
        Frequent Visitor
        5 years ago

        Yes, I did Anonymous 

         

        Here the result when applying. The table on the left is to check if the "test" measure by the selected client (test=0) is associated with the same client with no open tasks & completed over 90 days