Forum Discussion
Filter Conditions in swtich() function
- 1 year ago
try using related and treatas().
DistanceCategory =
VAR MachineID = 'Dimension'[Machine ID]
VAR VehicleType = RELATED('Dimension'[Vehicle Type])
VAR VehicleID = RELATED('Dimension'[Vehicle ID])-- Group 75th percentile
VAR GroupPercentile =
CALCULATE(
PERCENTILEX.INC(FACT, FACT[Kilometres], 0.75),
FACT,
TREATAS({VehicleType}, 'Dimension'[Vehicle Type])
)-- Individual vehicle 75th percentile
VAR VehiclePercentile =
CALCULATE(
PERCENTILEX.INC(FACT, FACT[Kilometres], 0.75),
FACT,
TREATAS({VehicleID}, 'Dimension'[Vehicle ID])
)RETURN
SWITCH(
TRUE(),
GroupPercentile <= 100 && VehiclePercentile <= 100,
"<= 100km",
GroupPercentile > 100 && GroupPercentile <= 150 &&
VehiclePercentile > 100 && VehiclePercentile <= 150,
"> 100km && <= 150km",
GroupPercentile > 150 && GroupPercentile <= 200 &&
VehiclePercentile > 150 && VehiclePercentile <= 200,
"> 150km && <= 200km",
GroupPercentile > 200 && VehiclePercentile > 200,
"> 200km",
"Unclassified"
)
try using related and treatas().
DistanceCategory =
VAR MachineID = 'Dimension'[Machine ID]
VAR VehicleType = RELATED('Dimension'[Vehicle Type])
VAR VehicleID = RELATED('Dimension'[Vehicle ID])
-- Group 75th percentile
VAR GroupPercentile =
CALCULATE(
PERCENTILEX.INC(FACT, FACT[Kilometres], 0.75),
FACT,
TREATAS({VehicleType}, 'Dimension'[Vehicle Type])
)
-- Individual vehicle 75th percentile
VAR VehiclePercentile =
CALCULATE(
PERCENTILEX.INC(FACT, FACT[Kilometres], 0.75),
FACT,
TREATAS({VehicleID}, 'Dimension'[Vehicle ID])
)
RETURN
SWITCH(
TRUE(),
GroupPercentile <= 100 && VehiclePercentile <= 100,
"<= 100km",
GroupPercentile > 100 && GroupPercentile <= 150 &&
VehiclePercentile > 100 && VehiclePercentile <= 150,
"> 100km && <= 150km",
GroupPercentile > 150 && GroupPercentile <= 200 &&
VehiclePercentile > 150 && VehiclePercentile <= 200,
"> 150km && <= 200km",
GroupPercentile > 200 && VehiclePercentile > 200,
"> 200km",
"Unclassified"
)