Measure giving different results in different visuals

[PaulR17]

Hi All,

I have a measure that gives the correct results in a table, but completely different results in any chart that I try.

This is the measure:

Actual Utilised Days = SUMX(VALUES(BookingVehicles[VehicleId]), [Total Days Utilised])

I have tried creating a new page and reloading all the filters, but nothing works.

Any ideas as to how to fix this would be greatly appreciated.

Thank you

[V-lianl-msft · Accepted solution]

Hi @PaulR17 ,

As @TomMartens said, the current row context and filter context will affect the result of measure. Please refer to for more details:

https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/ 

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

[TomMartens]

Hey @PaulR17 ,

this is difficult to answer.

What you have to consider whenever a measure is evaluated, is the concept known as Filter context,

Ths means,  everything that filters a table contributes to the filter context, of course, this is only valid for columns from tables that have an impact on the numeric expression, the first parameter of the CALCULATE function.

The measure (I assume it is a measure), the 2nd parameter of the SUMX, is impacted by the filter context.

Column header of a table visual, column and row headers of the matrix visual, columns used on the axis of a visual, as well as the legend are forming the filter context. Of course also slicers are contributing to filter context.

Hopefully, this provides some ideas on what's going on.

Regards,

Tom

[PaulR17]

Hi @TomMartens 

Thank you for the advice.

I have made sure that all the filters are the same for both visuals, but it doesn't stop the results being wildely and inconsistently different. 

I'll keep plugging away 🙂

[amitchandak]

@PaulR17 , can you share formula for [Total Days Utilised]

[PaulR17]

Hi Amitchandak,

Here is the formula 

Total Days Utilised =

var sameVehicle = COUNTROWS (
FILTER (
VALUES ( BookingVehicles[BookingId] ),
CALCULATE ( COUNT ( BookingVehicles[VehicleId] ) > DISTINCTCOUNT ( BookingVehicles[VehicleId] ) )
)
)

return {
IF(NOT(sameVehicle),
SUMX(
VALUES(BookingVehicles[BookingId]), [New No of Utilised Days]
),
DIVIDE(SUMX(
VALUES(BookingVehicles[BookingId]), [New No of Utilised Days]
), 2 )
)
}

And here is [New No of Utilised Days]

New No of Utilised Days =

var bookingID = MAX(BookingVehicles[BookingId])
var vehicleSwap = COUNTROWS(FILTER(BookingVehicles, BookingVehicles[BookingId] = bookingID)) >1
var noVehicleSwap = COUNTROWS(FILTER(BookingVehicles, BookingVehicles[BookingId] = bookingID)) = 1
var moreThanTwoVehicles = COUNTROWS(FILTER(BookingVehicles, BookingVehicles[BookingId] = bookingID)) > 2
var endTime = IF(noVehicleSwap, MAX(BookingVehicles[Effective End Datetime]), IF(MAX(BookingVehicles[Actual Delivery Time]) <MAX( BookingVehicles[Effective End Datetime]) && MAX(HireStatus[Hire Status Name]) = "Finalised", MAX(BookingVehicles[Actual Delivery Time]), MAX(BookingVehicles[Effective End Datetime])))
var startTime = IF(MAX(BookingDates[ChargeFromDateTime]) > MAX(BookingVehicles[Effective Start Datetime]), MAX(BookingDates[ChargeFromDateTime]), MAX(BookingVehicles[Effective Start Datetime]))
var TotalMinutesHired = DATEDIFF(startTime, endTime , MINUTE)
var totalDaysHired = DATEDIFF(startTime, endTime, DAY)
var endGracetime = MAX(BookingVehicles[Effective End Time])
var startGraceTime = MAX(BookingVehicles[Effective Start Time])
var Grace = IF(endGracetime < startGraceTime, 0, MOD(TotalMinutesHired,1440)) // Capture early returns
var GraceDiff = Grace - 1438 // To capture whole days from 00.01 to 23.59 that are not grace time
var AllocatedGraceDays = MAX(BookingDates[GracePeriod]) // From vehicle swaps etc
var diffMinutes = MAX(BookingVehicles[DeliveryTime]) < MAX(BookingVehicles[Effective Start Time])
var notLastVehicle = MAX(BookingVehicles[Effective Start Date]) = MAX(BookingDates[ChargeFromDate])
var Days = MAX(BookingVehicles[Effective End Datetime]) - MAX(BookingVehicles[Effective Start Datetime]) - AllocatedGraceDays
var LastDayofDateRange = MAX(Dates[Date])
var FirstDayofDateRange = MIN(Dates[Date])
var result =
SUMX(
FILTER(BookingVehicles, NOT(ISBLANK(BookingVehicles[VehicleId]))),
var firstDayOfUtilisation = startTime
var lastDayOfUtilisation = endTime

return
IF(
ISBLANK(firstDayOfUtilisation) || firstDayOfUtilisation > lastDayOfUtilisation,
0,
COUNTROWS(
INTERSECT(
CALENDAR(firstDayOfUtilisation, lastDayOfUtilisation),
VALUES('Dates'[Date])
)
)
)
)

var allowBlank = NOT(ISBLANK([No of InFleet Days]))
var isBlank = AND(allowBlank, ISBLANK(result))

return IF(isBlank, 0, IF([lastDayOfUtilisation] > LastDayofDateRange,result,
IF([firstDayOfUtilisation] >= FirstDayofDateRange && [lastDayOfUtilisation] <= LastDayofDateRange, result - 1,
IF(TotalMinutesHired< 1440, result,
IF(GraceDiff = 0, result,
IF(vehicleSwap && notLastVehicle && moreThanTwoVehicles, result +1,
IF(vehicleSwap && notLastVehicle, result,
IF(diffMinutes && vehicleSwap, result,
IF(Grace >59, result +1, result

))))))))

)

Really appreciate your help

[PaulR17]

Sorry, @amitchandak I forgot to mention you. Hopefully, you will see this now