Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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])
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.
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])
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?
Hi @Plamen
I was using filter in the UI to filter the measure = 0, have you applied the filter as well?
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
Hi @Plamen
You have mixed up 0, 1...so you filter the expected results out...
I will try another way tomorrow, too late today
you're totally right!
It works like charm now 🙂 thank you very much
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)