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 individual vehicle 75th percentile kilometre values with their respective group 75th percentile kilometre value

Depending on the value of the group 75th percentile value and the individual 75th percentile value, the individual vehicle should be assigned to one of four categories:
* <= 100km
* > 100km and <= 150km
* > 150km and <= 200km 
> 200km

my DAX runs without error, except it only categories the vehicles into the first category (<= 100km)

The DAX that I am using to do this is here:

DistanceCategory =
VAR VehicleType = SELECTEDVALUE('Dimension'[Vehicle Type])
VAR Vehicle = SELECTEDVALUE('Dimension'[Vehicle ID])

-- Group 75th percentile value
VAR GroupPercentile =
PERCENTILEX.INC(
FILTER(
FACT,
LOOKUPVALUE(
'Dimension'[Vehicle Type],
'Dimension'[Machine ID],
FACT[Machine ID]
) = VehicleType
),
FACT[Kilometres],
0.75 -- 75th percentile for the group
)

-- Individual vehicle 75th percentile value
VAR VehiclePercentile =
PERCENTILEX.INC(
FILTER(
FACT,
LOOKUPVALUE(
'Dimension'[Vehicle Type],
'Dimension'[Machine ID],
FACT[Machine ID]
) = VehicleType &&
LOOKUPVALUE(
'Dimension'[Vehicle ID],
'Dimension'[Machine ID],
FACT[Machine ID]
) = Vehicle
),
FACT[Kilometres],
0.75 -- 75th percentile for the individual vehicle
)

-- Categorize based on group and individual vehicle 75th percentile
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",
BLANK()
)




  • 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"
    )

8 Replies

  • If you are creating a calculated column then SELECTEDVALUE has no meaning. It can only be used for measures.

  • 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"
    )

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi jmcgowan-nz98,

     

    we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
    If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

     

    Regards,
    Vinay Pabbu

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi @jmcgowan-nz98,

       

      we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
      If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

       

      Regards,
      Vinay Pabbu

      • Anonymous's avatar
        Anonymous
        Not applicable

        Hi @jmcgowan-nz98,

         

        we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
        If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

         

        Regards,
        Vinay Pabbu

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi jmcgowan-nz98,

     

    As we haven't heard back from you, At this time we are closing this thread. If you have any further issues, please start a new thread in the community forum, and we are here to assist you. Thankyou for your understanding and continuous support.

    Thank you for being part of the Microsoft Fabric Community.

     

    Regards,

    Vinay Pabbu