Forum Discussion
Filtering a table for Max and Min
Hi - I think this should be simple but for some reason won't work for me!
I have a table that look like this. It lists a Folder, the type of file in it and the score for each file. This is an example of one folder called "Students"
| Folder | Type | Score |
| Students | Bank account | 8 |
| Students | Passport | 9 |
| Students | Driver License | 9 |
| Students | Medical Account | 9 |
| Students | CC Number | 8 |
| Students | Tax Number | 9 |
Ultimately, I want to work out the Max and Min score for each folder. In this simplified example there are only a few rows, in real life it could be hundreds.
So I thought I could just remove the Type column to get this:
| Folder | Score |
| Students | 8 |
| Students | 9 |
And then apply the Min and Max on the Score within the visualisation.
It works for Max as 9 is the greatest possible value in the set. For Min though I'm getting
| Folder | Score |
| Students | 7 |
Which I think is because for other folders other than Students the value can be anything from 7 to 9. 7 isn't a value that appears in the Student folder, but it's reporting it as the Min.
How do I get it to report 8 as the Min?
Gussing it's not as simple as I was hoping...
Thanks in advance!
Just to show that without Dax you see a different result...filtered also by type.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
6 Replies
Hi,
I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
If the data model descrbes STAR SCHEMA, something like below, I think it can be approached by using simple MIN and MAX DAX functions.
Please check the below picture and the attached pbix file.Thanks - my model is a bit more complex but is Star Schema. I might have to play around with the DAX functions a bit, don't think I'll be able to just do it from within the visualisation.
Hi naninamu
If you want to display the minimum and maximum scores when your visualization only includes the Folder field, you can simply use the Score column twice—once with the Min aggregation and once with the Max aggregation, like in the example below.
However, if you need to use these measures alongside other dimensions or within another measure, you need to adjust the filter context. You can do this using the ALLEXCEPT function, ensuring that the Min and Max calculations remain specific to each Folder.
Here’s how you can define the measures:
Folder_min = CALCULATE(min('Table'[Score]),ALLEXCEPT('Table','Table'[Folder]))Folder_Max = CALCULATE(max('Table'[Score]),ALLEXCEPT('Table','Table'[Folder]))The pbix is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
That's awesome - I will give this a go later tonight. Thanks!
Just to show that without Dax you see a different result...filtered also by type.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
