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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Ana_Cardoso
Frequent Visitor

Idenfity peak

Hello all, 
I have a dataset with Timestamp, TagName and KPIValue of multiple tags. And I need to count "alarms" that is when I had a peak on a curve by TagName. My alarm trigger is KPIValue >= 50. So, when I have a timestamp that the KPIValue >=50 I count one alarm and after that I only cont +1 alarm when I had another peak on graphic. 
On the image we can see an example with just one alarm... 
Could you help me with a DAX logic to calculate it?
I figured out something like: 
Alarm = IF (Data[Value] >= 50;1;0)
SUM (Data[Alarm]) but it returns the total of Values above or equals 50, instead of 1 (number of peaks).
Curve.PNG

 

1 ACCEPTED SOLUTION

Hi, @Ana_Cardoso 

 

Based on your description, you may create two measures as below.

 

 

Alarm =
var _currentvalue = SELECTEDVALUE('Table'[Value])
var _currentdatetime = SELECTEDVALUE('Table'[Data Hora])
var _currenttag = SELECTEDVALUE('Table'[Tag])
var _lastvalue =
CALCULATE(
MAX('Table'[Value]),
FILTER(
ALLSELECTED('Table'),
'Table'[Tag] = _currenttag&&
'Table'[Data Hora] =
CALCULATE(
MAX('Table'[Data Hora]),
FILTER(
ALLSELECTED('Table'),
'Table'[Tag] = _currenttag&&
'Table'[Data Hora]<_currentdatetime
)
)
)
)

return
SUMX(
'Table',
IF(
ISFILTERED('Table'[Data Hora]),
IF(
_lastvalue<50&&
_currentvalue>50,
1,
0
)
)
)

 

Count =
SUMX(
'Table',
[Alarm]
)

 

Result:

a1.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
v-alq-msft
Community Support
Community Support

Hi, @Ana_Cardoso 

 

Based on your description, I created data to reproduce your scenario.

Data:

d1.png

 

You may create a measure as below.

Count =
var _datetime = SELECTEDVALUE(Data[Date])
return
COUNTROWS(
FILTER(
ALLSELECTED(Data),
Data[Date]<_datetime&&
Data[Value]>=50
)
)

 

 

 

 

Result:

d2.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi, I've tried all the suggestions but none is what i need. 
I bring more informantion in order to try explain better what I want to count. First a table with samples of my data with a column "Alarm" wich is what I'm looking foward.

Data HoraTagValueAlarm
03/04/2020 03:40Tag 1100
03/04/2020 03:41Tag 1120
03/04/2020 03:42Tag 1601
03/04/2020 03:43Tag 1650
03/04/2020 03:44Tag 1300
03/04/2020 03:45Tag 1250
03/04/2020 03:46Tag 1591
03/04/2020 03:47Tag 1400
03/04/2020 03:48Tag 120
03/04/2020 03:49Tag 110
03/04/2020 03:43Tag 2490
03/04/2020 03:44Tag 2551
03/04/2020 03:45Tag 2590
03/04/2020 03:46Tag 2100

And an image where I put circles on the alarms. 
Alarms example.PNG

 It is continuos data. So, I have to count when I had a peak above 50 not the number of samples wich [Value] >=50.

Hi, @Ana_Cardoso 

 

Based on your description, you may create two measures as below.

 

 

Alarm =
var _currentvalue = SELECTEDVALUE('Table'[Value])
var _currentdatetime = SELECTEDVALUE('Table'[Data Hora])
var _currenttag = SELECTEDVALUE('Table'[Tag])
var _lastvalue =
CALCULATE(
MAX('Table'[Value]),
FILTER(
ALLSELECTED('Table'),
'Table'[Tag] = _currenttag&&
'Table'[Data Hora] =
CALCULATE(
MAX('Table'[Data Hora]),
FILTER(
ALLSELECTED('Table'),
'Table'[Tag] = _currenttag&&
'Table'[Data Hora]<_currentdatetime
)
)
)
)

return
SUMX(
'Table',
IF(
ISFILTERED('Table'[Data Hora]),
IF(
_lastvalue<50&&
_currentvalue>50,
1,
0
)
)
)

 

Count =
SUMX(
'Table',
[Alarm]
)

 

Result:

a1.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi, @v-alq-msft It was just what I needed. It works perfectly.
Thanks for the help. 

Regards,

Ana

amitchandak
Super User
Super User

@Ana_Cardoso , Try like

 

sumx(summarize(Data,Data[timestamp],"_sum",sum(Data[Value])),IF (_sum >= 50,1,0))

Greg_Deckler
Super User
Super User

Not 100% sure on this, sample source data would be greatly valuable. It sounds like you need to SUMMARIZE your data by timestamp and then FILTER for >=50?

 

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Ashish_Mathur
Super User
Super User

Hi,

If you wish to count the number of instances when the figures available in the Value column exceed 50, then write this measure

=CALCULATE(COUNTROWS(Data),Data[Value]>50)

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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