Filtering a table for Max and Min

[naninamu]

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!

[naninamu]

Sorry @Ritaf1983  - just had a closer look. What is the max of score column representing?

[Ritaf1983 · Accepted solution]

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.

[naninamu]

That's awesome - I will give this a go later tonight. Thanks!

[Ritaf1983]

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.

[naninamu]

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. 

[Jihwan_Kim]

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.