Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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 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.
Solved! Go to Solution.
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:
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 |
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:
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 |
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.
@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
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!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
7 |
User | Count |
---|---|
25 | |
24 | |
12 | |
11 | |
11 |