Forum Discussion

jmcgowan-nz98's avatar
jmcgowan-nz98
New Member
1 year ago
Solved

Filter Conditions in swtich() function

I am creating a new column in one of my tables my dimension table has a one to many relationship with my fact table on a column called Machine ID What I am trying to do is compare the individua...
  • Shravan133's avatar
    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"
    )