“Understanding Power BI Premium” Events

I’ll present “Understanding Power BI Premium” at three public events in June.

  • June 8th, Carolinas Power BI Group in Greensville, SC at 6 PM
  • June 24th, SQL Saturday Chattanooga, TN at 1:15 PM
  • June 26th, Atlanta MS BI and Power BI Group, Atlanta, GA at 6:30 PM

My calendar has the details.

Microsoft Power BI has enjoyed a lot of attention and success since it became generally available in July 2015.  But its licensing model and cloud-hosted limitations barred wide adoption, especially with larger organizations. The recently introduced Power BI Premium will change all of this. Join this session to learn how Power BI Premium will allow your organization to achieve:
• Flexibility to license by capacity
• Greater scale and performance
• Extending on-premises reporting with Power BI Report Server
• Embedded analytics

Understanding Writeback Target Allocation

I’m working on architecting a financial planning solution powered by Analysis Services Multidimensional. One thing that might not be obvious is how Multidimensional selects the target of writeback allocation. In this case, planning will be done at Customer and Product level. With the default equal allocation when writing at the customer level, it might appear that writeback doesn’t work correctly. You’d expect that only the cells that contribute to the aggregated value (10 in the screenshot below) will be affected by writeback. However, if the Customer and Product entities are in different dimensions, writeback will affect all products!

The reason behind this becomes obvious if you right-click the pivot table, and from its options enable “Shows rows with no data”. Then, you’ll see all products appearing with each customer (customers are crossjoned with products). Recall that by default, the pivot table uses NON EMPTY in MDX query to exclude combinations that don’t exist in the cube. But writeback makes no such assumptions. The reason for this is that the writeback cell is empty, then there is nowhere the writeback value will be allocated to. If the Customer and Product entities are in the same dimension, then the default equal allocation will write to all children of the affected parent, irrespective if their values contribute to its aggregated cell.

So, writeback is not the same as drilling through a cell. Now that you know how it works you can use different allocation settings to achieve the behavior you want. For example, you can choose a weighted allocation with the following expression to avoid writing back to empty values:

iif(Measures.CurrentMember = 0, null, Measures.CurrentMember)

Atlanta MS BI Group Meeting on May 22th

MS BI fans, join me for the next must-attend Atlanta MS BI and Power BI Group meeting on May 22th at 6:30 PM. My esteemed friend, Stacey Jones from Microsoft will explain the predictive analytics capability of Cortana Analytics Suite. He’ll demo a predictive analytics solution that was developed in a single day! It leverages Azure Machine Learning, Azure Data Factory, HDInsight, Spark, Power BI, and Intelligent Apps to deliver a solution that predicts the likelihood of flight delays for a customer. I will do a quick demo of the latest Power BI cool features. Our premium sponsor, TEKSystems, will sponsor the meeting.

Presentation: Cortana Intelligence Suite End-to-End
Level: Intermediate
Date: May 22nd, 2017
Time 6:30 – 8:30 PM ET
Place: South Terraces Building (Auditorium Room)
115 Perimeter Center Place
Atlanta, GA 30346
The Cortana Intelligence Suite provides tools to cover all types of business intelligence needs, from compelling dashboards to cutting edge predictive analytics! In this session we will look at a predictive analytics solution that was developed in a single day. It leverages Azure Machine Learning, Azure Data Factory, HDInsight, Spark, Power BI, and Intelligent Apps to deliver a solution that predicts the likelihood of flight delays for a customer.
Speaker: Stacey Jones has enjoyed his 27 year specializing in Business Intelligence and all things data. He currently serves as the Data Solutions Architect for Microsoft at the Atlanta Microsoft Technology Center (MTC).
Sponsor: People are at the heart of every successful business initiative. At TEKsystems, a leading provider of IT staffing, IT talent management and IT services, we understand people. Every year we deploy over 80,000 IT professionals at 6,000 client sites across North America, Europe and Asia. Our deep insights into the IT labor market enable us to help clients achieve their business goals—while optimizing their IT workforce strategies.

What Does Power BI Premium Mean for You?

