Forum Discussion
Remove Table Filters While Using Virtual Table Measures
Hi some_bih, thank you for those two possible solutions! With the 'Max Efficiency Score' measure, I'm only looking to interact with the date slicer and not the 'Driver Fleet Name' and 'DriverName/ID' filters in the table. I believe the ALLEXCEPT is function I'll need, but I'm still returning the max value within each Driver Fleet/DriverName combination instead of the overall max. Do you see anything I could be doing wrong?
MAX Efficiency Score =
var table1 = SUMMARIZE('Fuel (Traveling)', 'Fuel (Traveling)'[Driver Fleet Name], 'Fuel (Traveling)'[DriverName/ID], "EffScore", -[True Avg. Travel Difference])
var table2 = ADDCOLUMNS(table1, "DriverCountFleet", CALCULATE(DISTINCTCOUNT('Fuel (Traveling)'[DriverID]), ALLEXCEPT('Fuel (Traveling)', 'Fuel (Traveling)'[Driver Fleet Name], 'Combined Calendar'[Date - Copy])), "OrderCountFleet", CALCULATE(DISTINCTCOUNT('Fuel (Traveling)'[Order ID]), ALLEXCEPT('Fuel (Traveling)', 'Fuel (Traveling)'[Driver Fleet Name], 'Combined Calendar'[Date - Copy])), "OrderCountDriver", CALCULATE(DISTINCTCOUNT('Fuel (Traveling)'[Order ID])) + 0)
var table4 = ADDCOLUMNS(table2, "AvgOrders", DIVIDE([OrderCountFleet], [DriverCountFleet]))
var table5 = ADDCOLUMNS(table4, "Ratio", DIVIDE([OrderCountDriver], [AvgOrders]))
var table6 = ADDCOLUMNS(table5, "Score", IF([Ratio] > 1, [EffScore] * 1, [EffScore] * [Ratio]))
var final = ADDCOLUMNS(table6, "MaxValue", CALCULATE(MAXX(table6, [Score]), ALLEXCEPT('Combined Calendar','Combined Calendar'[Date - Copy])))
return MAXX(final, [MaxValue])
- some_bih2 years agoCommunity Champion
Hi C_PriceOH I do not see overall model and relationships, your measure so I do not want to spend your time on checking what could be "solution" when you want to use ALLEXCEPT, I do not have enought data / info.
I suggest you to create both measure I provide you and check if it works, if yes insert them into your visuals and "be happy" and accept solution.