Forum Discussion
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
- lbendlinSuper User
If you are creating a calculated column then SELECTEDVALUE has no meaning. It can only be used for measures.
- jmcgowan-nz98New Member
what function can I then use to replace it?
- lbendlinSuper User
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
- Shravan133Super User
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"
) - AnonymousNot 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- AnonymousNot 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- AnonymousNot 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
- AnonymousNot 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