Filter Conditions in swtich() function

[jmcgowan-nz98]

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

[v-vpabbu]

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

[v-vpabbu]

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

[v-vpabbu]

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

[v-vpabbu]

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

[Shravan133 · Accepted solution]

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

[lbendlin]

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

[jmcgowan-nz98]

what function can I then use to replace it?

[lbendlin]

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-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...