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

0 votes

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 !

Sep 2 in Power BI by John
• 120 points
119 views
John, do you want to add a measure to show the last 1 hour data in the report?

Is this your question?

1 answer to this question.

0 votes
Yes exactly !

 i need a measure for this approach
answered Sep 3 by anonymous

edited Sep 3
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

Related Questions In Power BI

0 votes
1 answer

Add Column Count_of_people across two tables to get the count of people from a city.

Hi Anitha, 1. Merge both tables, using City ...READ MORE

answered Mar 22 in Power BI by Cherukuri
• 27,960 points
30 views
0 votes
1 answer

What are the components I need to excel to become a pro in power BI

Having knowledge about these topics is a must.  Power ...READ MORE

answered Oct 8, 2018 in Power BI by Kalgi
• 41,470 points
59 views
0 votes
1 answer

To expand all lists in a row of lists at the same time without recurring values

Can't promise simple, but I have something ...READ MORE

answered Feb 19 in Power BI by Upasana
• 8,530 points
237 views
0 votes
1 answer

Calculated column with the sum of values from many columns in a row

Hi, You can create a new column by ...READ MORE

answered Mar 14 in Power BI by Cherukuri
• 27,960 points
46 views
0 votes
1 answer

Dax: Sum of values from many columns in a row

Yes, you can. Try to "Unpivot other columns" ...READ MORE

answered Apr 3 in Power BI by Upasana
• 8,530 points
473 views
0 votes
1 answer

How to assign Power BI license to a new user

The assign license method of MS Graph API might ...READ MORE

answered Sep 25, 2018 in Power BI by Kalgi
• 41,470 points
63 views
0 votes
1 answer

How to export Power Queries from One Workbook to Another with VBA?

Try solving it using the Workbook. Query ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,190 points
427 views
0 votes
1 answer

Power BI DAX expression more than 2 or conditon

Heyyy @Hannah, you just need remove or ([principlecode] = ...READ MORE

answered Oct 29, 2018 in Power BI by Kalgi
• 41,470 points
211 views
0 votes
1 answer

Replace “yes” found in one list with the actual string from another list

Objective : The idea is to use List.Transform twice, ...READ MORE

answered Feb 19 in Power BI by Upasana
• 8,530 points
45 views
0 votes
1 answer

Calculating MoM Variance of a Measure Field using DAX

Create an intermediate calculated column that considers ...READ MORE

answered Feb 28 in Power BI by Shubham
• 13,300 points
773 views