Category Archives: Self-Service BI

Online Analysis Services Course: Developing a Tabular Model

Check out the excellent, new online course by Peter Myers and Chris Randall for Microsoft Learning Experiences (LeX). Lean how to develop tabular data models with SQL Server 2016 Analysis Services. The complete course is available on edX at no cost to audit, or you can highlight your new knowledge and skills with a Verified Certificate for a small charge. Enrolment is available at edX.

Let’s block ads! (Why?)

Analysis Services Team Blog

Learn about Azure Analysis Services at the Microsoft Data Insights Summit 2017

We’re excited to participate in the Microsoft Data Insights Summit June 12 – 13, 2017 in Seattle, WA. This two-day event is designed to help you identify deeper insights, make better sense of your data, and take action to transform your business.

This year’s Microsoft Data Insights Summit will be filled with strong technical content, vibrant speakers, and an engaged community of experts. The event offers deep dive sessions, hands-on learning, industry insights, and direct access to experts. Join us to expand your skills, connect directly with Microsoft product development teams, and learn how to get the most from the Microsoft BI stack.

The Analysis Services program-management team is excited to deliver the following sessions.

Super Charge Power BI with Azure Analysis Services

Monday, June 12. 11:10 am – 12:00 pm.

Join this session to get a deep dive to how you can scale up a Power BI model by migrating it to Azure Analysis Services. This new service enables larger models and allows fine grained control of refresh behavior. We will cover migration, using the gateway for on-premises data, and new connectivity with Power Query and the M Engine for Power BI compatibility and reuse. Other topics will include creating reports that tell stories, distributing in SPO or PtW, collaborative conversations across teams, data story galleries, custom visuals, Sway, and more.

Creating Enterprise Grade BI Models with Azure Analysis Services

Tuesday, June 13. 11:40 am – 12:30 pm.

Microsoft Azure Analysis Services and SQL Server Analysis Services enable you to build comprehensive, enterprise-scale analytic solutions that deliver actionable insights through familiar data visualization tools such as Microsoft Power BI and Microsoft Excel. Analysis Services enables consistent data across reports and users of Power BI. The demos will cover new features such as improved Power BI Desktop feature integration, Power Query connectivity, and techniques for modeling and data loading which enable the best reporting experiences. Various modeling enhancements will be included such as Detail Rows allowing users to easily see transactional records, and improved support for ragged hierarchies.

Check out the sessions page for the complete list of sessions. Don’t miss out—register today!

Let’s block ads! (Why?)

Analysis Services Team Blog

Custom visuals now available in the Office store

Custom visuals have come a long way since they were announced as part of the GA of Power BI back in July 2015. From best visual contest, to a new and improved version of the platform and the developer tools, to the recent announcement for the new custom visuals community site, it was always about the community.

The community is the force that drives the success of custom visuals. Developers have contributed more and more custom visuals to the visuals gallery, which now has more than 80 visuals! Report creators embraced custom visuals and made some awesome reports. Also, your focus and passion has helped to improve the platform and make custom visuals what they are today. It was heartwarming to see how much you care and enjoy custom visuals! Thank you!

Custom visuals in the Office store

We are very excited to announce, that as of today, custom visuals are available to discover and download within the Office store.

What is the Office store? Simply put – it is the place to find apps (known as add-ins) for your Office 365 software.

The Office Store connects millions of users of Office 365 to solutions that help them get work done more efficiently, more insightfully or more beautifully than before.

What is changing?

Power BI is now a product in the Office store, listing all Power BI custom visuals. Searching and filtering by categories are available for easy navigation.

68d00abb ef26 4a05 8ec6 78466bacbbf7 Custom visuals now available in the Office store

The custom visuals gallery is moving to the Office store. Many visuals are already listed in the store, and we are working to onboard the rest of the visuals within a few weeks. The Office store will be the place to discover custom visuals and download them, as well as the place for developers to submit and manage their custom visuals in the store.

