Category Archives: PowerPivot DAX

#Excel Super Links #152

 

Bulk Download of Power BI Custom Visual Sample Files

https://datachant.com/2017/09/15/bulk-download-power-bi-custom-visual-sample-files/

Using KEEPFILTERS in DAX

http://www.sqlbi.com/articles/using-keepfilters-in-dax/

Highlight Cells Referenced in Excel Formulas

https://www.myonlinetraininghub.com/highlight-cells-referenced-excel-formulas

Power Query: In Your Face(book)

https://www.sumproduct.com/blog/article/power-query-in-your-facebook

Get Latest Storm Information in #Excel Using Only An Address and Storm Name

https://dhexcel1.wordpress.com/2017/09/18/get-latest-storm-information-in-excel-using-only-an-address-and-storm-name/

 

Advertisements

#Excel Super Links #150 – Special Edition

 

To celebrate the 150th issue of Excel Super Links, here are the links to all of my content at dhExcel1.wordpress.com. Although there are no descriptions, they are fairly self-documenting.

Enjoy!

https://dhexcel1.wordpress.com/2017/09/12/using-excel-to-find-how-far-the-storm-is-from-your-location/

https://dhexcel1.wordpress.com/2017/09/07/adding-multiple-measures-to-non-powerpivot-versions-of-excel-using-an-user-defined-vba-function/

https://dhexcel1.wordpress.com/2017/09/01/excel-impossibly-easy-2-change-sheet-tab-color-with-a-user-defined-worksheet-formula/

https://dhexcel1.wordpress.com/2017/08/26/returning-a-list-of-formulas-based-on-a-specific-excel-function-by-david-hager/

https://dhexcel1.wordpress.com/2017/08/20/excel-impossibly-easy-1-return-a-1d-array-from-non-contiguous-native-3d-ranges/

https://dhexcel1.wordpress.com/2017/08/17/documenting-formulas-in-an-excel-range-using-the-formulatext-isformula-and-textjoin-functions-by-david-hager/

https://dhexcel1.wordpress.com/2017/08/15/extracting-ingredients-from-an-excel-list-by-cost-category-by-david-hager/

https://dhexcel1.wordpress.com/2017/08/10/excel-short-and-sweet-tip-28-jazzing-up-a-star-rating-by-using-the-textjoin-by-david-hager/

https://dhexcel1.wordpress.com/2017/08/08/excel-super-links-1-120-600-excel-and-power-bi-links/

https://dhexcel1.wordpress.com/2017/08/07/excel-super-links-91-120-great-collection-of-excel-and-power-bi-links/

https://dhexcel1.wordpress.com/2017/08/05/creating-a-priority-list-with-excel-by-david-hager/

https://dhexcel1.wordpress.com/2017/08/04/list-of-priority-items-from-another-list-using-excel-textjoin-function-by-david-hager/

https://dhexcel1.wordpress.com/2017/08/03/creating-a-set-of-custom-instructions-using-the-excel-textjoin-function-by-david-hager/

https://dhexcel1.wordpress.com/2017/07/25/run-your-power-query-m-code-procedures-in-excel-worksheet-cells-by-david-hager/

https://dhexcel1.wordpress.com/2017/07/20/storing-and-running-m-code-from-excel-worksheet-cells-by-david-hager/

https://dhexcel1.wordpress.com/2017/07/13/lookup-a-bible-verse-in-any-language-using-excel-by-david-hager/

https://dhexcel1.wordpress.com/2017/07/12/excel-short-and-sweet-tip-26-showing-an-userform-with-a-worksheet-udf-by-david-hager/

https://dhexcel1.wordpress.com/2017/07/11/excel-getting-the-sunrise-and-sunrise-times-from-an-address-by-david-hager/

https://dhexcel1.wordpress.com/2017/07/10/getting-the-latest-earthquake-alert-using-the-webservice-and-filterxml-functions-in-excel-by-david-hager/

https://dhexcel1.wordpress.com/2017/07/07/excel-short-and-sweet-tip-25-playing-a-random-sound-with-a-worksheet-udf-by-david-hager/

https://dhexcel1.wordpress.com/2017/07/07/excel-super-links-61-90-250-excel-and-bi-links/

https://dhexcel1.wordpress.com/2017/07/05/conditional-formatting-gem-in-excel-highlight-the-n-closest-values-to-the-mean-of-a-range-in-a-filtered-list-by-david-hager/

https://dhexcel1.wordpress.com/2017/07/04/using-conditional-formatting-in-excel-to-highlight-the-n-closest-values-to-the-mean-of-a-range-by-david-hager/

