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

     

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)