The visuals gallery will still be available until we finish moving all of the visuals to the Office store, however new submissions will only go to the Office store. All custom visuals that were ever downloaded from the gallery will continue to work as usual even after the gallery will be deprecated.

Better for users

If you use custom visuals, here is what the integration with the Office store will mean to you.

  • An easy navigation experience using categories and search. We created a set of categories just for custom visuals.
    3cb4d5e0 a8f1 47dc 8b40 2afda33952cd Custom visuals now available in the Office store
  • A details page for each visual with high quality screenshots and videos to introduce you to the visual.
    98c8c070 d5f3 4ef0 8796 18fb1a82b851 Custom visuals now available in the Office store
  • Reviews and ratings are available as a feedback channel to the developer and also a way for you to help others gauge quality of a visual.

For more information about how to discover and use custom visuals in the office store, see Download and use custom visuals from the Office store.

Better for developers

For you custom visual developers, you will be able to use the Office developer center which will bring the following.

  • Submit your custom visuals in an easy to use web portal.
  • Manage your custom visual submissions.
  • Track how many people visited your custom visual page in the store and downloaded it.
  • Get direct feedback from users through the review system to help you improve the visual.

In addition to that, you will get broader exposure to a larger user base in the Office store.

For more information about how to submit visuals to the Office store, see Publish custom visuals to the Office store.

Get started with custom visuals

Let’s block ads! (Why?)

Microsoft Power BI Blog | Microsoft Power BI

The Latest and Best Way to Catch/Capture/Inspect Slicer Selections

Quick post today because, well, even more going on than usual.

This is actually the fifth post on this specific topic, which means that it’s something that keeps coming up.  But unlike previous posts, in which we kept DISCOVERING slightly better ways to do things, this latest post is triggered by us GETTING a new way to do it from Microsoft.

Yes, that’s right, this one will not work in previous versions of Excel, because we’re gonna use CONCATENATEX!

I’m gonna use Power BI Desktop in this post for the simple reason that I’m on my laptop, which is still running Excel 2013.

OK, let’s start with a Power BI Slicer, and a Card displaying a fancy (but simple!) measure that I’m going to share:

image thumb 10 The Latest and Best Way to Catch/Capture/Inspect Slicer Selections

A Power BI Slicer and Card
(But What is the Card Displaying?)

In its simplest incarnation, this measure is…  super simple.


“, ”

That’s it!  And then you put the [Selected] measure on your card.

In Excel, the DAX is the same.  To display it, you would simply put it in a cell using CUBEVALUE, something like:


(Where Slicer_Subcategory needs to be changed to the name of the slicer YOU are using, obviously.)

The original measure above is really awkward when the user has made NO selection on a slicer – because it can then return a REALLY long list!

To deal with that case, we add an IF to the measure to detect precisely that case, and then return “All.”


“, ”