https://dhexcel1.wordpress.com/2017/07/03/lookup-a-bible-verse-using-excel-wo-vba-by-david-hager/

https://dhexcel1.wordpress.com/2017/07/01/automating-word-and-powerpoint-from-excel-with-a-worksheet-udf-by-david-hager/

https://dhexcel1.wordpress.com/2017/06/26/excel-get-audio-result-for-any-excel-formula-or-cell-with-worksheet-udf-by-david-hager/

https://dhexcel1.wordpress.com/2017/06/25/excel-conditional-format-rows-in-list-1-that-are-not-in-list-2-by-david-hager/

https://dhexcel1.wordpress.com/2017/06/24/excel-conditional-format-rows-in-one-list-that-are-in-another-list-by-david-hager/

https://dhexcel1.wordpress.com/2017/06/23/get-information-from-excel-about-the-august-2017-total-eclipse-by-david-hager/

https://dhexcel1.wordpress.com/2017/06/22/creating-dependent-lists-from-a-column-lookup-in-an-excel-list-by-david-hager/

https://dhexcel1.wordpress.com/2017/06/20/excel-sparklines-display-last-n-values-by-david-hager/

https://dhexcel1.wordpress.com/2014/05/04/excel-did-you-know-tip5/

https://dhexcel1.wordpress.com/2017/06/19/excel-short-and-sweet-tip-23-open-windows-file-explorer-with-worksheet-udf-by-david-hager/

https://dhexcel1.wordpress.com/2017/06/17/excel-using-multiple-lists-with-data-advanced-filter-by-david-hager/

https://dhexcel1.wordpress.com/2017/06/13/excel-exciting-new-features-using-a-worksheet-udf-to-modify-shapes-on-a-worksheet-by-david-hager/

https://dhexcel1.wordpress.com/2017/06/09/esl31-60-a-valuable-collection-of-excel-and-power-bi-links-to-great-excel-and-modern-excel-techniques-vol-2-by-david-hager/

https://dhexcel1.wordpress.com/2017/06/08/excel-short-and-sweet-tip-22-move-vba-module-copy-to-another-workbook-by-david-hager/

https://dhexcel1.wordpress.com/2017/06/07/excel-exchange-rate-udf-with-symbol-lookup-by-david-hager/

https://dhexcel1.wordpress.com/2017/06/06/excel-short-and-sweet-tip-21-remove-unused-custom-formats-by-david-hager/

https://dhexcel1.wordpress.com/2017/06/05/excel-short-and-sweet-tip-20-create-vba-procedure-list-by-david-hager/

https://dhexcel1.wordpress.com/2017/06/03/creating-an-excel-translator-by-david-hager/

https://dhexcel1.wordpress.com/2017/06/02/using-excel-to-make-a-round-robin-tournament-schedule-by-david-hager/

https://dhexcel1.wordpress.com/2017/06/01/revisiting-using-the-excel-textjoin-function-to-return-unique-items-from-a-3d-range-by-david-hager/

https://dhexcel1.wordpress.com/2017/06/01/excel-using-power-query-to-return-all-words-from-a-list-of-letters-including-wildcards-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/28/excel-short-and-sweet-tip-19-excel-can-spell-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/27/excel-short-and-sweet-tip-18-toggle-formatting-on-worksheet-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/26/excel-short-and-sweet-tip-16-returning-a-letter-grade-based-on-a-normal-grading-scale-without-lookup-table-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/25/excel-project-tracking-workbook-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/24/excel-short-sweet-tip-16-multiple-delimiters-with-textjoin-for-custom-formatting-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/23/excel-native-3d-ranges-with-the-textjoin-function-plus-bonus-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/23/excel-udf-using-google-api-to-return-the-elevation-of-an-address-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/22/excel-creating-a-list-of-option-expiration-dates-and-triple-witching-dates-with-excel-formulas-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/21/excel-short-and-sweet-tip-15-using-textjoin-with-non-contiguous-ranges-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/20/copy-excel-chart-as-a-enhanced-metafile-picture-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/19/generating-random-initials-in-power-bi-by-david-hager/

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

