Category Archives: PowerPivot DAX

#Excel Super Links #44 – shared by David Hager

Excel Formulas Can Make a Graph http://www.excelhero.com/blog/2012/05/excel-formulas-can-make-a-graph.html

2D non-linear FEA with Excel

https://newtonexcelbach.wordpress.com/2016/06/23/2d-non-linear-fea-with-excel/

Generating Permutations in Excel

http://spreadsheetpage.com/index.php/tip/generating_permutations/

Excel Magic Trick 1427: DAX GEOMEANX function: Average Compounding Rate per Period for FV Calc

https://www.youtube.com/watch?v=uZGoIByc0pE

Copy #Excel Chart as a Enhanced Metafile Picture by David Hager

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

#Excel Super Links #43 – shared by David Hager

Dynamic format using DAX

https://www.kasperonbi.com/dynamic-format-using-dax/

Dynamic CAGR Calculation in DAX

https://powerbi.tips/2016/05/measures-calculate-cagr/

Animated Asynchronous Electric Mmotor Model in Excel

http://www.excelunusual.com/2017/01/asynchronous-electric-motor/

Excel Monthly Report Tutorial

https://andrewexcel.blogspot.jp/2017/05/monthly-report-tutorial.html

Copy #Excel Chart as an Enhanced Metafile Picture by David Hager

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

 

#Excel Super Links #41 – shared by David Hager

Excel High Precision Engineering Chart #1

http://blog.excelhero.com/2010/08/24/excel_high_precision_engineering_chart_1/

Power Query (M)agic Part 1: Always Have Good References

https://powerpivotpro.com/2017/05/power-query-magic-part-1-always-good-references/

Exposing System Secrets with VBA and WMI API

https://sites.google.com/site/beyondexcel/project-updates/exposingsystemsecretswithvbaandwmiapi

Determining The Drive Type From Excel

http://spreadsheetpage.com/index.php/tip/determining_the_drive_type/

Creating a Unique Delimited String from an Excel Filtered List by Using the TEXTJOIN Function 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/

 

#Excel Super Links #39 – shared by David Hager

VLOOKUP: List all the Matching Items

https://exceloffthegrid.com/vlookup-list-all-the-matching-items/

VLOOKUP Across Several Worksheets (2) – Multiple Search Criteria

https://excelxor.com/2014/11/15/vlookup-across-several-worksheets-2-multiple-search-criteria-2/#more-3154

VLOOKUP vs INDEX-MATCH. Which is better?

https://defeatexcel.com/vlookup-vs-index-match

Parameter Table in DAX

http://www.daxpatterns.com/parameter-table/

#Excel: Modifying Control Chart Data to Remove Outliers with Excel Formulas by David Hager

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

 

#Excel Super Links #38 – shared by David Hager

100 Excel Tips & Resources to Celebrate 2000 RSS Subscribers

http://chandoo.org/wp/2009/01/28/100-excel-tips-resources-to-celebrate-2000-rss-subscribers/

Power BI Desktop/DAX – Using Unicode (Arrows) as Measures in a Table

https://gqbi.wordpress.com/2017/05/16/power-bi-desktopdax-using-unicode-arrows-as-measures-in-a-table/

Reverse two-way Lookup in a Cross Reference Table

http://www.get-digital-help.com/2017/05/16/reverse-two-way-lookup-in-a-cross-reference-table/

The Excel VBA Code Cleaner

http://appspro.com/Utilities/CodeCleaner.htm

#Excel For You Blog Posts Containing Downloadable Workbook Files by David Hager

https://dhexcel1.wordpress.com/2017/05/16/excel-for-you-blog-posts-containing-downloadable-workbook-files-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).

 

#Excel Super Links #35 – shared by David Hager

Excel Code Cracker Game

http://www.andypope.info/fun/codecracker.htm

Excel VBA convert column of sentences to column of single words

http://stackoverflow.com/questions/20782832/vba-convert-column-of-sentences-to-column-of-single-words

Excel Magic Trick 1419: RANKX DAX Function & More: Ranking Profit for Products within Manufacturer

https://www.youtube.com/watch?v=sfJWoQixi2U

=SUM(‘???’!C3) Is it a valid formula?? No. It is magical indeed!

https://wmfexcel.com/2015/07/11/sumc3-is-it-a-valid-formula-no-it-is-magical-indeed/

Averaging of Scientific Results in Excel

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