DAX to return Highest and Lowest number in a column

[Anonymous]

Hi team,

I have a table something like this:

Table Name: Projects

Project Name	Rating
P1	0
P2	3
P3	-2
P4	-2
P2	-3
P5	2
P3	2
P1	1
P2	-1
P4	1
P5	1
P1	-1
P1	-3
P1	2
P1	-2
P1	3

I want to query it to get the (i) Highest positive ratingfor each project and (ii) lowest negative rating for each project

Eg: For Project P1, it should be 3 and-3

Thanks

[Anonymous · Accepted solution]

Hi @Anonymous ,

Try this.

max = 
var maxrate = CALCULATE(MAX('Table'[Rating]),ALLEXCEPT('Table','Table'[Project Name]))
return
IF(maxrate>0,maxrate,0)

min = 
var minrate = CALCULATE(MIN('Table'[Rating]),ALLEXCEPT('Table','Table'[Project Name]))
return
IF(minrate<0,minrate,0)

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[mahoneypat]

Please try this expression

Highest Positive =
CALCULATE ( MAX ( Project[Rating] ), Projects[Rating] > 0 )

Lowest Negative =
CALCULATE ( MIN ( Project[Rating] ), Projects[Rating] < 0 )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[lbendlin]

Highest = 
var p=SELECTEDVALUE(Projects[Project Name])
return calculate(max(Projects[Rating]),filter(all(Projects),Projects[Project Name]=p))

Lowest = 
var p=SELECTEDVALUE(Projects[Project Name])
return calculate(min(Projects[Rating]),filter(all(Projects),Projects[Project Name]=p))

You may need to replace ALL() with a context modifier that suits your scenario.

[edhans]

MIN and MAX will do.

MIN(Table[Field])

MAX(Table[Field])

If you are trying to show the MIN/MAX without being impacted by the Project Name, use this:

Measure =
MAXX(
    ALL(TableName),
    TableName[FieldName]
)

[Anonymous]

Thanks for your answer.
I understand we have MIN and MAX, but that will not take the positive and negative into consideration.
Eg: if I have Ratings as -1 and -2 for a project, and if I query for highest Positive rating, it will return -1 but actually there are no positive ratings

[lbendlin]

so what do you want to return in such a case? Zero or nothing?