topic DAX - Dynamic Measure based on slicer selection in DAX Commands and Tips

DAX - Dynamic Measure based on slicer selection

MischaT — Sat, 04 Dec 2021 17:56:35 GMT

Hello there!

I need some guidance again please 🙂

I am using the following measure to calculate distance:

EVENT TABLE

ID	PLACE	TYPE	LAT	LONG
01	PRETORIA	HAILSTORM	-29,33841	27,99473
02	BLOEM	SNOW	-25,52735	28,04517
03	POTCH	FLOODING	-29,33841	31,28885

WHAT I WOULD LIKE TO DO:

I would like to create a ID Slicer for the clients.

Based on the selected ID on the slicer, I would like the measure to use the selected ID's LAT and LONG and calculate the distance from the selected ID.

Any suggestions?

Would really apprecaite any assistance

Kind Regards, M

Re: DAX - Dynamic Measure based on slicer selection

AlB — Mon, 06 Dec 2021 15:15:53 GMT

Hi MischaT

The distance between the selected ID and where?

You can read the selected ID coordinates simply by using:

VAR IDLat = SELECTEDVALUE(EventTable[Lat])

VAR IDLong = SELECTEDVALUE(EventTable[Long])

and then use that in hte formula you already have

Please accept the solution when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Re: DAX - Dynamic Measure based on slicer selection

VahidDM — Sun, 05 Dec 2021 00:22:52 GMT

Hi MischaT

As AlB mentioned, it's not clear how do you want to calculate a distance? do you want to replace the selected ID Lat and Long with Lat1 and Long 1 in your formula or Lat 2 and Long 2??? I think you want to replace that with Lat and Long 2!

Can you share a sample of DB_ISITES table?

BTW, if you select an ID on a slicer that will filter the EVENT table and then Lat1 and Long1 will return the Lat and Long of the selected ID.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

Re: DAX - Dynamic Measure based on slicer selection

MischaT — Mon, 06 Dec 2021 10:24:04 GMT

Hi @AlB,

Thank you for the response.

I have tried using the SELECTEDVALUE, but unfortunately it still doesn't work.

I would like to calculate the distance from the selected ID to each of the ISITES.

When an ID is then selected, the distance calculation then does not work correctly

VahidDM, DB_ISITES Table:

SITE NAME	TYPE	PROV	LAT	LONG
Tropical	Bar	GP	-29,33850	27,99473
Sunset	Casino	KZN	-29,33840	31,28890

I'm very confused as to what is going wrong

Thank you!

Re: DAX - Dynamic Measure based on slicer selection

AlB — Mon, 06 Dec 2021 15:20:23 GMT

MischaT

Get the lat, long of the site in the same fashion:

VAR IDLat = SELECTEDVALUE(EventTable[Lat])

VAR IDLong = SELECTEDVALUE(EventTable[Long])

VAR SiteLat = SELECTEDVALUE(SitesTable[Lat])

VAR SiteLong = SELECTEDVALUE(SitesTable[Long])

then apply your formulae.

If this does not work I'd probablyn need a mock pbix reproducing the issue so that I can see exactly what you are doing. You have to share the URL to the file hosted elsewhere: Dropbox, Onedrive... or just upload the file to a site like tinyupload.com (no sign-up required).

Please accept the solution when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.