https://dhexcel1.wordpress.com/2017/05/17/excel-modifying-control-chart-data-to-remove-outliers-with-excel-formulas-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/16/excel-short-and-sweep-tip-13-keyboard-dfarot-unique-technique-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/15/excel-origin-of-sparklines-linechart-vba-user-defined-function-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/14/excel-finding-and-visualizing-the-last-record-in-a-table-based-on-criteria-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/13/excel-short-and-sweet-tip-12-documenting-formulas-and-highlighting-those-formulas-with-conditional-formatting-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/12/excel-creating-a-udf-with-the-vba-environ-function-and-using-it-to-make-a-table-of-environmental-variables-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/11/excel-a-valuable-collection-of-excel-links-to-great-excel-and-modern-excel-techniques-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/10/excel-building-a-frequency-summary-table-based-on-an-excel-list-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/09/excel-short-and-sweep-tip-11-most-frequent-item-in-column-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/08/excel-a-model-using-the-convert-function-containing-categories-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/07/excel-magic-consolidator-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/06/excel-short-and-sweep-tip-10-add-custom-autocorrect-with-vba-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/05/excel-using-conditional-formatting-to-highlight-cells-that-contain-array-formulas-using-the-formulatext-function-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/04/excel-short-sweet-tip-9-get-ip-address-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/03/excel-generating-a-random-sampling-from-a-list-using-vba-and-the-textjoin-function-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/01/excel-short-sweet-tip-8-vba-function-to-return-zodiac-sign-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/29/excel-worksheet-udf-that-adds-a-comment-to-any-cell-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/28/excel-short-sweet-tip-7highlighting-external-links-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/25/excel-using-conditional-formatting-to-highlight-3d-formulas-with-defined-names-by-david-hager/

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

https://dhexcel1.wordpress.com/2017/04/24/excel-using-conditional-formatting-to-highlight-cells-containing-native-3d-formulas-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/23/excel-short-sweet-tip-5-hiding-error-triangles-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/21/excel-short-sweet-tip-4/

https://dhexcel1.wordpress.com/2017/04/20/excel-short-and-sweep-tip-3-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/19/excel-modifying-shapes-from-an-udf-in-a-worksheet-cell-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/18/excel-short-sweet-tip-2/

https://dhexcel1.wordpress.com/2017/04/17/excel-short-and-sweet-tip-1-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/15/excel-combining-and-refining-a-static-filter-list-filter-criteria-udf-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/14/excel-using-advanced-filter-with-a-custom-list-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/11/excel-vba-create-a-table-of-file-locations-and-urls-for-your-favorites-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/10/excel-identifying-if-a-string-is-the-anagram-of-another-string-using-the-textjoin-function-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/08/mine3d-for-excel-an-excel-based-game-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/07/using-the-subtotal-function-in-excel-to-aggregate-values-from-multiple-filtered-lists-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/06/using-excel-vba-to-create-a-filter-criteria-function-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/04/multiple-lookup-values-in-a-single-cell-withwithout-duplicates-using-only-excel-formulas-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/03/excel-shift-calendar-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/01/generating-a-sorted-unique-array-in-excel-using-only-formulas-by-david-hager/

https://dhexcel1.wordpress.com/2017/03/30/excel-creating-a-list-made-up-of-formulas-from-a-filtered-list-by-david-hager/

https://dhexcel1.wordpress.com/2017/03/30/excel-filtered-list-using-a-calculated-column-to-count-occurrence-number-by-david-hager/

https://dhexcel1.wordpress.com/2017/03/25/using-the-excel-advanced-filter-to-a-list-having-internal-numbers-in-a-string-by-david-hager/

https://dhexcel1.wordpress.com/2017/03/23/using-conditional-formatting-to-highlight-unique-items-in-an-excel-filtered-list-by-david-hager/

https://dhexcel1.wordpress.com/2017/03/13/chapter-on-conditional-formatting-in-excel-by-david-hager-a-blast-from-the-past/

https://dhexcel1.wordpress.com/2017/03/11/my-collection-of-excel-tips-by-david-hager/

https://dhexcel1.wordpress.com/2017/03/02/using-the-choose-and-aggregate-functions-to-apply-conditional-formatting-to-a-filtered-list-in-excel-by-david-hager/

https://dhexcel1.wordpress.com/2013/12/19/highlighting-top-n-values-in-a-filtered-table-column-using-conditional-formatting/

https://dhexcel1.wordpress.com/2017/02/07/calculating-aggregation-for-internal-numbers-from-strings-in-a-range-by-david-hager/

https://dhexcel1.wordpress.com/2017/02/05/using-the-excel-textjoin-function-to-extract-an-internal-numeric-string-from-a-string-by-david-hager/

https://dhexcel1.wordpress.com/2017/02/03/averaging-of-scientific-results-in-excel/

