Calculate the number of Comebacks

[Anonymous]

Hi,

I have a Case table with unique Case ID's and a Case Log table with works logs for each case : 
Case Table

id	case no	subject	created date	owner name
500DMS	12	xyz	datetime	zima
500TMS	13	abc	datetime	elka

Case Log:

Case_c	proxy	case no	created by	Start_Date__c	Office__c	Queue_Name	Status	Come Back2[calculated]
asvsaj	500DMS	12		10/28/2022 9:06	location1	Client Service	In Progress
asbsat	500DMS	12		10/26/2022 1:33	location1	Client Service	In Progress
sacbsn	500DMS	12		10/25/2022 6:23	location1	Client Service	In Progress
safghj	500DMS	12		10/23/2022 22:54	location1	Client Service	In Progress
sdacasa	500DMS	12		10/26/2022 22:58	location2	Client Service	In Progress
ssasaccsa	500DMS	12		10/26/2022 20:09	location2	Client Service	In Progress
scsZXxz	500DMS	12		10/22/2022 0:13	location3	Client Service	New
xzccac	500DMS	12		10/26/2022 20:20	location1	DCS - Data and series team	Escalated	Yes
czxxzcxz	500DMS	12		10/26/2022 18:27	location1	DCS - Data and series team	Escalated	Yes
zxxz hgg	500DMS	12		10/25/2022 21:12	location3	DCS - Data and series team	Escalated	Yes
xzxczxcsd	500DMS	12		10/26/2022 19:33	location1	DCS - Data and series team	Escalated	Yes
xzxzxc	500DMS	12		10/26/2022 3:17	location2	Client Service	Escalated
zxxzxzxz	500DMS	12		10/27/2022 3:00	location4	DCS - Data and mani team	Escalated	Yes
zxxzcxzc	500DMS	12		10/26/2022 20:23	location1	DCS - Data and mani team	Escalated	Yes
xzxzcxz	500DMS	12		10/24/2022 21:12	location2	DCS - Data and mani team	Escalated	Yes
xzxzczx	500DMS	12		10/27/2022 8:22	location1	DCS - Data and mani team	Escalated	Yes
zxcxzc	500DMS	12		11/11/2022 23:17	location2	DCS - Data and mani team	Escalated	Yes

So the case and case log are connect by 'Case'[ID] and 'Case Log'[Proxy] column by one to many. I want to get the case comeback for each case when it came to the department DCS and status not 'escalated' or 'assigned' or few more values . so i created the calculated column which prints "Yes" when the conditions satisfy but I want to skip the first "Yes" as it'

sthe first time it came to the DCS department. So i use the below code to substract 1 but in matrix it substract 1 from overall comebacks and not for unique cases.
Count of Come_Back_2 = if((COUNTA('Case Log'[Come Back])-1)<=0,BLANK(),(COUNTA('Case Log'[Come Back])-1))

 So all in all i get the count of comeback value but when i put it in table w.r.t the case owner it just substracts (1) from the whole count of comebacks. so if i have 2 cases for one owner and 7 comebacks for case1 and 6 for case 2 it gives me total count as 12 i.e 13 - 1. but i need is (7-1) + (6-1) i.e 11

Hope it is clear. 

Thanks for any help !!

[ppm1 · Accepted solution]

Please update your post with the additional data/information you shared via Private Message, so that others can potentially benefit from your scenario. Below is an updated measure expression that appears to do what you need. Please confirm.

CaseBackCount =
SUMX (
    VALUES ( 'Case'[case no] ),
    VAR vCount =
        CALCULATE (
            COUNTROWS ( 'CaseLog' ),
            NOT ( 'CaseLog'[Status] IN { "new", "in progress" } )
                && SEARCH ( "dcs", 'CaseLog'[Queue_Name],, 0 ) > 0
        )
    RETURN
        IF ( ISBLANK ( vCount ), 0, vCount - 1 )
)

Pat

[ppm1]

Why are your using RANKX here? What about this measure?

= CALCULATE(COUNTROWS('tablename'), 'tablename'[yes/no col] = "Yes") - 1

Pat

[Anonymous]

I got the yes/no column after this code. So if I use your measure it just gives me -1 for each case no in a table

[FreemanZ]

it seems you expect a calculated column, try to create one with this:

CALCULATE(

    COUNTROWS(tablename),  ALLEXCEPT(tablename, tablename[case]),

    tablename[yes/no col] = "Yes"

) - 1