Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello everyone!
I have a seemingly simple measure I'm trying to create. I have a table that includes a FROM latitude and longitude coordinates, and a TO latitude and longitude coordinates. Using the latitude and longitude, I'm attempting to calculate the distance between the two using using SUMX. Here is my formula:
Measure = SUMX([table],ACOS(COS(RADIANS(90-[FromLatitude]))) * SUMX([table],COS(RADIANS(90-[ToLatitude]))) + SUMX([table],SIN(RADIANS(90-[FromLatitude]))) * SUMX([table],SIN(RADIANS(90-[ToLatitude]))) * SUMX([table],COS(RADIANS([FromLongitude]-[ToLongitude]))))*3959)
I have also created a calculated column for use with slicers. It works remarkably well. The only thing from the above equation that is different is I'm not using SUMX().
Column =
ACOS(COS(RADIANS(90-[FromLatitude]))) *
COS(RADIANS(90-[ToLatitude]))) +
SIN(RADIANS(90-[FromLatitude]))) * SIN(RADIANS(90-[ToLatitude]))) *
COS(RADIANS([FromLongitude]-[ToLongitude]))))*3959)
Long story short, I'm getting incorrect results when using SUMX(). Since SUMX() calculates on a row level basis, should I not see the same results on each row of the measure as I do with calculated column? Or do I misunderstand the usage of SUMX(). Perhaps my syntax is wrong?
Any guidance is greatly appreciated!
Hi,
maybe you just want the sumx of your entire calculation expression:
SUMX([table],ACOS(COS(RADIANS(90-[FromLatitude]))) * COS(RADIANS(90-[ToLatitude]))) + SIN(RADIANS(90-[FromLatitude]))) * SIN(RADIANS(90-[ToLatitude]))) * COS(RADIANS([FromLongitude]-[ToLongitude]))))*3959))
and this measure on a row level should give you the same result as:
SUMX([table],[Column])
and should give the same result as your calculated column.
Are you sure you evaluate your measure for single rows ?
Thank you for your reply. Using SUMX() for the entire expression seems to have got me closer!
What I'm trying to do, is create a measure so I can total distances, average distances etc. across all our locations. I believe the best way for that using a SUM or SUMX. The calculated column for the distances works just fine for my filtering using slicers. Am I making any sense?
I supect both of you are missing a CALCULATE( ) around your expression -- as to convert the row context from SUMX into the filter context for the expression?
Hi,
in a measure, the CALCULATE() is implicit (around the expression), so you don't have to explicitely write it.
regards,
Dries
@Bitwize_PowerBIyou have it sort of backward. If you reference a measure, the CALCULATE is implicit. It's not that the expression is in a measure. If that were the case we would never have to use CALCULATE in any measure. If you just stick an expression in a SUMX rather than a reference to another measure, there is no implicit CALCULATE. A naked expression needs a CALCULATE in the construction you're using.
Proud to be a Super User!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |