cancel
Showing results for 
Search instead for 
Did you mean: 
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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.