Forum Discussion
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).
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))))returnSUMX('Table',IF(ISFILTERED('Table'[Data Hora]),IF(_lastvalue<50&&_currentvalue>50,1,0)))Count =SUMX('Table',[Alarm])Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
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.
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
Ana_Cardoso , Try like
sumx(summarize(Data,Data[timestamp],"_sum",sum(Data[Value])),IF (_sum >= 50,1,0))
Hi, Ana_Cardoso
Based on your description, I created data to reproduce your scenario.
Data:
You may create a measure as below.
Count =var _datetime = SELECTEDVALUE(Data[Date])returnCOUNTROWS(FILTER(ALLSELECTED(Data),Data[Date]<_datetime&&Data[Value]>=50))Result:
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 Hora Tag Value Alarm 03/04/2020 03:40 Tag 1 10 0 03/04/2020 03:41 Tag 1 12 0 03/04/2020 03:42 Tag 1 60 1 03/04/2020 03:43 Tag 1 65 0 03/04/2020 03:44 Tag 1 30 0 03/04/2020 03:45 Tag 1 25 0 03/04/2020 03:46 Tag 1 59 1 03/04/2020 03:47 Tag 1 40 0 03/04/2020 03:48 Tag 1 2 0 03/04/2020 03:49 Tag 1 1 0 03/04/2020 03:43 Tag 2 49 0 03/04/2020 03:44 Tag 2 55 1 03/04/2020 03:45 Tag 2 59 0 03/04/2020 03:46 Tag 2 10 0 And an image where I put circles on the alarms.
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))))returnSUMX('Table',IF(ISFILTERED('Table'[Data Hora]),IF(_lastvalue<50&&_currentvalue>50,1,0)))Count =SUMX('Table',[Alarm])Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
