Forum Discussion
Filter table by max value, filtering by 2 values
I have a table with the columns Category, Sub Category, Score, and User ID. I would like to add a measure that displays the Max Score per Category, per User ID. I can create a measure for Max Score by category, but cannot figure how to fileter it as well to User ID.
Help is appreciated.
Hi MichaelRensing,
To create a measure that displays the maximum score per Category and User ID, you can use a DAX formular that would be something like this:
Max Score per Category and User ID =
CALCULATE(
MAX('Table'[Score]),
ALLEXCEPT('Table', 'Table'[Category], 'Table'[User ID])
)This formula uses the CALCULATE function to calculate the maximum score for the specified Category and User ID. The ALLEXCEPT function is used to remove any filters except for the Category and User ID filters. This ensures that the maximum score is calculated only for the selected Category and User ID.
To use this measure, simply add it to your table or visualization along with the Category and User ID columns. The measure will display the maximum score for each Category and User ID combination.
Feel free to let me know if you have any questions or comments.
Best regards,
Isaac Chavarria
If this post helps, then please consider Accepting it as the solution and giving Kudos to help the other members find it more quickly.
1 Reply
Hi MichaelRensing,
To create a measure that displays the maximum score per Category and User ID, you can use a DAX formular that would be something like this:
Max Score per Category and User ID =
CALCULATE(
MAX('Table'[Score]),
ALLEXCEPT('Table', 'Table'[Category], 'Table'[User ID])
)This formula uses the CALCULATE function to calculate the maximum score for the specified Category and User ID. The ALLEXCEPT function is used to remove any filters except for the Category and User ID filters. This ensures that the maximum score is calculated only for the selected Category and User ID.
To use this measure, simply add it to your table or visualization along with the Category and User ID columns. The measure will display the maximum score for each Category and User ID combination.
Feel free to let me know if you have any questions or comments.
Best regards,
Isaac Chavarria
If this post helps, then please consider Accepting it as the solution and giving Kudos to help the other members find it more quickly.