Forum Discussion

doamss's avatar
doamss
New Member
2 years ago

Measure wont aggregate then taking fallback values

Hi, I have made a measure, which would not aggregate correctly and can't figure out how to solve it.

Shortly, there are three tables: Reisai [Columns: Reisas (ID of a trip)], Reisai_detaliai [Details of each trip; Columns: Reisas (ID), Pradzia (start of trip), Vilkikas (truck ID)], Kuras [Fuel used, Columns: Reisas (ID), Kiekis (amount used)]. Fuel table does not have unique values and some trips does not have fuel. I want to create a measure, which would take fuel for each trip, and if there are no fuel, take from the last trip with fuel the amount (regardless of filters). Here's what I came up with:

Kiekis with Fallback =
VAR CurrentReisas = SELECTEDVALUE(Reisai[Reisas])
VAR CurrentPradzia = SELECTEDVALUE(Reisai_detaliai[Pradzia])
VAR CurrentVilkikas = SELECTEDVALUE(Reisai_detaliai[Vilkikas])
VAR LastValidPradzia = MAXX(
FILTER(
ALL(Reisai_detaliai),
Reisai_detaliai[Pradzia] < CurrentPradzia && Reisai_detaliai[Vilkikas] = CurrentVilkikas &&
NOT(ISBLANK(CALCULATE(SUM(Kuras[Kiekis]))))
),
Reisai_detaliai[Pradzia]
)
VAR LastKiekis = CALCULATE(SUM(
Kuras[Kiekis])
,filter(ALL(Reisai_detaliai), Reisai_detaliai[Vilkikas]=CurrentVilkikas  && Reisai_detaliai[Pradzia] = LastValidPradzia)
 )
VAR Kiekis = CALCULATE(SUM(Kuras[Kiekis]))
RETURN
IF(      ISBLANK(Kiekis),      LastKiekis,      Kiekis )

Unfortunately, it would aggregate ONLY values that has Kiekis for specific trip, but not for LastKiekis. How do I fix it, so that the aggregate would take AVERAGE of ALL values that are either directly related or taken from last trip?

 

 

Thank you so much!

Note: there are filtered out values, the table is huge, and i want to take it into account.

3 Replies

  • Yes, how can it help? Is it just to replace ISBLANK?

    • lbendlin's avatar
      lbendlin
      Super User

      yes, it is a convenience function to probe for Blank and replace with a different value if needed.