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.
Is there a way to measure in POWER BI the recurring fault ticket say for the last 3 months.
Scenario is the same customer (Circuit ID) has an outage on July 10, 2024, then the following Month (August) it again has an outage. Is there a way in POWER BI for me to know that this CIRCUIT ID: 456111 has reocurred.
Example data:
1st: outage Month of July
Company name : ABC Enterprises
Circuit ID : 456111
Restoration Ticket date: July 10, 2024
Reason for outage: Fiber cut
2nd: outage Month of August
Company name : ABC Enterprises
Circuit ID : 456111
Restoration Ticket date: August 5, 2024
Reason for outage: Fiber cut
Thank in advance
Solved! Go to Solution.
Hi, @juliopatra
Assuming your data is in this format.
Two tables convert data in Power Query: Transpose-Use First Row as Headers-Rename first column-Append.
In the Power BI Desktop:
Measure = CALCULATE(COUNT(Append1[Circuit ID]),ALLEXCEPT(Append1,Append1[Restoration Ticket date]))
Count for different dates 456111, when it is greater than or equal to 2, it is a reoccurrence.
Please see the attached document.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @juliopatra
Assuming your data is in this format.
Two tables convert data in Power Query: Transpose-Use First Row as Headers-Rename first column-Append.
In the Power BI Desktop:
Measure = CALCULATE(COUNT(Append1[Circuit ID]),ALLEXCEPT(Append1,Append1[Restoration Ticket date]))
Count for different dates 456111, when it is greater than or equal to 2, it is a reoccurrence.
Please see the attached document.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so Much, It's another way to get things done. I really appreciate all the help!
Thank you so Much for the response, I will be trying it out and let you know of the results.
Thank you!!!
Hi @juliopatra ,
In transform data sort date/time column desc .You can create one calculated column and mark the occurrence of circuit Id as 1,2,3.. .You can use this data to identify the I'd which are repeating.
I hope it will be helpful.
Thanks,
Sai Teja
@SaiTejaTalasila Thank you for the idea, i will also try it and let you know of the result.
Thank you!!
Hi,
You can achieve this functionality using measures in Power BI. Follow these steps to implement the solution:
Month = MONTH('SampleTable01'[Restoration Ticket Date])
Year = YEAR('SampleTable01' [Restoration Ticket Date])
3. Create a Measure for Recurring Faults
Write a measure to calculate the number of times the same Circuit ID had an outage in the past three months:
Recurring Fault Count =
VAR CurrentCircuit = SELECTEDVALUE('SampleTable01' [Circuit ID])
VAR CurrentDate = MAX('SampleTable01' [Restoration Ticket Date])
RETURN
CALCULATE(
COUNTROWS('SampleTable01'),
FILTER(
'SampleTable01',
'SampleTable01' [Circuit ID] = CurrentCircuit &&
'SampleTable01' [Restoration Ticket Date] <= CurrentDate &&
'SampleTable01' [Restoration Ticket Date] > EDATE(CurrentDate, -3)
)
)
4. Create a Flag for Recurring Outages
Add another measure to flag whether an outage is recurring:
Is Recurring =
IF(
[Recurring Fault Count] > 1,
"Yes",
"No"
)
5. Visualize the Data
Add a table or chart in Power BI showing columns like Company Name, Circuit ID, Restoration Ticket Date, Reason for Outage, and the measures Recurring Fault Count and Is Recurring.
Hope this helps.
Thanks!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
25 | |
22 | |
11 | |
10 | |
9 |
User | Count |
---|---|
48 | |
30 | |
20 | |
17 | |
15 |