100% Customizable Data Driven Alerts/Notifications in Power BI and Power Pivot

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

Power Pivot and Power BI Classes for Summer/Fall 2016First, we are very excited to announce new class dates:  Houston (September 27-28), Los Angeles (Oct 11-12), Indianapolis (Oct 25-26), and London UK (Nov 15-16).

Reserve your spot today!

(And we’re conducting a special class Thu-Fri this week in Bentonville AR, but that one is by invite only – drop us a note via the contact form if you work in Bentonville and are interested in joining us at the last minute.)

New Sorcery:  Power BI Gets Unlimited-Flexibility DAX-Driven Alerts!

DAX Alerts are Like Security Cameras with Image Recognition :)“Inventing” new techniques/capabilities is probably my favorite thing to do.  Look no further than my last post, Movers and Fakers, for a fun (and hopefully useful!) example.  Some of my other past favorites include Fixing Stale Slicers, Frankenspark, Hyperlink “Drilldown”, Top N vs. Bottom N vs. “All Others”, Sort by Slicer, and honestly dozens more (I really should do a “greatest hits” style post some day, given how long this site has now been running – nearly seven years! – but that will have to wait).

All of those were created using “just” the built-in features of the products, but sometimes, you need actual software changes in order to pull something off.  Today’s technique (and contest!) has long struck me as precisely that – something we needed Microsoft to do for us – but then recently, an inspiration struck me…

Need #1: Get Notified of Failures, not just “Error was Logged.”

First let’s talk about the need in question:  a failed refresh needs to result in notifications, not just an error in the log.  If a data source has outright vanished, or is no longer reachable, or the password has expired, etc., your scheduled refresh will fail and you will see that in the logs, but will you be notified as well?  In on-premises environments, you will not.  Your dashboard/report consumers will stumble onto this problem, get confused, and swamp you with emails.  Or worse, they get confused and NOT swamp you with emails, meaning they just silently lose trust in the overall system.  (We’ve seen this happen a few times with borderline serious consequences).

PowerBI.com does offer email notification on failure, so at the moment, this gap really only exists in on-prem environments (which still represent the majority of our clients, but hey – it’s still progress).

Need #2:  Data Source “Successfully” Refreshed, But Returned Bad or Incomplete Data

Bad Timing, Total Garbage Data, or Some Data Missing: Three Cases When Power BI or Power Pivot Refresh “Success” Isn’t Success

Bad Timing, Total Garbage Data, or Some Data Missing:  Three Cases When “Success” Isn’t Success

Even more critically, sometimes the refresh technically succeeds and you STILL need to be notified.  This is where robust Alerts/Notifications come in, and so far, we don’t have those really have those baked into the MS platform.

The three primary examples of when we need to be notified of failure even when technically the refresh succeeded:

  1. When a data source wasn’t yet “ready” to be refreshed – most commonly, this happens when the database doesn’t yet have the latest data in it (because some other process didn’t run, or is late), so you end up not having the data for today (or the most current week/month/etc.), or worse, you have some of the data for today but not all of it.
  2. When a data source “succeeds” but returns garbage data – we’ve seen examples, for instance, where all numeric columns came back loaded with nothing but zeroes (again due to a problem upstream from the data source) – this can happen to all of the data or just the latest rows.
  3. When certain segments of the latest data didn’t show up – (aka “Not All Precincts Reporting”) Another very common scenario occurs when, say, 95% of your locations reported data for the latest time period, but a few failed to transmit/submit their corner of the data, so you end up with most of the latest data, but not all of it.  (Particularly common in retail sales environments, for example).

Need #3:  The Data is Legit, But There are REAL “Events” That Warrant a Notification

Good, Bad, or Simply New/Strange: More Cases Where DAX-Driven Alerts are Useful in Power Pivot and Power BI

REAL Things that are Good, Bad, or Even Just Strange/New:  Three Things We Want to Know About

