Category Archives: PowerPivot DAX

Excel Super Links 1-150

This time you really hit the jackpot! >850 Excel and Power BI Links, all of the best articles. Make this the cornerstone of your Excel resource library. Download the Word document here.

Excel Super Links 1-150

 

 

Advertisements

Adding Multiple DAX Measures to Non-PowerPivot Versions of #Excel using an User-Defined VBA Function

 

 

In this article,

http://dailydoseofexcel.com/archives/2017/07/10/look-ma-no-powerpivot/

Jeff Weir pointed to a video made by Mike Girvin about adding measures to non-PowerPivot versions of Excel (link below)

https://www.youtube.com/watch?v=FVVK-8QZC1M&t=422s

Mike demonstrated how measures can be added to a data model in these “disabled” version through pivot table options. Please view this video to see how Mike did it.

The link to the working file for this video will be referred to in this article (Thanks, Mike!).

https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1269Finished.xlsx

You can download this file and reproduce the technique presented here.

Although it is not well-known, Microsoft started at Excel version 2016 (Office 365) marketing versions that do not have PowerPivot capability. For details on this, see:

https://blogs.office.com/en-us/2015/09/18/new-ways-to-get-the-excel-business-analytics-features-you-need/

So, this article is dedicated to those who purchased non-PowerPivot versions of Excel 2016, although the technique presented here will work on any version of Excel 2013 or greater.

Jeff Weir mentioned in his article that since some Excel 2016 versions did not have the full-blown PowerPivot capability, and that VBA could be used to build a user interface to the data model. Well, I have not created a UI, but I have made a way to add multiple measures to the data model using an user-defined function. The code for the VBA function is shown below. To use this, add astandard module in the VBE and then save the workbook as .xlsm. Then, copy/paste the code into the module.

 

Function AddMeasure(TableName As String, MeasureName As Range)

Application.Volatile False

With ActiveWorkbook.Model

For Each mCell In MeasureName

mFormat = mCell.Offset(0, 2).Value

.ModelMeasures.Add mCell.Value, .ModelTables(TableName), mCell.Offset(0, 1).Value, _

Switch(mFormat = “Boolean”, .ModelFormatBoolean, mFormat = “Currency”, .ModelFormatCurrency, _

mFormat = “Date”, .ModelFormatDate, mFormat = “DecimalNumber”, .ModelFormatDecimalNumber, _

mFormat = “General”, .ModelFormatGeneral, mFormat = “PercentageNumber”, .ModelFormatPercentageNumber, _

mFormat = “ScientificNumber”, .ModelFormatScientificNumber, mFormat = “WholeNumber”, .ModelFormatWholeNumber), _

mCell.Value

Next

End With

AddMeasure = “DONE”

End Function

Then, place the following information in the range D10:F14.

 

NetRevenue SUMX(fTransactions,ROUND(RELATED(dProducts[Price])*fTransactions[Units]*(1-fTransactions[Discount]),2)) DecimalNumber
MaxRevenue MAXX(fTransactions,ROUND(RELATED(dProducts[Price])*fTransactions[Units]*(1-fTransactions[Discount]),2)) PercentageNumber
MinRevenue MINX(fTransactions,ROUND(RELATED(dProducts[Price])*fTransactions[Units]*(1-fTransactions[Discount]),2)) Currency
AverageRevenue AVERAGEX(fTransactions,ROUND(RELATED(dProducts[Price])*fTransactions[Units]*(1-fTransactions[Discount]),2)) General
CountOfRevenue COUNTAX(fTransactions,ROUND(RELATED(dProducts[Price])*fTransactions[Units]*(1-fTransactions[Discount]),2)) General

 

To run this as a worksheet formula, type this formula in any cell.

=AddMeasure(“fTransactions”,D10:D14)

This will add the 5 measures to the data model, as shown in the Pivot Table Fields list.

AddMeasure2

After the 5 measures are added to the pivot table, the resulting pivot table will look like this.

AddMeasure5

Of course, the DAX formulas to be added have to return valid results, or the procedure will fail.

This powerful technique is yet another reason why users should not completely abandon Excel for Power BI desktop, as discussed in this article at powerpivotpro.com

https://powerpivotpro.com/2017/09/excel-is-still-the-best-tool-for-teaching-dax/

And, this technique does not HAVE to be run from a UDF, but I am still amazed that it can. I am sure that you will find this very useful.

#Excel Super Links #75 – shared by David Hager

 

Getting A Handle On Userforms [VBA]

https://colinlegg.wordpress.com/2016/05/06/getting-a-handle-on-userforms-vba/

Excel VBE Options

http://www.excelgaard.dk/Lib/VBE/Options/

Excel Solution: Who Should Sit Where?

http://datascopic.net/seating/?doing_wp_cron=1497911478.3255810737609863281250

Basket Analysis in DAX

http://www.daxpatterns.com/basket-analysis/

Excel Short & Sweet Tip #6 (Shuffling a String) by David Hager

https://dhexcel1.wordpress.com/2017/04/25/excel-short-sweet-tip-6-shuffling-a-string-by-david-hager/

 

Generating Random Initials with DAX in Power BI by David Hager

After publishing a short article about generating random initials in Excel

https://dhexcel1.wordpress.com/2017/05/18/excel-short-and-sweet-tip-14-generating-random-initials-by-david-hager/

I wanted to see if the same formula worked in Power BI. When I entered the aforementioned formula into a calculated column in Power BI desktop, I got an error message. After some research, I discovered that was no CHAR function in the DAX function reference. Then, I remembered that Chris Webb showed examples of the new UNICHAR function at the following link.

https://blog.crossjoin.co.uk/2017/04/11/the-dax-unichar-function-and-how-to-use-it-in-measures-for-data-visualisation/

And, Chris discovered that the UNICHAR function was completely undocumented and was probably introduced in the April 2017 update. So, I changed my random initials formula using UNICHAR instead of CHAR, and it worked.

=UNICHAR(INT(RAND()*26)+65)&UNICHAR(INT(RAND()*26)+65)&UNICHAR(INT(RAND()*26)+65)

Now, you can use this in your Power BI projects.

It is still a mystery how DAX could be around for 8 years and not have the CHAR function (or its equivalent).

 

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!