Filter Context

[KMichael99]

I'm looking to calculate a Positional Max for Jump Height. On the report I have a slicer for 'Player Name'. I have used the following DAX to calculate a MAX value, removing the filters for 'Date' and 'Player Name'. 

Positional Max CMJ Jump Height=

CALCULATE(

    MAX(CMJ[Jump Height (Imp-Mom) [cm]]]),

    REMOVEFILTERS('Calendar Reference'[Date]),

    REMOVEFILTERS('Player Reference'[Player Name]))

However, currently this does not take into account the individual's 'Position Group' (Front Row, Second Row etc...). The easy option would be to have an additional slicer for 'Position Group', however I don't want to have this. I want to keep the dashboard as quick and easy to use as possible. 

Therefore I have created a measured called 'Position Lookup', which returns the 'Position Group' based on the 'Player Name' slicer selection - this works fine. 

How can I filter the above DAX Measure to incorporate the 'Position Lookup' value. The issue is that the above DAX Measure removes the 'Player Name' filter context, but the 'Position Lookup' measure cannot do this, as it uses the 'Player Name' to lookup the 'Position Group'.

Any thoughts would be greatly appreciated 

[Anonymous]

Hi @KMichael99 

Did you get your problem solved? If not, please provide detailed sample data and your expected results. We can better understand the problem and help you. Show it as a screenshot or excel. Please remove any sensitive data in advance.

Best Regards,
Community Support Team _Yuliax

[123abc]

It looks like you want to calculate the positional max for jump height, considering the 'Position Lookup' measure while keeping the dashboard user-friendly without adding a slicer for 'Position Group'. You can achieve this by using the CALCULATETABLE function to create a virtual table that includes the necessary filters.

Here's an example of how you might modify your DAX measure:

Positional Max CMJ Jump Height =
CALCULATE(
MAX(CMJ[Jump Height (Imp-Mom) [cm]]),
REMOVEFILTERS('Calendar Reference'[Date]),
TREATAS(VALUES('Player Reference'[Player Name]), 'Player Reference'[Player Name]),
TREATAS(VALUES('Position Lookup'[Position Group]), 'Position Lookup'[Position Group])
)

In this modified measure:

1. REMOVEFILTERS('Calendar Reference'[Date]) removes filters on the 'Date'.
2. TREATAS(VALUES('Player Reference'[Player Name]), 'Player Reference'[Player Name]) creates a virtual table that includes the selected 'Player Name' from the slicer, preserving the filter context for 'Player Name'.
3. TREATAS(VALUES('Position Lookup'[Position Group]), 'Position Lookup'[Position Group]) does the same for the 'Position Lookup' measure, creating a virtual table that includes the 'Position Group' based on the selected 'Player Name'.

This way, you consider both the 'Player Name' and its associated 'Position Group' in the calculation, while still removing the filter on 'Date'. The TREATAS function is used to apply these virtual tables as filters.

Please adapt the measure based on your data model and relationships, and make sure to test it thoroughly to ensure it meets your requirements.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[KMichael99]

Hi, 

Thanks for your reply. 

It seems as if I can't do the following: TREATAS(VALUES('Position Lookup'[Position Group]), 'Position Lookup'[Position Group]). 

This is becuase the 'Position Lookup'[Position Group]) is not a TableNameOrColumnName. It is a Measure. 

Any solutions to this?

Thanks

[123abc]

Ah, I understand the challenge now. If Position Lookup is a measure and not a direct column reference, then you cannot use it directly within the TREATAS function as you've tried.

To address this, you can create a new calculated column in your data model (perhaps within the 'Player Reference' table) that uses the Position Lookup measure to get the Position Group for each player. Once you have this new calculated column, you can then use it to filter or adjust your measures.

Here's a step-by-step guide:

1. Create a New Calculated Column:

   You can create a new calculated column in the 'Player Reference' table that fetches the 'Position Group' based on the 'Player Name' using the Position Lookup measure. The formula would look something like:

Position Group Column = [Position Lookup]

1. When you add this column to your table, it will dynamically fetch the 'Position Group' for each player name based on the measure's logic.

2. Modify Your Measure:

   After creating the new calculated column, you can modify your DAX measure to incorporate this column for filtering:

Positional Max CMJ Jump Height =
CALCULATE(
MAX(CMJ[Jump Height (Imp-Mom) [cm]]),
ALL('CMJ'),
'CMJ'[Player Name] IN VALUES('Player Reference'[Player Name]),
'CMJ'[Position Group] = 'Player Reference'[Position Group Column]
)

1. Here:

   • We are still using the CALCULATE function to adjust the filter context.
   • The 'CMJ'[Position Group] = 'Player Reference'[Position Group Column] condition ensures that the calculation considers the 'Position Group' based on the new calculated column from the 'Player Reference' table.

By creating the calculated column, you essentially move the logic from a measure to the data model, making it easier to incorporate within your DAX formulas.