IF(COUNTROWS(ALLSELECTED(Products[Subcategory]))>5, “> 5 Subcategories”,
“, ”

  1. Whatever column you’re going to use as your slicer, you need to change the measure to reference that (as opposed to Products[Subcategory] in my example).
  2. 5 is not some holy limit – feel free to set your own there, or omit that entire second IF in the case of slicers with very few values.
  3. Obviously feel free to play with the delimiter in CONCATENATEX – I used “, “ but you can do whatever you want.
  4. Same thing with the text values for “All” and “>5 Subcategories”

You can also use this in combination with other visuals, such as Treemap:

image thumb 11 The Latest and Best Way to Catch/Capture/Inspect Slicer Selections

Remember:  You Can Use Other Visuals as Slicers Too, and this Technique Still Works
(Doubly useful IMO since it’s often difficult to tell what’s selected in a Treemap by just looking)

Download the PBIX


Get Your Files

Let’s block ads! (Why?)


Power BI Community Report Theme Gallery

We are very excited to announce the launch of the new theme gallery in the Power BI Community. This gallery is a dedicated space for you to share and find new themes for use in your Power BI reports.

c29751e3 7098 40c8 85af 9f86b260580f Power BI Community Report Theme Gallery

Theming is a new preview feature in the Power BI Desktop that lets you pick colors to use in your charts and to show in the color palette. This feature is a great way to quickly incorporate your company branding or a little personality to your reports. To add a theme to your report, you import a JSON file listing all the colors you want to use in your report’s charts. You can learn more about the theming feature in our March Desktop release blog post or documentation.

Do you need a little inspiration for your own themes or don’t want to create your own JSON file?

The theme gallery is the perfect place to get started. You’ll be able to download any theme you like in the gallery. From there you can import it as is into your own reports or use it as a starting place and make any modifications you want before importing.

2707d769 3634 4627 a49e 6b61117cf07a Power BI Community Report Theme Gallery

Want to show off your design prowess?

Submit themes you’ve created to the theme gallery for others to see and use. If community members like your theme, they’ll be voted up to the top of the list. You will also have pride knowing you’ve helped others create beautiful reports.

Need a theme that is high contrast or color blind friendly?

There is a whole section of the gallery geared towards themes that are more accessible than standard colors palettes. You can find these themes by filtering the list down to Accessible themes.

8467aab6 0c51 4506 be30 71b856111ec3 Power BI Community Report Theme Gallery

Make sure to visit this new gallery in the Power BI Community, submit your own creations, and kudos themes you love!

Let’s block ads! (Why?)

Microsoft Power BI Blog | Microsoft Power BI

Querying Documents With Different Structures in Azure DocumentDB

This is a quick post to share how we can use the coalesce operator in Azure DocumentDB (which is a schema-free, NoSQL database) to handle situations when the data structure varies from file to file. Varying data structure is a common issue in big data and analytics projects. A schema-free database like DocumentDB allows us to ingest and store the data with varying structures without a lot of upfront effort. However, accommodating these varying data structures is challenging later when we want to analyze the data. When querying the data (think Schema on Read here), I do need to impose a consistent structure on the data to perform analytics.

Following is a highly simplified example which shows how I have a PrevVal in one document, but not the other:

 Querying Documents With Different Structures in Azure DocumentDB

Here are the results if I do a simple select statement in the Azure DocumentDB Query Explorer:

 Querying Documents With Different Structures in Azure DocumentDB

See how with the simple select above I get the PrevVal (aliased to PreviousDataValue) property returned (in results on the right) for Document1 but not Document2? No big surprise there of course. However, what I really want is a “standardized” structure that is consistent across all documents so that I can perform analytics on the data, and potentially store the valuable data in my data warehouse.

Enter coalesce. Coalesce checks for the existence of a property inside of a document. This makes it easier to deal with properties that don’t *always* exist in all documents.

Here’s what happens when I add the ?? coalesce operator to our select query:

 Querying Documents With Different Structures in Azure DocumentDB

In the above example, I’ve told the coalesce operator to return a 0 if the PrevValue doesn’t exist. And…shazam! We now have a standardized structure output on the right. The trick to making this work of course is including every possible property that could possibly come through in the set of JSON documents.

If missing data values like PrevValue is an issue, you can find specific documents which are missing a specific property by using NOT IS_DEFINED like this:

 Querying Documents With Different Structures in Azure DocumentDB

Let’s block ads! (Why?)

Blog – SQL Chick

What makes a Data Source a Data Source?

[unable to retrieve full-text content]

It should be obvious, and it is — at least at the Tabular 1200 compatibility level: A data source definition in a Tabular model holds the connection information for Analysis Services to connect to a source of data such as a database, an OData feed, or a file. That’s straightforward. However, at the Tabular 1400… Read more
Analysis Services Team Blog

Dive into Power BI at Summit EMEA

67bf86d0 0066 4a7d ad2c b13afcbbfca8 Dive into Power BI at Summit EMEA

I invite you to join me at Summit EMEA and learn about how you can maximize the value in your data by using the amazing capabilities in Power BI. At the event, you’ll hear what’s new with Power BI, discover new ways to deliver insights for your business, hear from power users and experts, and learn how your peers are using their data. Data is at the heart of all business and Summit EMEA is a great opportunity to stay informed about the latest data innovations. 

Insights to action with Dynamics 365

Power BI is gathering significant momentum in the market, with recognition both from analysts as well as a rapidly growing community of users. One of the reasons for Power BI’s success is that we in the product engineering team, are continuously updating the product based on user feedback. Summit EMEA is a great opportunity to hear about the latest Power BI updates and what’s driving our user-led innovation. Power BI is part of the overall Microsoft Business Aplication Platform and works closely with other products such as PowerApps and Flow. These related products will also be covered at Summit EMEA. 

Dedicated Power BI sessions

We will have a dedicated track and sessions for Power BI, I will kick off this track with an overview of how Power BI helps Dynamics 365 users make sense of their data. We’ll explore live and actionable visualizations that help you make prompt, impactful decisions. All this with some really cool demos!

There’s a full slate of content dedicated to Power BI in our breakout sessions. You’ll learn about the latest topics, such as:

  • Mobile insights with Power BI on the go.
  • Using R with Power BI.
  • Data modeling using Power BI desktop.
  • Developing your first Power BI report. 
  • Using solution templates to deliver turnkey solutions.

View the full schedule of Power BI content.

Intelligent business applications

Mike Ehrenberg, CTO for Microsoft Business Solutions, will deliver the keynote for Summit EMEA and discuss Microsoft’s strategy for intelligent business applications. You’ll get an inside view on Dynamics 365 and how it unifies CRM and ERP capabilities into purpose-built applications to help you meet the changing needs of today.

I hope to see you at Summit EMEA.

2e3e41ac 0314 4035 829f dae0751abf0e Dive into Power BI at Summit EMEA

a8775277 8503 4954 b413 fe57073001d4 Dive into Power BI at Summit EMEA

Let’s block ads! (Why?)

Microsoft Power BI Blog | Microsoft Power BI

Two Great New Power BI Features

Today I have decided to stray from my normal topics to talk about two exciting updates in Power BI Desktop this month. March 2017 really was a great release, but the 2 things I am most excited about are the new Matrix preview and Custom Report Themes (color palettes) also in preview.

The capability of the matrix visual has been a big gap for a very long time (since the beginning actually).  The current matrix barely meets the minimum capability to make it usable.  It is fine if you want 1 item on rows and 1 one columns, but it falls away very quickly thereafter. All that changed last week with the delivery of a new matrix preview.

Turn On The Preview

Microsoft often releases functionality that is not quite finished but is good enough to let it loose on the community. All these preview items can be turned on/off in the preview settings window. You can find the preview features window at File\Options and Settings\Options.

image thumb 4 Two Great New Power BI Features

Once you set the options, you will need to restart Power BI Desktop for them to take effect.

After turning on the preview, you will see there is an additional matrix visual.

image thumb 5 Two Great New Power BI Features

I like this approach as it means anyone using the existing basic matrix can continue to do so without breaking anything while Microsoft builds out the new features.

Expand, Collapse, Drill and Filter

Expand and collapse behaves just like a pivot table however with a slightly different UI. The new matrix experience is however entirely consistent with the chart drill experience so it is very intuitive.

matrix ppp Two Great New Power BI Features

The new cross filter behaviour is of course not possible in a regular pivot table in Excel (without VBA). You can select any column, row or cell in the matrix and it will cross drill the other visuals on the canvas as can be seen above.

Staggered Column

The staggered column formatting is is a nice touch allowing the user to change the offset of the nested columns as you can see below.

stepped layout Two Great New Power BI Features

The second killer feature this month is custom report themes.  I’ve worked on developing custom software and solutions for many years. In my experience custom color palettes can be a deal breaker for some users. Custom colors won’t help you improve the quality of the analysis but if you don’t let the users have their corporate colour palette, then you will be on a hiding to nothing. I figure just let them have the colors they want and move on.

Custom report themes are also in preview and still have a way to go before they are fully integrated into the UI. But thankfully Microsoft has fast tracked the preview to allow those that need these to get started.  You can turn on this preview feature the same way as before with the matrix.  After you turn on the preview feature, you will be able to import your own custom theme as shown below.

image thumb 6 Two Great New Power BI Features

But before you can do that, you need to create your own theme file.

Create a JSON Configuration File

To use the preview feature, you need to create a JSON configuration file that contains the color codes you want.  This is not very user friendly in its current form, but that is the price you have to pay to get the feature more quickly as a preview feature.  It is pretty easy to create one – I just did it using Notepad.exe.

It is also very helpful to get a screen colour palette tool to help you. There are many on the web that you can use – I used this one to find something l liked.

If you already have a corporate palette then you really just need a way to accurately select the correct color hex codes (eg using a screen color picker tool) – I use FastStone Capture.  If you want to build something from scratch, then a web based color palette tool can be your friend.

Here is the content of the JSON file I created








I just added the above code to a new NotePad file, then saved this as a text file with the name Excelerator.json (make sure it doesn’t add .txt to the end by first selecting file type *.*) and then I imported it to my Power BI Desktop file using the menus I showed above – it worked first time.

image thumb 7 Two Great New Power BI Features

When I went to select a color thereafter, here is the palette I had available.

image thumb 8 Two Great New Power BI Features

OK, I admit I am not a graphic artist and this first attempt isn’t great.  One really nice thing about the theme feature is that you can specify only a few colors or as many as you want (literally hundreds and hundreds).  In my example above I only specified  2 colors (the 2 light blue columns of colors), and Power BI filled out other possible colors automatically for me across the screen (you can see 6 additional color families).  Also all the color variations down the length of a column are automatically generated by Power BI.

OK, so I didn’t like what I had, so I went back to my color palette tool and tried again.  This time I got something softer on the eyes (less electric).  But of course the whole point is you can have what ever you like.

image thumb 9 Two Great New Power BI Features

Here is my final JSON file








Ironically I started a new project with a client today and these 2 features were on the “I hope they arrive soon because we really need them for this project” list. Perfect timing Microsoft!

Let’s block ads! (Why?)


SSMS DAX Query Editor

We are excited to announce the SQL Server Management Studio DAX Query Editor! Have you ever authored a DAX query in SSMS using the MDX editor? With the new DAX Query Editor, you no longer need to do. Download RC3 for vNext from the SSMS release candidate download page.

SSMS DAX Animated SSMS DAX Query Editor

To try it out, click on the new DAX Query toolbar button, or right-click > New Query in Object Explorer.

DAX Query toolbar button SSMS DAX Query Editor

IntelliSense works for DAX functions and model objects. Members listed for selection are type aware. For example, after an EVALUATE statement, the DAX Query Editor expects a table type, so lists DAX table-valued functions, and tables in the model.

IntelliSense functions SSMS DAX Query Editor

Once a function is selected, parameter information is provided.

Parameter Info SSMS DAX Query Editor

In the following example, measures are offered for selection instead of DAX functions and tables based on the position of the parameter.

Type aware parameter info SSMS DAX Query Editor

And of course, syntax highlighting works too.

Syntax highlighting SSMS DAX Query Editor

We hope you agree these features make DAX query authoring in SSMS more productive. For example, the type-aware IntelliSense makes it easier to find what you’re looking for.

This is the first release of the SSMS DAX Query Editor; it is not yet in GA status. We are still adding enhancements. For example, DEFINE MEASURE syntax recognition, and parenthesis-match highlighting are planned for the next release. If you have suggestions for enhancements, or general feedback, please use ProBIToolsFeedback at

Let’s block ads! (Why?)

Analysis Services Team Blog