I’ve been meaning to blog about this for a while - today's the day :-). Inside Power BI Desktop is a relatively new feature called "New Table". This is only available in Power BI Desktop and not in any of…
(Image Courtesy of Orbitz.com) Disproportionately Popular International Destinations by US State – Can We Do Something Similar? Inspiration Strikes! I haven’t done a DAX post in a long time, so I was thrilled to get some inspiration from an unlikely…
I answered some forum questions recently that were similar in nature and I thought they would make a good blog article. The scenario is this: there is a table that contains information about when an employee was absent from work…
Have you Heard This Joke: Gil, Austin, and Rob (left to right) Walk Into a Bar… (Yes, Rob’s hair was purple at the time.) (Note from Rob: and it’s still purple today!) Intro from Rob Well folks it’s an exciting…
For Much of Last Week, Our Power Pivot & Power BI Book Topped the Spreadsheet Category on Amazon (Think about it: more people learning about DAX than learning about VLOOKUP or SUMIF is a MAJOR development!) Awareness That’s Been a…
This Email and the Highlighted Text were Automatically Generated in Response to DAX “Detectors” Scanning Our Results During Scheduled Refresh I’ve Wanted this Feature Forever. We Now Have It. Classes Announced for Houston, Los Angeles, Indianapolis, and London First, we…
YTD is easy with “standard” calendars, but the previous year’s equivalent is a non-obvious formula. Back in the Saddle! What’s this, you say?? Yeah, it’s Rob, and I’m here with an actual formula post! Woo woo! Starting the year off…
Guest Post by Andrew Todd
Cube Formulas: Ultimate flexibility for your reports but lots of drama to update…
A while back, I was tasked with finding a way to automate upkeep of a scorecard built primarily with thousands of cube formulas (Yes, thousands!). This particular scorecard was still under development, and maintaining and making changes to it had become a full-time job! All of the individual cube formulas needed to be updated several times a week, and this was expected to go on for months as executives made up their minds on the final product.
Fortunately, I found two tricks that allowed me to:
a.) Change slicer references in all cube formulas with a single click
b.) Modify entire tables across multiple sheets in seconds
These two tricks freed up time that I used to drive further improvements and start performing real analysis, rather than just maintaining a report.
Cube Formulas: Flexible and Powerful
Cube formulas allow you to add PowerPivot/SSAS Tabular calculations to any cell in virtually any orientation that you can think of. They’re a big part of what makes Excel simply the world’s best data tool, period. (Imagine if you could use them in Power BI Designer!)
The flexibility of cube formulas is powerful, but it does carry a price. Cube formulas are worksheet functions, so they bring their ‘worksheet function drama’ with them (lack of portability, unique formulas, individual updates, etc).
Here are two tricks to keep your formulas easy to maintain and update!
Cube Formula Trick 1: Consolidate Your Slicer References!
The first thing I noticed when opening my scorecard was the length of the cube formulas! I clicked on a cell containing a CUBEVALUE() formula and was greeted by multiple rows of slicer names in the function bar. The scorecard had a total of 10 slicers, and each needed to be referenced in the formulas. What’s more, not all of the slicer names in the scorecard were to be included in the calculations and yet more slicers were set to be added in the future!
Yikes! Imagine updating thousands of these! I’m going cross-eyed already!
The D-Man Innovates!
Guest Post by Andrew Todd
Datazen is a great visualization tool that is free to Microsoft SQL Server Enterprise customers. If you haven’t worked with Datazen yet, check out this post by Rob and Chris! Datazen allows you to design an awesome dashboard before you even work with any data! You simply draw a visualization, and then Datazen reverse engineers the exact layout of the aggregate table that you need in order to make that visualization work.
If you already have a PowerPivot model that you use for reporting, naturally you’ll want to use it to build visualizations in Datazen rather than reinvent the wheel entirely. One way to incorporate your dozens or even hundreds of measures and model logic (not to mention dozens of hours) into Datazen visualizations is through DAX queries.
Two Paths to Datazen DAX Query Nirvana…
Ok, you’ve built a fantastic BI dashboard in Datazen and your chakras are perfectly aligned. Now, there are two enlightening paths that you could take to build tables for Datazen visualizations with DAX queries:
A) Create a new ‘Data Connection’ in Datazen to your PowerPivot model hosted on SharePoint/SSAS Tabular, using the Analysis Services Data Provider. You can automate the refresh of data with SharePoint/Power Update and in Datazen itself.
B) Query your Power Pivot model using DAX Studio and save the workbook to a One Drive folder, then import the data into Datazen. This process can also be automated using Power Update.
Let’s take a closer look at both of these methods, starting with a SharePoint hosted Power Pivot model.
by Matt Allington I was helping a friend out recently with an interesting problem. It all started with a SUM( ) that wasn't behaving. It quickly became a SUMX( ) problem but evolved into a DAX Studio/Query problem. Let me…