topic Alarm Log Page in DAX Commands and Tips

Alarm Log Page

adarshmp8998 — Sun, 07 Jul 2024 13:09:31 GMT

This question is next to "Alarm Log, Start Time & End Time in Power BI Visual" which you have already answered.

Can you help me to get a single instance rather than all the records of Low Temp and same Clear Time for all.

Alarm Log Table should contain:

Start Time: 09-02-2024 11:29

End Time: 09-02-2024 11:36

Date Time	Asset	State Number	Alarm Category
09-02-2024 10:57	A	2	Clear
09-02-2024 11:29	A	101	Low Temp
09-02-2024 11:30	A	101	Low Temp
09-02-2024 11:31	A	101	Low Temp
09-02-2024 11:32	A	101	Low Temp
09-02-2024 11:34	A	101	Low Temp
09-02-2024 11:35	A	101	Low Temp
09-02-2024 11:36	A	2	Clear
09-02-2024 11:37	A	2	Clear
09-02-2024 11:38	A	2	Clear

You have provided below DAX to create a table:

Alarm Log = 
ADDCOLUMNS(
    FILTER(
        'Table',
        'Table'[State Number] <> 1 && 
        'Table'[State Number] <> 2 && 
        'Table'[State Number] <> 3
    ),
    "Start Time", 'Table'[Date Time],
    "End Time", 
    CALCULATE(
        MIN('Table'[Date Time]),
        FILTER(
            ALL('Table'),
            'Table'[Asset] = EARLIER('Table'[Asset]) && 
            'Table'[Date Time] > EARLIER('Table'[Date Time]) && 
            ('Table'[State Number] = 1 || 
            'Table'[State Number] = 2 || 
            'Table'[State Number] = 3)
        )
    )
)

Thank You in Advance.

Re: Alarm Log Page

foodd — Sun, 07 Jul 2024 13:12:04 GMT

Hello adarshmp8998 , thank you for sharing your question with the Community. Remember to adhere to the decorum of the Community Forum when asking a question.

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523/highlight/true#M607150

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you. Proud to be a Super User!

Re: Alarm Log Page

BeaBF — Mon, 08 Jul 2024 12:23:49 GMT

adarshmp8998 Hi!

Alarm Log =
ADDCOLUMNS(
FILTER(
'Table',
'Table'[Alarm Category] = "Low Temp" &&
NOT(ISBLANK(
CALCULATE(
MAX('Table'[Date Time]),
FILTER(
ALL('Table'),
'Table'[Asset] = EARLIER('Table'[Asset]) &&
'Table'[Date Time] > EARLIER('Table'[Date Time]) &&
'Table'[Alarm Category] = "Clear" &&
'Table'[Date Time] <= EARLIER('End Time') &&
'Table'[Date Time] > EARLIER('Start Time')
)
)
))
),
"Start Time", 'Table'[Date Time],
"End Time",
CALCULATE(
MAX('Table'[Date Time]),
FILTER(
ALL('Table'),
'Table'[Asset] = EARLIER('Table'[Asset]) &&
'Table'[Date Time] > EARLIER('Table'[Date Time]) &&
'Table'[Alarm Category] = "Clear" &&
'Table'[Date Time] > EARLIER('End Time')
)
)
)

BBF

Re: Alarm Log Page

Anonymous — Tue, 09 Jul 2024 06:48:00 GMT

Hi adarshmp8998 ,

Thanks for the reply from BeaBF and foodd , please allow me to provide another insight:

Do you need to display different Asset values? If so, you can put the Asset field into the slicer.

If I understand you wrongly, please provide more details so that we can better help you solve the problem.

Best Regards,

Clara Gong

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

Re: Alarm Log Page

adarshmp8998 — Tue, 09 Jul 2024 14:03:39 GMT

Hi foodd , BeaBF , Anonymous,

Please accept my sincere apology for not providing necessary/insufficent information.

Please refer to the attached files and work is in progress for the same.

Question:

Lets consider a alarm of Low Temperature triggered at 11:01 and the it was active till 11:30 so between 11:01 to 11:30 Alarm Category will be Low Temperature. So here I want first row in the alarm log table that should be 11:01 as a start time instead of all records between 11:01 to 11:30.

For Sample I am Creating a dummy Alarm Log Table to get visualize how alarm log table should look like.

Please refer to the attached files of Sample Data set and pbi

Sample Data:

https://docs.google.com/spreadsheets/d/1675VjlLLT-3r_gx8JJY7be3KVX3hCFXl/edit?usp=sharing&ouid=110720534063898360565&rtpof=true&sd=true

pbi File:

https://drive.google.com/file/d/1tDY0ajaXCRlxt4LIzIL19HbV0RnxUg4S/view?usp=sharing

Asset Name	Alarm Category	Start Time	End Time
A	Low Temperature	09-07-2024 07:59	09-07-2024 07:59
A	Low Temperature	10-07-2024 08:59	10-07-2024 09:05
A	High Temp	11-07-2024 07:29	11-07-2024 07:45
B	Low Temperature	09-07-2024 07:59	09-07-2024 09:59
B	Low Product	10-07-2024 08:59	10-07-2024 09:05
B	High pH	11-07-2024 07:29	11-07-2024 07:45
C	Low BOD	09-07-2024 07:59	09-07-2024 08:30
C	Other	10-07-2024 08:59	10-07-2024 09:05
C	Low BOD	11-07-2024 07:29	11-07-2024 07:45