Calculate and count row according to condition

[pandeyml]

Hi guys,

I have a task and I'll try to put it as easy as possible.

So I have to count no. of times a [CASE] came back to a specific [Department] in my organisation .

[CASE] Table : (Contains case details)

Case Number	Subject	Category
332453	text	a
2393874	text	b
2332983	text	a

[Case Log] Table: (contains people working on the each case. So if 2 person work on one case 2 rows will be created with start and end time)[Will just take case no. 0332453 for our measure

Case Number	Worker name/id	Status	Queue Name	Came back
332453	zoel	new	tmt
332453	zima	escalated	dcs - search
332453	elka	escalated	dcs - find	Yes
332453	zoel	escalated	tmt
332453	elka	jumped	dcs - find	yes

Ok, so for case number [0332453]. I want to create a measure s.t if the case status is 'escalated' or ' jumped' and the queue name contains 'dcs' then the second instance it came to dcs should be marked as 'Yes' which means a comeback of case to the same department. Above table [Last column] is what I want. 

Case comeback:
SWITCH(
TRUE(),
'Case Log'[Status] <> "new" && 'Case Log'[Status] <> "in progress" && SEARCH("dcs",'Case Log'[Queue Name],,0)>0,
"Yes",
Blank()
)

I use the above measure but it doesn't skips the first instance where the case status is "escalated" or "jumped" and is in Queue containing 'dcs'. What I get is,

Case Number	Worker name/id	Status	Queue Name	Came back
332453	zoel	new	tmt
332453	zima	escalated	dcs - search	Yes
332453	elka	escalated	dcs - find	Yes
332453	zoel	in progress	dcs
332453	elka	jumped	dcs - find	yes

I want to skip the first yes. The dax might be silly. any new approach is welcome

Later after this i want to calculate the count of comeback for each case. I guess a simple count will work here.

Case Number	Count of case comeback
0332453	2

Hope was clear in stating the problem. Thanks

[v-jingzhang · Accepted solution]

Hi @pandeyml 

As you mentioned every row is created with start and end time, you can use the following DAX to create a column. I add a _first variable in your formula to get the first instance's start time per case and compare it with other start times. 

Case comeback = 
VAR _first = CALCULATE(MIN('Case Log'[StartTime]),ALLEXCEPT('Case Log','Case Log'[Case Number]),'Case Log'[Status] <> "new" && 'Case Log'[Status] <> "in progress" && SEARCH("dcs",'Case Log'[Queue Name],,0) > 0)
RETURN
SWITCH(
TRUE(),
'Case Log'[Status] <> "new" && 'Case Log'[Status] <> "in progress" && SEARCH("dcs",'Case Log'[Queue Name],,0)>0 && 'Case Log'[StartTime] > _first,
"Yes",
Blank()
)

After that, you can use a simple count on [Case comeback] column to calculate the count of comeback for each case.

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Nishudhan]

Hi,

You can use the following measure - 

IF("'Case Log'[Status] = "escalated" || 'Case Log'[Status] = "jumped" && CONTAINSSTRING(QueueName ,"dcs"),"Yes",Blank())

And then you have to use summarize here,

SUMMARIZE(Case Log , Case Log[Case Number]," Total Count ",Count(Came back))

I think this will work . please try and let me know . 

[pandeyml]

Hi @Nishudhan ,

Thankyou for your reply. Will it skip the first instance of the condition where the case is 'escalated'||"jumped" and "dcs" ? because the first time it comes to our department [dcs] isn't consider as a comeback.

Regards,

Shubham