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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
adarshmp8998
Frequent Visitor

Alarm Log Page

Hi @v-yohua-msft ,

 

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 TimeAssetState NumberAlarm Category
09-02-2024 10:57A2Clear
09-02-2024 11:29A101Low Temp
09-02-2024 11:30A101Low Temp
09-02-2024 11:31A101Low Temp
09-02-2024 11:32A101Low Temp
09-02-2024 11:34A101Low Temp
09-02-2024 11:35A101Low Temp
09-02-2024 11:36A2Clear
09-02-2024 11:37A2Clear
09-02-2024 11:38A2Clear

 

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.

1 ACCEPTED SOLUTION
adarshmp8998
Frequent Visitor

Hi @foodd , @BeaBF , @v-kaiyue-msft,

 

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=11072...

 

pbi File:

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

 

Asset NameAlarm CategoryStart TimeEnd Time
Low Temperature09-07-2024 07:5909-07-2024 07:59
Low Temperature10-07-2024 08:5910-07-2024 09:05
High Temp11-07-2024 07:2911-07-2024 07:45
BLow Temperature09-07-2024 07:5909-07-2024 09:59
BLow Product10-07-2024 08:5910-07-2024 09:05
BHigh pH11-07-2024 07:2911-07-2024 07:45
CLow BOD09-07-2024 07:5909-07-2024 08:30
COther 10-07-2024 08:5910-07-2024 09:05
CLow BOD11-07-2024 07:2911-07-2024 07:45

View solution in original post

4 REPLIES 4
adarshmp8998
Frequent Visitor

Hi @foodd , @BeaBF , @v-kaiyue-msft,

 

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=11072...

 

pbi File:

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

 

Asset NameAlarm CategoryStart TimeEnd Time
Low Temperature09-07-2024 07:5909-07-2024 07:59
Low Temperature10-07-2024 08:5910-07-2024 09:05
High Temp11-07-2024 07:2911-07-2024 07:45
BLow Temperature09-07-2024 07:5909-07-2024 09:59
BLow Product10-07-2024 08:5910-07-2024 09:05
BHigh pH11-07-2024 07:2911-07-2024 07:45
CLow BOD09-07-2024 07:5909-07-2024 08:30
COther 10-07-2024 08:5910-07-2024 09:05
CLow BOD11-07-2024 07:2911-07-2024 07:45
v-kaiyue-msft
Community Support
Community Support

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.

vkaiyuemsft_0-1720507640089.png

 

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.

BeaBF
Super User
Super User

@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

foodd
Super User
Super User

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-...

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/1447...

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!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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