Category Archives: PowerPivot DAX

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

DistCalc1

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

HTH!