I’m sure we’ve heard the announcements today about Power BI Premium. In fact, Power BI Premium is so important that Microsoft has positioned it as a new product under the Power BI marketing umbrella name instead of a new licensing model. Microsoft and industry experts covered the announcements well so I won’t reiterate the obvious. You may wonder what these changes mean for you. Let’s summarize.

Power BI Portfolio

In a nutshell, Power BI Premium targets larger organizations which have faced two issues with the current Power BI licensing model:

  • No “reader” license. If a report has a Power BI Pro features, all users accessing reports would need Power BI Pro license. So, if you a report that used Power BI Pro features, such as gateways or live connections and you won’t this report to be available to 1,000 users, you had to foot $10,000/month bill because everyone required Power BI Pro.
  • Per user license. A case in point – one year after a successful Power BI hybrid pilot, a Fortune 100 organization has purchased whopping 5 Power BI Pro licenses. There are several reasons for the slow adoption by large companies but one of them is the per-user license.

Large organizations who are seeking a mass Power BI deployment to potentially thousands of users could save big with Power BI Premium (use the nice calculator to find how much). On the downside, I’m not happy about Microsoft requiring Power BI Pro licenses for contributors on top of Power BI Premium.

I don’t see smaller organizations being very much interested in Power BI Premium. For them, a welcome change would be that Power BI Free adds Power BI Pro features. On the downside, Power BI Free loses simple dashboard sharing. This reflects the Microsoft vision about Power BI Free: it is for individual users who are evaluating Power BI. To mitigate the impact of the Power BI Free changes, Microsoft offers one year Power BI Pro trial offer to all Power BI Free users as of May 2nd.

Today Starting in June 2017th
Power BI Desktop Connect to 70+ data sources

Data transformations

Report creation and exploration

No changes
Power BI Free No live connections, No gateway connectivity

Smaller capacity limits and data refresh rates

Only simple dashboard sharing

Access to all data sources
Performance equivalent to Power BI Pro
No sharing (not even simple dashboard sharing)
Power BI Pro Access to all data sources

Larger capacity limits and data refresh rates

All sharing options (simple, workspaces, org content packs)

No changes
Power BI Premium Increased capacity limits
Dedicated environment
Content distribution (reader license)
Power BI Report Server
More features in future, such as in-memory caching, incremental refresh (read the whitepaper)

Personally, I’d like to see more Power BI pricing tiers added, e.g. Standard tiers. Currently, the lowest Power BI Premium tier (P1) is $5,000 per month which would be probably out of reach for smaller organizations. But fear not, you can stay within the old Power BI Pro licensing model.

Power BI Report Server

Microsoft has decoupled SSRS from SQL Server so it gets more frequent updates. SSRS becomes actually two products:

  • SSRS – This is the SSRS we know it but with no Power BI integration. It will get new RDL features but no Power BI features. See the Microsoft blogs here and here for more details.
  • Power BI Report Server – Distributed as a standalone installer, Power BI Report Server is a superset SSRS as it gets both existing report types and Power BI reports. As far as the reason for the name change, the Power BI name is a strong brand while SSRS has been associated with the old style paginated reports.

You can get Power BI Report Server in two ways:

  • As a part of the Power BI Premium bundle. You get the same number as licensed EE cores as the number of v-cores you purchased with Power BI Premium.
  • Standalone and covered by a SQL Server Enterprise Edition with Software Assurance license, plus Power BI Pro licenses for report authors (as with Power BI Premium).

So, although Power BI Report Server has divorced SQL Server, it’s still covered by its license (kind of when you send your kid to college but she still lives with you). Currently, SQL Server doesn’t check for Software Assurance in any way (there isn’t such SKU). So, it looks like Power BI Premium licensing would be an honor system for customers who want to get it standalone covered by a SQL Server Enterprise Edition license.

Power BI Embedded