Lastly, if the data refreshes correctly and completely, but there are REAL things happening in the data that are interesting, it would be good to know about those as well.

PowerBI.com does have a “select this tile and notify me if it goes above or below a certain number” feature, which again shows that Microsoft is on the right track and making progress, but there is a LOT of functionality missing:

  1. Scan across a whole list of entities (locations, products, etc.) and let me know if ANY of them go above or below a threshold (rather than letting me know if the overall total crosses a threshold, which is pretty much what the current feature does).
  2. Check individual entities against their own individual thresholds – maybe X% is the right threshold for Socks, but Y% is the right threshold for Pants, Z% for Shoes, etc. and I want to be able to specify those in a table somewhere.
  3. Perform “invisible” validation – the current PowerBI.com functionality is hooked to visible tiles.  We also need/want to devise tests like “IF Measure1 > Measure2, let me know” and I don’t want to display a validation-only measure to the user in order to set it up.
  4. Spot Outliers – you could argue that this is kinda the same as #2, but as in the Movers and Fakers post, we can dynamically calculate new thresholds for each entity (or intersection of entities!) on an ongoing basis.
  5. Do ANY of this On-Prem – and lastly, remember that even the current data alerts feature from MS is cloud only (and currently iPhone-only?), so even if the simplest flavor of it is sufficient, many people still don’t have access to it.

 

The Inspiration:  Named Ranges!

There’s a VERY good reason why PowerBI Alerts are missing a lot of things that we want:  it’s a VERY difficult feature to make complete.  I’m sympathetic rather than critical.  Think about it:  it would need its own expression-design UX, similar to the Conditional Formatting dialogs in Excel, or the Rules Wizard in Outlook, to even handle 25% of the real-world scenarios.  And these are not cheap things to design and build – trust me as someone who has attempted it back when I worked there:

image

To Provide Us With a Properly-Flexible Alerts UX, Power BI Would Need Dialogs Like These – But FAR More Sophisticated

But why invent new expression languages and user experiences when we have DAX and Excel???  THAT was the inspiration that hit me the other day.  We don’t need, or even WANT, more UX around alerts!  We want alerts to be tied into the robust tools we already have!

So, check out these two excerpts from my current Power Pivot workbook:

DAX and Named Ranges: The Key to 100% Customizable Data Driven Alerts/Notifications in Power BI and Power Pivot

Each of the named ranges (in green) has a well-known name (UpdateAlert, UpdateAlert1, etc.), and if those are non-blank after a scheduled refresh, we get an email alert!

The pivot at the top is 100% “lifted” from Movers and Fakers – in fact, that post is what got me thinking about Alerts again.  Go skim that post now if you’ve never seen it before, because it’s important to know how the pivot in question ONLY returns Education / Subcategory pairings that have dramatically spiked or fallen in the most recent month, relative to the preceding 12 months.

In short, the pivot in question uses the DAX language to scan for outliers – and that’s a far more robust and complete language to use than any UX that could be built for finding outliers.

Then, on our hidden validation sheet, we use regular Excel formulas to do the following:

  1. Reference into the pivot
  2. If the pivot is non-empty (meaning, it found some outliers), our Validation sheet picks those up
  3. For the things it finds, our Validation sheet constructs “friendly” text strings (using basic concatenation and IF’s)…
  4. And those friendly strings are anchored into cells that are Named Ranges with special names like UpdateAlert

This combination of DAX and Excel formulas can do ABSOLUTELY ANYTHING.

That’s right, ANYTHING.  Whatever your heart can desire or your brain can imagine, you can build your very own custom Alerts feature around it, as if the entire Microsoft development team were at your disposal.  (In a way, they are, because they gave us DAX, and they gave us Excel.)

Wait, who decides what the Named Ranges are Called?

Yeah, great question!  Well, here’s where we DO need a little bit of software help – but it’s help we already have (otherwise I wouldn’t be writing this post).

