Measuring Distance in Power BI Desktop

Please contact me with any Excel Project work you may have. Written 10/28/2020

Email address: dhExcel1@gmail.com

Among the many new BI features introduced with the recent release of the free version of the Microsoft Power BI desktop is the “new” trigonometric DAX functions such as SIN and COS. Of course, these functions have been around forever in Excel, but for some reason were not included in the first two major releases of Power Pivot. So, now they are available to the DAX language (and also in Excel 2016, if you have a spare computer handy to use in the preview version), and as such can be used to calculate straight-line distances between two points on the Earth as defined by their latitude and longitude coordinates. Also, in the formula, the constant PI()/180 is required a number of times, so the implementation of the new DAX variable comes in very handy here. In Power BI, if you add a new measure named PI_D180 as equal to PI()/180 (0.0174532925199433), you can use that measure in your DAX formula for distance, as shown below.

Distance = ACOS(SIN(Distance[Latitude_1]*[PI_DIV180])*SIN(Distance[Latitude_2]*[PI_DIV180])+COS(Distance[Latitude_1]*[PI_DIV180])*COS(Distance[Latitude_2]*[PI_DIV180])*COS((Distance[Longitude_2]*[PI_DIV180])-(Distance[Longitude_1]*[PI_DIV180])))*3959

Hope that you find this useful!

The DAX ENDSWITHX Function Equivalent

I remembered the excellent articles written by Rob Collie at powerpivotpro.com on the CONTAINSX function equivalent:

http://www.powerpivotpro.com/2014/01/containsx-finding-if-a-value-in-table-1-has-a-matching-value-in-table-2/

and

http://www.powerpivotpro.com/2014/01/containsx-revisited-what-was-the-match/

I decided that it was time to extend this solution to search at the end of a string. In particular, I started to look at my bank statements and I had a need to filter all payments that were a fixed monthly payment. So, Rob’s formula shown below worked great for this:

= SUMX (MList,FIND (MList[MonthlyPay],Payments[Type],, 0 ) )> 0

where MList is a linked list [MonthlyPay] of the search strings those fixed payments, and Payments[Type] is a column in the table for my bank statement. I was able to use this Boolean result to filter my pivot table to afford the desired result.

The payment descriptions are space delimited, and I also wanted to return all searches that look at only the last string in the space delimited string. The following formula accomplishes this.

 = SUMX( MList, FIND( MList[MonthlyPay], MID( [Type], FIND( “~”, IFERROR( SUBSTITUTE( Payments[Type], “”, “~”, LEN(Payments[Type]) -LEN(SUBSTITUTE(Payments[Type],””,””)) ), “~” ), , 1 ), 255 ), , 0 ) ) >0

The internal SUBSTITUTE function creates a string with only the last space in the original string replaced with a “~”, the position which can then be located by the FIND function.

In cases where it may be necessary to search the end of a string that uses another delimiter, it may be desirable to automatically change the delimiter in this formula. By using a one cell linked table, the value in Delimiter[delim] can be changed in the table and then updated in the DataModel. The result is shown in the formula below.

= SUMX( MList, FIND( MList[MonthlyPay], MID( [Type], FIND( “~”, IFERROR( SUBSTITUTE( Payments[Type], VALUES(Delimiter[delim]), “~”, LEN(Payments[Type]) -LEN( SUBSTITUTE( Payments[Type], VALUES(Delimiter[delim]), “” ) ) ), “~” ), , 1 ), 255 ), , 0 ) ) >0

Although I cannot claim to know every DAX formula ever made, I am fairly certain that this represents the 1^st example of parameterizing a text character in a DAX formula.

BTW, I apologize for the brevity of this article and the lack of any pictures showing visually what I am describing.

It is possible that I drifted off of the subject of an actual ENDSWITHX function equivalent, so to correct that, I offer the following formula.

= SUMX( MList, FIND( MList[MonthlyPay], RIGHT([Type],MAX(MList[LenVal])), , 0 ) ) >0

where a column in the MList table [LenVal] uses the formula  =LEN([MonthlyPay]) to calculate the number of text characters for each search string. The maximum value is used with the RIGHT to return a searchable string from the end of the [TYPE] string. If the [type] string has is smaller  than the max len value of the search string, it returns the shorter string without producing an error.

HTH!

Returning the First Temperature in PowerPivot

Here in Southeast Texas, it has been rather cool this year. In fact, as of this writing (May 25), there has not been a day yet at 90°F or above. I thought that this was pretty unusual, but I could not (easily) find a temperature dataset on the Internet to prove it. However, I thought that I had such a dataset stored away somewhere in my computer, but I could not find it (due to poor file naming) until this weekend. It was missing a few years of data in the middle of the dataset, but I had enough to test out my theory After I loaded the data into PowerPivot, it was pretty easy to come up with a DAX measure that would give me the first day of the year at 90°F or above.

First90:=CALCULATE(MIN(Table1[Date]),Table1[Max TemperatureF]>=90)

Here is a picture of the pivot table with that measure.

 

If you closely you can see the years that are missing data. Well, since that went so smoothly, I figured why not look up the first day of freezing for each year. So, my first attempt used the following formula:

First32:=CALCULATE(MIN(Table1[Date]),Table1[Min TemperatureF]<=32,MONTH(Table1[Date])>6)

I added the criteria MONTH(Table1[Date])>6 to look up only the first freezing temperature that was at the end of the year. The results are shown below:

 

Now, the missing data was easy to see (1979-1985), since no result was returned for the First90 measure and a bogus result was retuned for the First32 measure. However, some of the legitimate data also showed no result for the First32 measure (1986, 1987, 1994, and 2002). I had failed to realize that the first freeze of the winter may not be in the year where the winter started. In order to find a solution that did work the way I intended, to would have to take into account that the first freeze of the winter season could also be in the next calendar year.

I am not going to bore you with the details of the numerous times that my attempts to solve this problem ended up in failure. Also, I would not want to bias your attempts to come up with a better solution than I have, since I am sure that there is another one out there. The key to the solution is that I needed to create a custom time period to call “winter”, and it had to work in a pivot table with a year row field. Here is my solution:

I created two calculated fields, one for the dates at the beginning of the winter and one for the dates at the end of the winter. Realize that these may not work for datasets from anywhere in the world (and especially in the Southern Hemisphere).

[BeginYear]=DATE(YEAR(Table1[Date]),7,1)

[EndYear]=DATE(YEAR(Table1[Date])+1,6,30)

I then created another calculated column for the desired DAX formula (I could not get this to work as a measure, so feel free to extend this).

[FirstFreeze]=CALCULATE(MIN(Table1[Date]),ALL(Table1),Table1[Min TemperatureF]<=32,DATESBETWEEN(Table1[Date],Table1[BeginYear],Table1[EndYear]))

The results in the [FirstFreeze] column give a result for every day with valid data, so the field setting has to be set to Average to return the desired value. Further, this field cannot be aggregated as Average in the pivot table when the calculated column has a Date datatype, so in order to use it must be changed to decimal type in the DataModel and then back to Date by use the filed settings in the pivot table (hope that is clear) J.

When done, the resulting pivot table looks like this:

 

Now that you can see that the years that were missing the first freezing temperatures in the initial attempt now have populated.

HTH!

David Hager

My Favorite Links # 7