User Profile
PierreAlfredKel
New Member
Joined 2 years ago
User Widgets
Contributions
Power BI Slicer/Filter affecting measure when I don't want it to
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 ) ) "
