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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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