Tag Archives: PowerPivot

Measuring Distance in Power BI Desktop

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:




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 1st 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.


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).



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.


David Hager

Interesting job postings related to Power BI and VBA

After I wrote about my opinion on Excel and Power BI, I decided to search trends at indeed.com related to Power BI and VBA. First, I put in the search terms Excel, VBA, and PowerPivot. Interestingly, the following shows an unexpected (to me) result.


Prior, to mid-July 2013, there were basically zero job postings based on those criteria. Then, the job postings grew significantly from that point to now. Obviously, this was long after the introduction of PowerPivot (~4 years), so what happened at this time? The following link is to a posting on the SQL Server blog on July 8, 2013.


This was the announcement of the introduction of Power BI. So, why would this announcement causing a “surge” of job postings base on searching for Excel, VBA, and PowerPivot? It is clear (to me) that there was(is) an expectation by the corporate world that Microsoft would implement a programming language (probably VBA) that would eventually be extended throughout PowerPivot (the DataModel object so far) and the rest of the Power BI offerings, creating the ability to build corporate, non-manual solutions using Excel as the hub.

I will be watching closely to see if this “indicator” gives any clues pertaining to the strengthening or weakening of this expectation.

Dynamic Write-Back to Sync PowerPivot Tables with Date Tables created by Power Query

The following technique is a modification to Chris Webb’s excellent Power Query article located at: http://cwebbbi.wordpress.com/2013/11/19/generating-a-date-dimension-table-in-power-query/

I hope that I am not stepping on something that Chris has already done but not published. Anyway, in order to understand what I am presenting here, you will have to read Chris’s article and download his workbook to follow this.

The M script in the workbook uses one cell input tables to store the beginning and end dates to size the date table as desired. It would be nice to size the date table based on the date range of a fact table in the PowerPivot Data Model. This would be a table that is refreshed periodically and the date range of the fact table would change as a result. Instead of manually updating the one cell input tables after visually determining the minimum and maximum values of the column in the fact table containing the dates, the following technique could be used.

The first step is to create two measures to calculate the min and max values:



Then, if you are not a MDX expert like Chris (and I am definitely not one), create a PowerPivot pivot table and add the two measures to the pivot table. Next, convert the pivot table to cube formulas by highlighting it and then select from the menu Pivottable Tools, Analyze, OLAP Tools, Convert To Formulas. If the upper left cell of your pivot table was cell A1, you will see the following CUBE formulas in A1 and A2 respectively:



The next step is to create a cube formula that is independent of cell references, so by replacing A$1 in the 2nd formula with the first formula, we obtain:




This formula will return the smallest date in the fact table. Repeating the steps for the max value gives this formula.


Now, by copying these formulas into the one cell input tables (in the Date Parameters worksheet in Chris’s workbook), a dynamic updating effect for the Data Model is created. When the fact table is updated through PowerPivot with new rows and the Power Query M script is run, the generated data table date range will sync with the date range of the fact table. Once again, thanks to Chris for leading the way in showing the possibilities of Power Query.