skip to Main Content

Fully-Customizable DAX-Based Alerts in Power BI and Power Pivot, Our New Class Schedule, and Announcing a Contest!

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…

Read the Rest

Two Tricks to Make Your Cube Formula Scorecards Drama-Free!

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!

=CUBEVALUE(“ThisWorkbookDataModel”,”[Measures].[Total Sales]”,slicer_country1,slicer_country2,slicer_dim_calendar,slicer_salesrep,
slicer_contest,slicer_supplier,slicer_supplier1,slicer_supplier5,slicer_salesterritory
,slicer_salesterritor2)

Yikes! Imagine updating thousands of these! I’m going cross-eyed already!

The D-Man Innovates!

Read the Rest

Building Datazen Dashboards from Existing Power Pivot Models

Guest Post by Andrew Todd

image48-1024x526
Build awesome dashboards for mobile and desktop using Datazen

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.

Read the Rest