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.
There are 4 sites and many assets under each site.
This measure calculates MTBF(M) at asset level with result as blank if either numerator or denominator is 0
MTBF(M) = if([Runhour(Rolling)]=0 || [Rolling failures]=0, blank(), DIVIDE([Runhour(Rolling)],[Rolling failures))
Now how should i calculate average at site level of this MTBF(M)=sum of MTBF(M)/ non blank count of MTBF(M) so that at site level average is displayed but at assets level MTBF(M) individual value is displayed.
MTBF(M) is a measure not calculated column
Average in red space at site (Ex MOU)
Solved! Go to Solution.
Hi @WTAS80486
Please use
MTBF(M) =
AVERAGEX (
CALCULATETABLE (
VALUES ( 'MeterReading'[Asset] ),
CROSSFILTER ( ReadingDate[Reading Date], MeterReading[readingdate], NONE )
),
CALCULATE (
IF (
[Runhour(Rolling)] = 0
|| [COUNT(failures)Cummulative] = 0,
BLANK (),
DIVIDE ( [Runhour(Rolling)], [COUNT(failures)Cummulative] )
)
)
)
@WTAS80486
Please use
Asset URl(M) =
IF (
ISINSCOPE ( MeterReading[Asset] ),
MAXX (
CALCULATETABLE (
VALUES ( 'MeterReading'[Asset] ),
CROSSFILTER ( MeterReading[readingdate], ReadingDate[Reading Date], NONE )
),
IF (
[Runhour(Rolling)] == BLANK (),
BLANK (),
CALCULATE (
[Asset URL(Measure)],
CROSSFILTER ( MeterReading[readingdate], ReadingDate[Reading Date], NONE )
)
)
)
)
Hi @WTAS80486
Please use
MTBF(M) =
AVERAGEX (
CALCULATETABLE (
VALUES ( 'MeterReading'[Asset] ),
CROSSFILTER ( ReadingDate[Reading Date], MeterReading[readingdate], NONE )
),
CALCULATE (
IF (
[Runhour(Rolling)] = 0
|| [COUNT(failures)Cummulative] = 0,
BLANK (),
DIVIDE ( [Runhour(Rolling)], [COUNT(failures)Cummulative] )
)
)
)
How can the same concept be applied for description fields
The description fields like asset URl, desction , location are not pulling data for rows with runhours blank coming from meter reading table
should be blank at site (MOU) level
Hi @WTAS80486
Please try
=
MAXX (
CALCULATETABLE (
VALUES ( 'MeterReading'[Asset] ),
CROSSFILTER ( MeterReading[readingdate], ReadingDate[Reading Date], NONE )
),
IF (
[Runhour(Rolling)] == BLANK (),
BLANK (),
CALCULATE (
[Asset URL(Measure)],
CROSSFILTER ( MeterReading[readingdate], ReadingDate[Reading Date], NONE )
)
)
)
It does display all description fields at asset level but also displays at site. At site it needs to be blank
Here needs to be Blank
Earlier i was using this measure
@WTAS80486
Please use
Asset URl(M) =
IF (
ISINSCOPE ( MeterReading[Asset] ),
MAXX (
CALCULATETABLE (
VALUES ( 'MeterReading'[Asset] ),
CROSSFILTER ( MeterReading[readingdate], ReadingDate[Reading Date], NONE )
),
IF (
[Runhour(Rolling)] == BLANK (),
BLANK (),
CALCULATE (
[Asset URL(Measure)],
CROSSFILTER ( MeterReading[readingdate], ReadingDate[Reading Date], NONE )
)
)
)
)
@WTAS80486 This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Also, MM3TR&R: https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-...
the problem with this is
User | Count |
---|---|
21 | |
14 | |
11 | |
8 | |
5 |
User | Count |
---|---|
24 | |
22 | |
20 | |
15 | |
10 |