cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors