topic Re: Max of a filtered measure in DAX Commands and Tips

Max of a filtered measure

rrhutch — Wed, 22 Feb 2023 19:51:56 GMT

I have a Measure that was created called miDynamicDistance that calculated the miles between two latitudes and longitudes based on a zip code a user chooses.

I then have that measure in the filter pane on a table visual. For an example for this post, I have the measure in the filter pane set to is less than 200 to filter everyone that is less than 200 miles.

Since that is in the filter pane and not some place like a slicer, I want to display on the page either a card or another kind of visual that says show the max mileage value of the filtered measure. So in this situation, it would display 200.

Re: Max of a filtered measure

FreemanZ — Thu, 23 Feb 2023 02:22:40 GMT

hi rrhutch

how is your measure [miDynamicDistance] defined?

Re: Max of a filtered measure

rrhutch — Thu, 23 Feb 2023 23:50:04 GMT

miDynamicDistance =

var Lat1 = MIN(usazipcodes[lat])

var Lng1 = MIN(usazipcodes[lng])

var Lat2 = MIN(dADDRESSES[USA_lat])

var Lng2 = MIN(dADDRESSES[USA_lng])

---- Algorithm here -----

var P = DIVIDE( PI(), 180 )

var A = SIN(Lat2*P)*SIN(Lat1*P)+COS(Lat2*P)*COS(Lat1*P)*COS((Lng1*P)-(Lng2*P))

var final = 3959 * ACOS(A)

return final

Re: Max of a filtered measure

FreemanZ — Fri, 24 Feb 2023 00:19:28 GMT

hi rrhutch

MIN or MAX is used to capture the value for a measure in a certain context. The expectation is to show the MAX value of a measure, not sure in what context does the measure return its max value. You are the best to know. Anyway, it is worthy trying to replace all or some of the MIN to MAX in your code.

Re: Max of a filtered measure

rrhutch — Wed, 01 Mar 2023 02:40:36 GMT

It did not. I was able to get at it through a different direction. I created a table that I use for a slicer. I then use the value of the slicer in the duplicate for the formula I posted that is a conditional statement returning a Y or N to filter the table. That then allows it to display on the page the filtered value and filters the table.

inRange =

var Lat1 = MIN(usazipcodes[lat])

var Lng1 = MIN(usazipcodes[lng])

var Lat2 = MIN(dADDRESSES[USA_lat])

var Lng2 = MIN(dADDRESSES[USA_lng])

---- Algorithm here -----

var P = DIVIDE( PI(), 180 )

var A = SIN(Lat2*P)*SIN(Lat1*P)+COS(Lat2*P)*COS(Lat1*P)*COS((Lng1*P)-(Lng2*P))

var DIST = 3959 * ACOS(A)

var FINAL = IF(DIST<=MAX(filterDistanceMiles[DistMiles]),"Y","N")

return final