Get MINX and MAXX in measure that HASONEVALUE

[Niiru1]

I currently have 3 columns of data:

Location	Identifier Count	No. of cases	Range
Long stay unit	1	2	2-6
Long stay unit	1	6	2-6
Hospital	2	21	3-21
Hospital	3	4	3-21
Hospital	7	7	3-21
Hospital	1	3	3-21
Hospital	1	13	3-21

What I'm trying to do is create a measure :IF location/identifier count column HASONEVALUE then find the min and max value of cases and put the ranges into a range column

e.g.

Range3 =

VAR A = IF(HASONEVALUE(Sheet1[Outbreak location]),CALCULATE(MINX('Sheet1','Sheet1'[Total number cases - All])))

VAR B = IF(HASONEVALUE(Sheet1[Outbreak location]),CALCULATE(MAXX('Sheet1','Sheet1'[Total number cases - All])))

RETURN

A &"-" & B

[Fowmy · Accepted solution]

@Niiru1 


Your TOTAL NUMBER OF CASEAS ALL  is in text format, change. it to whole number 

Try the followin measure:

Range3 = 

var _loc = MAX(Sheet1[Outbreak location])
var a = 
CALCULATE(
    MIN(Sheet1[Total number cases - All]),
    FILTER(ALLSELECTED(Sheet1),
    Sheet1[Outbreak location] = _loc
    )
)
var b = 
CALCULATE(
    max(Sheet1[Total number cases - All]),
    FILTER(ALLSELECTED(Sheet1),
    Sheet1[Outbreak location] = _loc
    )
)
return
IF( HASONEVALUE(Sheet1[Outbreak location]), a&" - "&b)

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn