Need a DAX To check the new escalations arrived on the change of the escalation counter from zero to one or more

Question

To check the new escalations arrived, on the change of the escalation counter from zero to one or more, if no new escalation then reset to zero. So the timestamp must validate for last one hour every time for today. I need the measure accordance to this approach.  PLEASE HELP !

Comments

John, do you want to add a measure to show the last 1 hour data in the report?

Is this your question?

Answer 1

Yes exactly !

 i need a measure for this approach

Comments

Can you share any dummy data or describe the table columns which are required for the question?

---

Esc ID	Created Date	Last Esc Status Change Date	Description	Ced Status
34	8/25/2019   3:55:01PM	8/25/2019    3:55:01PM	Alert : Tool not working	Yellow
35	9/1/2019     8:44:02PM	9/1/2019      8:44:02PM	Insurance: Provide user details	Awareness
41	9/4/2019     4:56:00PM	9/4/2019      5:56:00PM	Bike Membership: Alert! Provide Details	Red

 

Need a measure for Count of escalations between current time(M1) and 1 hour prior(M2) 

M1: NOW()

M2: Current Time = NOW()-Time(1,0,0)

---

Try like below to get the count of ESC Id where last escalation changes in last 1 hour.

CALCULATE(DISTINCTCOUNT(Table[Esc ID]), Table[Last Esc Status Change Date ]>M1 && Orders[Last Esc Status Change Date ] <M2)

---

Thank you for the help :)

---

By using the above mentioned measure i'm able to get the count of Esc ID where last escalation change in last 1 hour.It's showing 1 if any new escalation in last 1 hour , if no escalation its showing 0.

Now can u please include Esc ID with Description in the measure ? 

If count of new escalation in last 1 hour is 35 , So 35 (ESC ID) and Description both should be displayed. 

This is my requirement. Please provide a measure.

Esc ID	Description
35	Insurance: Provide user details

---

Hi, one way is to group the escalations Id using a column. Use the column and description to show the table.

Try this,

New_Column = IF(Table[Last Esc Status Change Date ]>M1 && Orders[Last Esc Status Change Date ] <M2,Table[Esc ID],0)

The column will contain Esc Id for last 1 hr and 0.

Select the table and New_Column and description fields. Next, filter the New_Column values to exclude 0.

You can see Esc Id and description in the table.

Hope it works!

---

Thank You Ramya. It worked. I'm able to get the Esc ID and description in my KPI.

 In next steps i will be integrating with MS Flow. 

Can you please help with measures for the following scenarios : Please help me with only the measures for the scenarios mentioned below:

#	Scenario	Expected Result
1.	There are 3 new escalations created during last one hour	A mail is triggered showing description of the 3 escalations in mail
2.	During last one hour status of one escalation changed from Yellow to Red	No mail should be triggered