The short version is that Power Update now examines your workbook during a refresh (after the refresh, technically) and checks a series of Named Ranges that conform to a set of patterns that we decided upon:

  • If one of the UpdateError, UpdateError1, UpdateError2… (continues checking until the named range with the next consecutive postfix number is empty) named ranges are not empty then their contents is added to the {errorList} email element and the task fails.
  • If one of the UpdateWarning, UpdateWarning1, UpdateWarning2… named ranges are not empty then their contents is added to the {warningList} email element and the task succeeds.
  • If one of the UpdateAlert, UpdateAlert1, UpdateAlert2… named ranges are not empty then their contents is added to the {alertList} email element and the task succeeds and forces the success email to be sent even if it’s disabled.

So, you create (some or all of) those named ranges and give them formulas to populate them.  Go nuts – you don’t have to reference into pivots either, you can alternately use Cube Formulas (maybe even leverage CUBESET() !), DAX Query tables…  maybe even do some CONCATENATEX() in your DAX if you want…

Let’s recap that…

Here’s what you do:

  1. Grab the latest version of Power Update from here.  Remember, it’s 100% free for one workbook, so this costs nothing if you don’t already have it.
  2. Create a new schedule in Power Update, and configure Email Settings to work with your email server (it works GREAT with O365 email for instance).
  3. Use a combination of DAX, Excel formulas, and the named ranges listed above to set up the alerts you want, ranging from Errors (which are treated as a failure) to Warnings and Alerts (which are not treated as refresh failures).
  4. Make sure you include the tag or tags in the Email Settings dialog for what you want to see:  {errorList}, {warningList}, and/or {alertList} – note that these tag names are CASE SENSITIVE in this first release!

Power Update Email SettingsMake Sure You Include the {errorList}, {warningList}, and/or {alertList} tag(s) (Case Sensitive!) In the Success and/or Failure Message Fields

In Email Settings, Make Sure You Add Your Desired Tags:
{errorList}, {warningList}, and/or {alertList} to Succeeded/Failed Messages!

Now run a refresh and see what happens!

“But Rob, I’m Not Using Excel at All, I’m Just Using PowerBI!”

If you’re a diehard PowerBI virtuoso and don’t use Excel-based Power Pivot for anything in production, you absolutely CAN still make use of this technique to set up hyper-flexible alerts on your PowerBI models!

The easiest thing to do would be to put all of the DAX validation into your PowerBI model (the PBIX file), upload it, and then create a lightweight Excel file for the validation.  That Excel file can then be connected directly to your Power BI model in the cloud.  Then you set up Power Update to refresh the XLSX validation file – even if you’re using PowerBI’s built-in refresh scheduler for the dashboards, you can instruct Power Update to run a refresh of the validation workbook shortly after the scheduled refresh of the PBIX.

Contest:  Win Our Clothes!  (We’ll Give You Some Unworn Versions of Course)

These are the official (internal) PowerPivotPro polos and sweatshirts that we issue to consultants and trainers on our team:

Win Our (Unworn) PowerPivotPro Clothes

Want One of These?  Or One of Each?  How About a Free License to the Full-Power Version of Power Update?
Or Maybe Even Name-Your-Own-Prize?  Enter our Alerts Contest!
(Winners Will of Course Be Blogged About Here as Well)

We think that people will immediately start using these capabilities to do completely unexpected and cool things.  Which got us thinking…  let’s get some of those ideas flowing, and explicitly encourage our community here on PowerPivotPro.com to get involved…  with a contest!

