Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pandeyml
Frequent Visitor

Calculate and count row according to condition

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
332453texta
2393874textb
2332983texta

[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     StatusQueue NameCame back
332453zoel  newtmt 
332453zimaescalated         dcs - search 
332453elkaescalated      dcs - findYes
332453zoelescalatedtmt 
332453elkajumpeddcs - findyes

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 NumberWorker name/idStatusQueue NameCame back
332453zoelnewtmt 
332453zimaescalateddcs - searchYes
332453elkaescalated    dcs - findYes
332453zoelin progressdcs 
332453elkajumpeddcs - findyes

 

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
03324532

Hope was clear in stating the problem. Thanks

 

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

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.

View solution in original post

3 REPLIES 3
v-jingzhang
Community Support
Community Support

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
Frequent Visitor

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 . 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.