https://dhexcel1.wordpress.com/2017/01/25/charting-data-with-formula-links-in-excel-by-david-hager/

https://dhexcel1.wordpress.com/2017/01/08/creating-a-unique-delimited-string-from-an-excel-filtered-list-by-using-the-textjoin-function-by-david-hager/

https://dhexcel1.wordpress.com/2017/01/07/creating-an-excel-table-of-components-by-product-from-a-list-using-the-textjoin-function-by-david-hager/

https://dhexcel1.wordpress.com/2017/01/03/creating-a-unique-delimited-string-from-a-delimited-string-excel-formula-method-by-david-hager/

https://dhexcel1.wordpress.com/2017/01/02/using-the-excel-textjoin-function-to-return-unique-items-in-a-one-cell-delimited-string-from-a-2d-and-3d-range-by-david-hager/

https://dhexcel1.wordpress.com/2016/11/29/xlcube-an-excel-game/

https://dhexcel1.wordpress.com/2015/08/31/power-bi-help-for-excel/

https://dhexcel1.wordpress.com/2015/08/16/measuring-distance-in-power-bi-desktop/

https://dhexcel1.wordpress.com/2015/06/21/the-dax-endswithx-function-equivalent/

https://dhexcel1.wordpress.com/2015/03/31/bingo/

https://dhexcel1.wordpress.com/2014/09/13/go-to-special-for-browser-copypaste-to-excel/

https://dhexcel1.wordpress.com/2014/07/07/calculating-the-nth-best-performance-unique-individuals-with-excel-and-powerpivot-2/

https://dhexcel1.wordpress.com/2014/06/13/using-excel-3d-formulas-on-filtered-lists/

https://dhexcel1.wordpress.com/2014/06/09/excel-3d-easy-as-1-2-3-and-a-b-c-and-others/

https://dhexcel1.wordpress.com/2017/01/07/archive-of-excel-experts-e-letter-by-david-hager/

https://dhexcel1.wordpress.com/2014/05/25/returning-the-first-temperature-in-powerpivot/

 

 

#Excel Super Links #148 – Shared by David Hager

 

Using Custom Lists in Excel

https://exceloffthegrid.com/create-an-custom-list/

Power Query: a Function Function

https://www.sumproduct.com/blog/article/power-query-a-function-function

Excel Pivot Chart Drill Down Buttons

https://www.myonlinetraininghub.com/excel-pivot-chart-drill-down-buttons

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

https://dhexcel1.wordpress.com/2017/09/07/adding-multiple-measures-to-non-powerpivot-versions-of-excel-using-an-user-defined-vba-function/

Advanced Filter Magic

https://dhexcel1.wordpress.com/2017/06/17/excel-using-multiple-lists-with-data-advanced-filter-by-david-hager/

 

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 #147 – Shared by David Hager

 

Introducing the Timeline Storyteller custom visual for Microsoft Power BI

https://powerbi.microsoft.com/en-us/blog/what-story-does-your-timeline-tell-introducing-the-timeline-storyteller-custom-visual-for-microsoft-power-bi/

CALCULATE and FILTER: A Love Story?

http://sqlkover.com/calculate-and-filter-a-love-story/

Filter data based on a comma separated list using CALCULATE and PATHCONTAINS

https://www.kasperonbi.com/filter-data-based-on-a-comma-separated-list-using-calculate-and-pathcontains-even-with-external-models/

Why I STILL Teach DAX in Excel: My Lament to Microsoft

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

Getting the Latest Earthquake Alert Using the WEBSERVICE and FILTERXML Functions in #Excel

https://dhexcel1.wordpress.com/2017/07/10/getting-the-latest-earthquake-alert-using-the-webservice-and-filterxml-functions-in-excel-by-david-hager/

 

#Excel Super Links #146 – shared by David Hager

 

How To Solve a Complex DAX Problem

https://exceleratorbi.com.au/how-to-solve-a-complex-dax-problem/

Dax Gamechangers – Some useful features and functions from early projects

http://dataspinners.co.uk/2017/09/02/dax-gamechangers-some-useful-features-and-functions-from-early-projects/

Power BI Drill Through

http://radacad.com/power-bi-drill-through
Calling Power BI API using Power BI Desktop to document Power BI Service

http://prathy.com/2017/09/calling-power-bi-api-using-power-bi-desktop-to-document-power-bi-service/

#Excel Short and Sweet Tip #19 (Invaluable Excel Speller

https://dhexcel1.wordpress.com/2017/05/28/excel-short-and-sweet-tip-19-excel-can-spell-by-david-hager/