topic Re: Filter Conditions in swtich() function in DAX Commands and Tips

Filter Conditions in swtich() function

jmcgowan-nz98 — Tue, 29 Apr 2025 23:38:23 GMT

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()
)

Re: Filter Conditions in swtich() function

lbendlin — Tue, 29 Apr 2025 23:46:17 GMT

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

Re: Filter Conditions in swtich() function

jmcgowan-nz98 — Tue, 29 Apr 2025 23:48:26 GMT

what function can I then use to replace it?

Re: Filter Conditions in swtich() function

lbendlin — Wed, 30 Apr 2025 00:20:55 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Filter Conditions in swtich() function

Shravan133 — Wed, 30 Apr 2025 00:37:59 GMT

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

Re: Filter Conditions in swtich() function

Anonymous — Mon, 05 May 2025 11:45:29 GMT

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

Re: Filter Conditions in swtich() function

Anonymous — Thu, 08 May 2025 12:16:59 GMT

Hi @jmcgowan-nz98,

Regards,
Vinay Pabbu

Re: Filter Conditions in swtich() function

Anonymous — Mon, 12 May 2025 11:24:35 GMT

Hi @jmcgowan-nz98,

Regards,
Vinay Pabbu

Re: Filter Conditions in swtich() function

Anonymous — Mon, 02 Jun 2025 07:01:45 GMT

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