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

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Plamen 

     

    You have mixed up 0, 1...so you filter the expected results out...

    I will try another way tomorrow, too late today

    • Plamen's avatar
      Plamen
      Frequent Visitor
      5 years ago

      you're totally right! 

      It works like charm now 🙂 thank you very much 

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Plamen 

         

        To display in your table, the GROUPBY is not needed, but it scans too many rows if you have large dataset

        test = 
VAR T1 = ADDCOLUMNS(yourTable,"check", IF([Task Status]="Completed"&&[Task ageing]="90+",0,1))
RETURN
SUMX(T1,[check])

         

        Ashish_Mathur the way is better, but needs a little modification, and now it is 1 not 0

         

        test = 
VAR a = CALCULATE(COUNTROWS(yourTable),yourTable[Task Status]="Completed"&&yourTable[Task aging]="90+")
VAR b = CALCULATE(COUNTROWS(yourTable),yourTable[Task Status]<>"Completed"||yourTable[Task aging]<>"90+")
RETURN
IF(a>0&&b=0,1)