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

[Plamen]

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?

[Ashish_Mathur]

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.

[Vera_33 · Accepted solution]

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]

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? 

[Vera_33]

Hi @Plamen 

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

[Plamen]

Yes, I did @Vera_33 

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

[Vera_33]

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]

you're totally right! 

It works like charm now 🙂 thank you very much 

[Vera_33]

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)