Search This Blog

Monday, July 27, 2015

Single value filters and custom error messages in Power BI

"Let me be the one for you, otherwise we need to work on communication". Basically this was a very brief description of a recent use case for a power view report which had been later replicated in a Power BI slide as well.

Users were aware of PowerView / Power BI limitation (as of 2015-July-27) of not having filters with a single choice of values to select. PowerView slicers could be a good solutions for this, which would allow having that single value selection functionality (Power BI slicers still need to be improved); and use of regular filters has been a more preferable choice in order to have more room for useful visualizations within a report. 

However users usually get confused by selecting multiple values for a particular filter, which might lead to improper data discoveries. And then they simply asked, "Yes, we understand PowerView behaviour and we understand that we make mistakes with a multiple filter's values selection. Show us then an error message in our report so that we would be aware about this mistake".

It was easier to tell them that they should properly operate with their filters, however I decided to explore PowerView / Power BI functionality a bit more if it would allow some custom error messages, before taking the easy path.

First, I pulled a sample data from a Wikipedia page ( with countries'  populations statistics into my data model; 
and also exported some lookup data for parameters:
Then I created a simple visualization of this data along with 2 slicers: Population Group & Region.

Then for 'Country by Population' table I added 3 DAX measures:
Region Selected = COUNTROWS(FILTERS(Regions[Region]))
PopulationGroup Selected = COUNTROWS(FILTERS(PopulationGroup[Population Group]))
MultipleSelected = if(OR([Region Selected]>1,[PopulationGroup Selected]>1),1,0)

And one measure for the CurrentDate table:
ShowError = if([MultipleSelected], [MultipleSelected]*MIN([MessageType])+1, MAX([MessageType]))
Then I applied ShowError to the Top Left table region with CurrentDate table data:
and MultipleSelected to the main chart in order to control visual behavior of the top table region and this main chart correspondingly.
And it worked! If I selected more than one value from any of the slicers, then the Top table region would show a defined error message (instead of a report date value, please see the slide with this visualization at the beginning of the post) and main chart would become blank:
Also, I realized that with this chart's bars hiding I have found a bug with the Power BI charts in general (which I believed would be fixed soon) that data labels were still visible while data bars were not, which didn't make any sense.
This very same reports was originally developed in PowerView:

And both Power BI and PowerView files can be found here: 

So at least some communication back to users was possible in Power BI, and I think there will be more and more improvements in this Power BI area :-)

Happy data adventures !

Tuesday, July 21, 2015

Setting specific colors for PowerView charts

I was listening for Phil Collins' "True Colors" song in my car audio this morning and it reminded me about the very issue that PowerView currently has: that it doesn't allow to set specific colors to different elements of its own visuals. There are more and more announcements made about coming changes and improvements in this area for Power BI, however if you're still using SharePoint or Excel based PowerView charts, this might be a challenge for you.

There is a way to change colors in PowerView with the help of setting different themes though (Format Power View reports); however I don't really like this approach when one theme change affects all of your PowerView slides and there is not much control left on how to set different colors for different sections of your report.

So I have come with a quick workaround on how certain colors are still can be chosen for my PowerView visuals.

For this demo test I have pulled 2 data sets from Wikipedia using PowerQuery and pushed them further into a PowerPivot model.
1) Top 10 Countries by Population (
2) Top 10 Countries by Territory (

Then I created simple PowerView charts to show this data:
And here is a big question: what if I want to color top chart with Color A and second one with Color B; still not much options left with the existent PowerView interface. But I've noticed that with each color theme there is an order of particular colors, so even if I'm not allowed to set a specific color, I still have a control to choose colors from the existent theme and set them based of my customers' needs.

Default color theme starts with Blue, then goes Red, Yellow and other colors. So I added blank columns to the PowerPivot model's tables (Color 1 & Color 2):

And then I could place empty columns to the set of Values of my PowerView chart this way:
so that by default [Color 1] column would be Blue colored, [Color 2] column will be Red and real Value will become Yellow. Here is a trick: since both [Color 1] & [Color 2] are empty columns, no colors will be shown for them and only one Yellow color will prevail in the chart :-)

You can play a little bit more with this example; I've saved all PowerQueries, PowerPivot model and PowerView charts in this Excel file (Top10Countries.xlsx).

Eventually your PowerView charts can get close to your "true colors" and that's why I like it :-)