Power BI Embedded has been gaining a lot of traction but the problem was that it’s separate from Power BI Service. Consequently, it had to catch up with Power BI Service. For example, it still doesn’t have connectivity to on-premises data sources. The good news is that Power BI Embedded marries Power BI Service so there will be a feature parity and a common set of APIs. The part that I’m not excited about is that its new licensing model requires Power BI Premium (good bye per-session licensing). This might be a showstopper for small ISVs. I hope that Microsoft introduces less expensive pricing tiers to better cater for needs of smaller companies. [Update 6/15/2017:  Microsoft announced low-cost EM* plans for Power BI Embedded starting at $625/mo]


Power BI Report Measures Over Tabular Models

The May release of Power BI Desktop adds the ability to define DAX calculated measures when Power BI Desktop is connected live to a Tabular model or Power BI datasets. This is conceptually similar to defining MDX calculated members in Excel connected to a cube. The measure definitions are local to the Power BI Desktop model (the Tabular model is not modified). You can do all measure-related tasks as when you define measures in the data model, such as changing the data type and formatting the measure or changing the home table. In the screenshot below, I’ve defined a YTD report measure over the Adventure Works Tabular model.


Behind the scenes, the DAX query generated by Power BI Desktop adds the measures as query-scoped measures in the /* USER DAX BEGIN/END */ section:

DEFINE MEASURE ‘Reseller Sales'[Reseller Sales YTD] =


