Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to 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.
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.
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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
103 | |
99 | |
98 | |
38 | |
37 |
User | Count |
---|---|
151 | |
121 | |
73 | |
71 | |
63 |