Here are the Rules:

  1. The contest will run from August 2, 2016 through Friday, August 19th.
  2. You must get it working with Power Update (and prove it with screenshots), but you do NOT have to purchase anything – the free version will suffice nicely.
  3. Feel free to submit multiple entries, and we will use the latest entry from each contestant.
  4. In other words, we expect iteration – we’ll even give you feedback on your earlier versions so you can improve them, so enter early!
  5. Your final entry must be in our hands by 11:59 PM, US Eastern time, on Friday Aug 19th, 2016.
  6. We will select at least one winner, but reserve the right to declare as many as 3-5 winners depending on the quality and quantity of entries.  Excellence will be rewarded.
  7. Only one free license to Power Update will be awarded, but all winners will receive the clothes 🙂
  8. Yes, we have women’s sizes.
  9. We will also separately declare one internal winner from the PowerPivotPro team.
  10. I (Rob) am the final judge.  Muhaha.
  11. Entries must include a writeup (preferably in PPTX or DOCX format) with screenshots.  Concise and clear writing earns you extra points, as does description of the problem, the business impact, the human element, funny stories, how the idea came to you, what would make it better… whatever you think is worth telling us.  Be yourselves folks.
  12. You do NOT have to provide us with the workbooks or PBIX files (but if they are not sensitive, please feel free to include them!)
  13. Do NOT send us sensitive business data – we will politely delete it and ask you to resubmit without such disclosure.
  14. Entries must be emailed to [email protected]

 

And For Clarity…

If you’d like to grab the workbook I used for this blog post, with its named ranges defined, you can download it here.  (It won’t refresh since you don’t have the Access database that “powers” it, but that’s not the important part – you need the named range examples and the underlying DAX).

If you have questions about the idea, the contest, or how Power Update works, please leave a comment on this post.

Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology.

This Post Has 14 Comments

  1. Great article! 🙂 Question – is there a time we could expect training opportunities anywhere on the East Coast? We are beginning to implement Power BI rather extensively throughout several of our companies. I currently am the only one who really knows how to use it and is familiar with it. Our company is open to sending employees to training opportunities but usually aren’t as open if they require large amounts of travel. I know you may not PERSONALLY have a say in location – but just so people know there’s an interest for this on the East side too! 🙂

    Thanks so much!

  2. I LOVE this! Tons of potential in my ERP world where there are tools like Knowledgesync that allow you to do these kinds of things, but they are kind of clunky and you have to know SQL to use them.

    Now I have a simple way to point Excel to Acumatica data and fire off data-driven alerts based on business conditions. Someone just entered a priority 1 Sales Order? A customer hasn’t been contacted in 30 days and needs a phone call to stay in touch? Need to know about some bad data entry and don’t want to have to customize the screen to prevent it? Now we have a way to build in an unlimited list of business conditions that people will want to know about and have it running on Auto-Pilot. Awesome!

    Thank you so much for adding this feature!

    1. Tim, your excitement mirrors my own – anyone who is not yet blown away by what can be done with this… has not yet understood it! 🙂

      (and if that’s the case, well, I needed to write a better post, didn’t I? Such is the nature of writing: failures are always the writer’s fault, never the reader’s, so let me know if something is hazy, folks).

  3. It would be great if you could use this to send an alert to the Powers That Be that reads “ALERT: GDRIII needs all non PP related duties vaporized post haste so he can implement the Zillion and one things he has swirling around in his mindgrapes to make things more better” Thanks for the continued inspiration. 😉

  4. This is an excellent new Power Update feature. But … “Ooh, they only answer “More! More! More!” 🙂

    I have a scenario where I would need to dynamically address the mail depending on the refresh results. I made the following test:
    – store in a cell named ‘UpdateWarning’ a valid email address such a [email protected]
    – configure the alert message to be sent to {warningList}.

    Unfortunately, I got the following error message: “Sending email failed. Error: The specified string is not in the form required for an e-mail address.”

    Is it possible to use a dynamic tag for the alert address list ?

  5. Superb example and idea. Opportunites galore if you image how you can trigger event procedures in excel using VBA. You really can control anything throughout the Microsoft Office suite based on results derived from DAX

    1. Yep! And Power Update allows you to call VBA as part of the refresh process. (Or even call the VBA and have *it* run the refresh as part of its own code).

      Many doors open. MANY.

Leave a Comment or Question