Power BI Slicer/Filter affecting measure when I don't want it to

[PierreAlfredKel]

Hello everybody hope you doing well :).

I'm a beginner at Power BI, I am currently working on making powerful visuals to better understand the maintenance in my company. Particulary my power bi targets the zones with the failure breakdowns in the factory.

It also creates key performance indicators in relation with these zones as seen with the images below.

I have a database from sfc, which incoporates all the events in which x machine in x zone has encountered a breakdown in the factory. You need to know that in my company each time the factory stops, the stop is named T, and depending on the type of event that stops the line of the factory, an attributed number will be put after the T. For example if the breakdown is a unplannified maintenance stop instead of a plannified maintenance stop then it would be labeled as T5 instead of T2.

I have this measure called timedifference= which sums up the time between the failures and it works with an index which I created in power query.

I have another measure called "ComptesdePannes" which counts the number of breakdowns between an interval of time. To create the KPI MTBF (MeanTimeBetweenFailure), I need to divide my TimeDifference Measure by my ComptedePannes Measure (TimeDifference/ComptedePannes). And this works perfectly fine, but when I use my filters and only click on the T5, my comptes de pannes will change from 199 to 61 which works out as intended but my timedifference measure will also change from (118390 mins) to (24712 mins) and I don't want this. What I want is that when I divide, the numerator stays the same (118390) while the denominator changes based on the T Filters selected such as T5. What could I do, to stop slicers from affecting this one measure.

This is my current timedifference formula :

"
TimeDifference =
VAR CurrentIndex = 'EVT ARRET'[Index] 
VAR CurrentRow = 'EVT ARRET'[DAT_EVTFINAL]
VAR NextRow =
CALCULATE(
MIN('EVT ARRET'[DAT_EVT]),
FILTER(
'EVT ARRET',
'EVT ARRET'[Index] = CurrentIndex + 1 
)
)
RETURN
IF(
ISBLANK(NextRow),
BLANK(),
IF(
NextRow = CurrentRow,
0,
(NextRow - CurrentRow) * 24 * 60 
)
)

"

I tried using multiple different  formula with various functions like ALL and even tried editing interactions but it didn't work as intended, all my previous values became blank with the different formulas, and I don't know why. I also tried working with the index as I thought that maybe since the timedifference measure works in relation with the index, maybe the filters would be affect the index. I don't know how to advance in my projet, I'm thankful for any help! 

Test Formula 1

"

TimeDifference =
VAR CurrentIndex = 'EVT ARRET'[Index]
VAR CurrentRow = 'EVT ARRET'[DAT_EVTFINAL]
VAR NextRow =
CALCULATE(
MIN('EVT ARRET'[DAT_EVT]),
FILTER(
ALL('EVT ARRET'), 
'EVT ARRET'[Index] = CurrentIndex + 1
)
)
RETURN
IF(
ISBLANK(NextRow),
BLANK(),
IF(
NextRow = CurrentRow,
0,
(NextRow - CurrentRow) * 24 * 60
)
)

"

and I also tried this formula

Test Formula 2

"

TimeDifference =
VAR CurrentIndex = CALCULATE(MAX('EVT ARRET'[Index]), ALLSELECTED('EVT ARRET'))
VAR CurrentRow = 'EVT ARRET'[DAT_EVTFINAL]
VAR NextRow =
CALCULATE(
MIN('EVT ARRET'[DAT_EVT]),
FILTER(
ALL('EVT ARRET'),
'EVT ARRET'[Index] = CurrentIndex + 1
)
)
RETURN
IF(
ISBLANK(NextRow),
BLANK(),
IF(
NextRow = CurrentRow,
0,
(NextRow - CurrentRow) * 24 * 60
)
)

"

[v-nuoc-msft · Accepted solution]

Hi @PierreAlfredKel 

For your question about not wanting to be affected by the slicer, here I suggest you create a virtual table for calculating TimeDifference.

But be careful not to create a relationship between the data table and the virtual table.

Regards,

Nono Chen

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

[PierreAlfredKel]

Also timedifference is not a measure but a calculated column!

[PierreAlfredKel]

I would like to also inform that when I used my foruma that works except for blocking out the filters gives me 1988 distinct values and that when I use the test formula with all function, allselected, remove filters, it solely gives me one distinct value