TOTALYTD(SUM(‘Reseller Sales'[Sales Amount]), ‘Date'[Date])




“Reseller_Total_Sales”, ‘Reseller Sales'[Reseller Total Sales],

“Reseller Sales YTD”, ‘Reseller Sales'[Reseller Sales YTD]


Report-level measures are a welcome enhancement. Bringing this further, I’d like to see the ability to define report-level measures using the Quick Measure feature. Another feature that I’m waiting for is the ability to use custom measures (both defined in the model and report-level) in the new numeric range slicer (currently in preview).

Important Power BI Announcements on May 3rd

Please make sure you register free and join Microsoft Business Forward online event (https://www.microsoft.com/en-us/dynamics365/business-forward) on May 3rd at 10 AM ET.

Join Satya Nadella (CEO Microsoft), James Phillips (Corporate Vice President, Business Applications, Platform and Intelligence), Judson Althoff (Executive Vice President, Worldwide Commercial Business) for major announcements and details on the “new generation” of Power BI, Dynamics 365 applications, LinkedIn, and the Microsoft Cloud.

This should be an important event not to miss!

Atlanta MS BI Group Meeting on April 24th

MS BI fans, join me for the next must-attend Atlanta MS BI and Power BI Group meeting on April 24th at 6:30 PM. Mike Bruce and Alex Higgins from Acuity Brands will share how they use Power BI to improve their development process. Acuity Brands will sponsor the event. And I’ll demo the new Quick Measures Power BI measure.

Presentation: Using Power BI to Track Software Development Performance
Level: Intermediate
Date: April 24, 2017
Time 6:30 – 8:30 PM ET
Place: South Terraces Building (Auditorium Room)

115 Perimeter Center Place

Atlanta, GA 30346

Overview: By using Power BI, Acuity Brands can monitor development teams’ progress with rich, interactive dashboards. Data from Visual Studio Team Services ODATA feeds and APIs as well as pulling data from DocumentDB, teams can drill into their development performance and see where they may be having development performance. Data is accessed via embedded Power BI reports running on dedicated hardware throughout development team spaces as well as accessible via the web using SSO!
Speaker: Mike Bruce has been developing software for the past 22 years focusing on Microsoft products. He is currently runs the DevOps and QA team for Platform Architecture at Acuity Brands.

Alex Higgins recently joined Acuity Brands via the Leadership Program. He has lead the effort to build custom visuals in PowerBI as well as creating embedded reports.

Sponsor: Acuity Brands is the North American market leader and one of the world’s leading providers of lighting solutions for both indoor and outdoor applications. We provide customer-driven smart and simple lighting solutions that offer quality lighting and value-added benefits by empowering world-class talent to create and leverage our industry-leading portfolio of products, technology, and services; drive world-class cost efficiency; and leverage a culture of continuous improvement.

Tabular DAX Editor

The Tabular toolset is getting better. One thing that I miss from Multidimensional is the cube script that lets you view all custom calculations in one place so that you can organize them any way you want, add comments, etc. This is why I contributed to the DAX Editor tool. Microsoft has taken notice and introduced a tool (also called DAX Editor) in the latest SSDT release. Read Kay Unkroth’s announcement here.

The Microsoft DAX Editor supports the old XML-based schema and the new JSON schema. On the upside, it gives you a break from the Measure Grid and the formula bar. On the downside, you can work only on one measure at the time. So, let’s leave it to marinate it a few more months with the hope that we can finally have a Tabular script. As Kay commented at the end of his blog post there is a hope:

Yes, we are hearing this a lot from you guys! Having all expressions in a single document makes it easy to find and replace, search, etc. It’s on the backlog, but not yet on the top of the priorities. Looking at the higher prio work we still need to get done , it’s more mid-termish. But we know how we want to achieve this and we are laying down the foundation with this DAX Editor. Btw. it is much, much more than just an editor window. That is really just the tip of the iceberg. Same with the DAX query window in SSSM. The real beauty (and complexity) is in the DAX parser behind these windows, and a few other features like IntelliSense. It’s coming together. Brick by brick!

Meanwhile, use the community DAX Editor.

Automating Excel to Power BI Publishing

Excel 2016 added a Publish to Power BI menu to let you export or connect Excel workbooks to Power BI. You can read more about this Excel feature here. One area where Excel is still ahead of Power BI Desktop is that is has an object model that lets you automate tasks with VBA. Unfortunately, Power BI Desktop doesn’t have an object model so you have to resort to unsupported ways (aka hacks) to automate tasks, such as refreshing and publishing to Power BI. I discussed some here.

I’ve noticed that Microsoft added not yet documented PublishToPBI method to Excel 2016. With it, refreshing the Excel data model and publishing it to Power BI Service takes two lines of code (you’d need more code to open the Excel workbook from an external application).

Sub Macro1()


ActiveWorkbook.PublishToPBI PublishType:=msoPBIExport, nameConflict:= msoPBIAbort, bstrGroupName:=”<Some Workspace>”

End Sub

Power BI Quick Measures

One of the most common complaints raised by Power BI customers is the DAX steep learning curve. The April release of Power BI Desktop introduces a feature called Quick Measures. Currently in preview (make sure to enable Quick Measures from File ð Options and settings ð Options, Preview features), Quick Measures are supposed to replace Quick Calcs. Besides supporting only a limited number of packaged calculations and not working on top of custom measures, the problem with Quick Calcs is that they don’t show the DAX formulas so there isn’t a way for you to learn from the work Microsoft did or to change the formulas to customize their behavior. This changes with Quick Measures.

You can create a Quick Measure over implicit or explicit measures. To do so, once you add a field to the report, expand the measure drop-down in the Fields of the Visualizations pane, and the click “Quick measures”. Then, select the calculation type. Currently, Power BI Desktop supports about 20 quick measures organized in four categories: Aggregate by category (average, min, max, variance), Filters (filtered value, difference or percentage from filtered value), Time intelligence (YTD, QTD, MTD, and their variances), Running total, Mathematical operations (additions, subtractions, division, multiplication, percentage difference).


For some obscure reason, the YTD quick measure I tried works only with an inline date hierarchy (Power BI Desktop can auto-generate an inline date hierarchy when you add a Date field to the report). But fear not! Once you create the quick measure, it becomes a regular measure and it gets added to the Fields list. Which means that you can change its formula! This is the auto-generated one.

SalesAmount YTD =



ERROR(“Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy”),

TOTALYTD(SUM(‘ResellerSales'[SalesAmount]), ‘Date'[Date].[Date])


And this is how to get it work with any field in your Date table.

SalesAmount YTD = TOTALYTD(SUM(‘ResellerSales'[SalesAmount]), ‘Date'[Date])

Quick Measures are a welcome upgrade of Quick Calcs. They are designed to help you add common calculations and help you learn DAX.