Power BI

Performance Problems with IF statement execution in SSAS Tabular

Performance Problems with IF statement execution in SSAS Tabular

November 4, 2015

Due to the high compression rates and stellar in-memory architecture of SSAS Tabular, most people with smaller models do not experience performance problems (in-spite of employing bad data modeling techniques and inefficient DAX measures).  However, as the size of your models increase, you will start to see performance issues creep up, especially if you are not paying attention to data modeling and DAX measures. Last week, I gave a presentation at the PASS Summit 2015 on my experience of building a 150 GB Tabular model in SSAS 2012. During that, I shared my story on how some of the DAX measures with IF statements were causing performance issues and how to work-around that issue by rewriting your DAX measures. During that day, Microsoft also announced that they resolved this issue in SQL 2016, so I thought of demonstrating the issue, workaround and also the fix in SSAS 2016.

Performance problems with IF statement in SSAS Yabular

Issue in SSAS 2014 (& older versions)

For demonstrating the issue, I will be writing queries against the Adventure Works model in SSAS 2014 and using MDX Studio to show the server timings. Let me start with the below query

WITH MEASURE ‘Date'[test] = If ( 1 = 2, [Internet Total Sales], [Reseller Total Sales] )
SELECT NON EMPTY { [MEASURES].[Test] } ON COLUMNS,
NON EMPTY (
{ [Date].[Calendar Year].Children },
{ [Product].[Product ID].Children },
{ Geography.[Country Region Name].Children } ) ON ROWS
FROM [Model]

The above MDX query defines a DAX measure called Test, which depending on the condition displays either Internet Total Sales or the Reseller Total Sales (To make it simple, I just made a static condition 1=2 but that can be replaced by any dynamic condition also). The query results should display the Test measure for Year, Product ID and Country. Now, normally we would expect that the Test measure should only execute the true part of the IF statement. But let us execute this in MDX Studio and see what actually happens.

  Storage Engine scans against SSAS 2014 (Original query)

You can see that both the branches of the IF statement are being executed, even though we expect only the true part to be executed. For smaller models, it might not make a difference but for large models with expensive measures, this might cause severe performance issues.

Workaround in SSAS 2014 (& older versions)

The workaround for this issue is to rewrite your DAX such that we ensure that the measures get executed only if the condition is true.

WITH MEASURE ‘Date'[test] = CALCULATE([Internet Total Sales], FILTER(Currency, 1=2)) + CALCULATE( [Reseller Total Sales], FILTER(Currency, 1<>2))
SELECT NON EMPTY{[MEASURES].[Test]} ON COLUMNS,
NON EMPTY({[Date].[Calendar Year].children}, {[Product].[Product ID].children},{Geography.[Country Region Name].children}) ON ROWS
FROM [Model]

Note that the measure has been rewritten as the sum of two CALCULATE functions. The key is to use a table in the filter clause within the CALCULATE that satisfies the below conditions

  • Is related to the fact table of the measure
  • Is low in cardinality (you can also use a low cardinality column instead of a table)
  • Is not being used in the calculations for the measure/condition. If yes, do some extra testing to make sure the performance is not worse

The reasoning behind the table being connected to fact table is because the calculate() with the false condition has to evaluate to zero / BLANK so that the result of the Test measure would only be the one appropriate measure. If the table is not related, you will end up with the sum of both the measures. A low cardinality table or column is preferred because in this technique, you will see that there are 2 additional queries being sent to the storage engine, which evaluates the FILTER part for the two measures. If the tables have high cardinality, the time for the FILTER queries will take more time. The reason why I said that the table or column should not be used in the measure calculations or condition is because I have seen that in certain conditions, this could actually make the performance worse and still execute both the branches. So just make sure you do some extra testing.

Storage Engine scans against SSAS 2014 (Workaround query)

That said, let us look at the scans for the above query. You can see that only the Reseller Sales measure is executed. Also, if you notice carefully, there are 2 extra scans which basically check the filter condition for Currency. In large models, these scans for low cardinality dimensions will be almost negligible and the time for these extra scans will be much lesser than the time taken to execute the other measure also. In this case, the Adventure Works model is just 18 MB, so you won’t see much of a difference.

New Optimization in SSAS 2016

SSAS 2016 CTP2.3 (and newer versions) has a new optimization for this issue – Strict evaluation of IF / SWITCH statements. A branch whose condition is false will no longer result in storage engine queries. Previously, branches were eagerly evaluated but results discarded later on. To prove this, let us execute the original query against SSAS 2016 and see the results.

Storage Engine scans against SSAS 2016

Now we can see only the relevant measure is being executed. Also, it is faster compared to SSAS 2014 versions of both the original query as well as the workaround. I hope this article will help people who are not on SSAS 2016 to optimize IF statements, and also help understand what the new optimization in SSAS 2016 – Strict evaluation of IF / SWITCH statements actually means. There are also a bunch of new features and optimizations in SSAS 2016 and you should check them out!

Posted by SQLJason, 2 comments
Quick Intro to Power BI Visuals Gallery

Quick Intro to Power BI Visuals Gallery

October 19, 2015

I don’t usually look forward to Mondays (especially after spending a very exhausting though rewarding weekend organizing SQL Saturday Charlotte), but then today was different. Amir Netz had already spoiled my weekend by putting out a teaser on Power BI and I was actually waiting for Monday to come so that I could remind him of his promise.

Twitter conversation - Amir Netz

And well, he didn’t disappoint Smile… This is such a great news to the world of dataviz. Let me quote his announcement below

I’ll admit it. I am very excited… So deep breath. Here is exactly what we are introducing today:

  1. Custom visuals in the Power BI service and Desktop: The ability to upload and incorporate a custom visual, whether a broadly useful visual from our community gallery or a completely bespoke visual tailored for the needs of a single user, into the report and then share it with others. This is available in the Power BI service today, and in the Desktop next week.
  2. The Power BI visuals gallery: A community site (visuals.powerbi.com) that allows creators to upload new Power BI visuals and for users to browse, select and download those visuals.
  3. Power BI developer tools: With our developer tools every web developer can code, test and package new visuals directly in the Power BI service to be loaded to the gallery.

You can read more on this in the official blog post here. Let me use this moment to give a quick intro to the Power BI visuals gallery and how you can use some of the community examples to enhance your visualizations.

Quick introduction to Power BI Visiuals Gallery

First of all, note that this functionality is only available in the Power BI service as of now and will be available in Power BI desktop next week. Also, the custom visuals can not be pinned to a dashboard as of now, but that feature should also be coming soon. That said, follow the steps below:- 1) Head over to https://app.powerbi.com/visuals and feel free to choose any of the awesome visualizations created by our community. For now, I am going to choose Hexbin Scatterplot (which was created by my colleague David Eldersveld and won the third prize in the Power BI custom viz contest– you might also want to check out his thoughts on Power BI Custom Visualization here) and KPI Indicator with status.

Choosing custom visuals from Power BI Visuals gallery

2) For each of the selected visuals, click on the visual icon and then you will be presented with the Download Visual window. Click on Download Visual button.

Download Power BI custom visual

Read the terms of use in the next screen and then press the I agree button.

Agree to terms and conditions

3) This will begin the download of your pbiviz files (power bi custom visualization files). Once the download is over, sign in to Power BI service and then open a new report. Click on the Ellipsis symbol (…) to import the two pbiviz files that you downloaded.

Import Power BI custom visual file (pbiviz) in Power BI Service

4) Now you can use those custom visualizations just like the existing ones. For e.g., I can create a hexbin scatter plot chart by selecting Sales Amount, Sales Quantity and Store name. Note how I change the default visualization to a regular scatterplot and then to a hexbin scatterplot. Also look at the benefits that a hexbin scatterplot gives over a regular scatterplot – you can easily see where the concentration is more, and you also have rug marks on your axis to show where the dots are. Feel free to explore the chart, you can watch how it works from the video in this link.

Hexbin Scatterplot in PowerBI service

5) I added a regular bar chart for Sales Amount by Year on the bottom left to show the interactive features of the new charts. Then, I went ahead and added the Sales Amount and Sales Amount LY by Calendar Month and chose the KPI Indicator visual. Note how smoothly all the charts work with each other!

Addding KPI Status Indicator to Power BI service

6) Feel free to explore further. For e.g., I added a slicer for ClassName also to check out the interactivity

testing out the interactivity for custom visuals in Power BI Service

7) You can save this report and share with others now. When you share a report that contains a custom visualization, you may be greeted with a warping that the report contains custom visuals. Click on the Enable custom visuals button to see the report.

Enable custom visuals warning in Power BI service

You can see how easy it was for someone like me, who doesn’t know how to code, to incorporate these visualizations in my report. And for those who know to code, the possibilities are endless. As the community grows, we are going to get more and more of these awesome visualizations and this will greatly impact the lives of people in the data analytics industry. As for me, I can’t wait to see what all awesome stuff is going to come from the community and also what other surprises the Power BI team has in store for us (would be definitely tough to top this one though!)

Posted by SQLJason, 0 comments
My Thoughts on Cross Filtering in Power BI

My Thoughts on Cross Filtering in Power BI

September 30, 2015

It’s amazing how much of an influence your upbringing can have on you and your preferences. I was the youngest in my family for almost 10 years (till my brother came along) and not to say, growing up, I was very much pampered by my mom. However, my dad was more of a proponent of what I call tough love and back in those days, it was still legal to spank your child to set him straight. (Sometimes, I even feel like my father was the one who coined the proverb – Spare the rod and spoil the child). Looking back now, I feel those were the moments that really formed my character and helped me reach where I am, and I am really thankful to God almighty for giving me the perfect family. I know some of you might be nodding your head in agreement with me reading this post, while a lot of you might be getting really angry at what you are reading. This is perfectly understandable, and it’s ok, because we live in a free country, right? You might also be wondering the reason of such a lengthy introduction to my blog. Well, the reason is that today’s post is kind of tough love as I am criticizing (constructively) the way Cross Filtering works in Power BI, and also providing an example of how I think cross filtering should work in an efficient BI tool by showing Tableau as an example.

My thoughts on cross filtering in Power BI

Action Item – Please do it

For those who don’t have time to read this post, I would please request two things:-

1) Please vote for this issue (link given below) and tell others to also do it https://support.powerbi.com/forums/265200-power-bi/suggestions/6709520-drill-down-should-drill-or-cross-filter-other-visu

2) If you are reading this before 12 PM PST Oct 2, 2015, please take the below survey which will give the feedback directly to the Microsoft Power BI research team (and if you are in the US, you might win a $50 Amazon gift card also). Make sure that you mention “making cross-filtering more intuitive” and “ability to hold selections on more than one chart for cross-filtering” as two points for this question in the survey– “What would make Power BI Desktop a better experience for you?

Please please please do it Smile

How Cross Filtering in Power BI works

Before I start this post, I have to say that I am one of the biggest fans of Power BI, and I have never been as optimistic about a Microsoft BI product as I am right now. (and it’s not just me being a fan boy, the most recent Forrester Wave report shows Microsoft leading the BI pack)

Forrester Wave BI report

The Power BI team is also one of the most responsive teams and you can regularly see the product managers as well as the product team members interacting with the general community on twitter and the Power BI community. But they don’t have an infinite number of resources and time, and hence will be making changes to the product based on priority, and votes are one way we can help the team prioritize the feature requests. The more the votes, the higher the priority and the faster we can get this feature implemented by the product team and this is where I really need all of you to pitch in. Let’s start by taking a look at how cross filtering in Power BI works today:- Let’s say I have 3 charts – bar chart for sales by business lines, bar chart for sales by country and a bubble chart which shows some KPIs by countries.

Power BI dashboard

You can click on any chart and see the rest of the charts refresh for it. For eg, I can click on Nutrition business line and see the other charts refresh for it. However, there are a couple of things I can not do. For eg 1) I want to see the bubble chart for business line – Nutrition and country – Japan. Normally you would expect to click on Nutrition in the first chart, and Japan in the bottom chart to see the cross-filtered bubble chart. However, Power BI currently does not allow us hold selections on multiple charts. The workaround is to add slicers or filters for the needed fields, however it will break the flow when I am trying to find insights from my dashboard.

1 Power BI not able to hold multiple selections

2) I want to see where a particular country is in the bubble chart along with the rest of the countries, so I can assess it’s performance with respect to the other countries. For eg, when I click on Japan in the bottom bar chart, I want Japan to be highlighted in the bubble chart. Right now, you can see from the above image, that when I click on a particular country, only that country appears in the bubble chart because it is filtered. What we need is an option to specify whether we need a chart to be filtered or in this case, highlighted. 3) Cross-filtering during drill downs is the most counter-intuitive feature for me. I had raised this issue in 2013 for Power View and despite getting a lot of votes for the connect issue, the issue is still active. For eg, let’s say the bottom chart shows the sales by Region which can be drilled down into countries. When I drill down into the Greater China region (which only has 3 countries), I expect the top chart to cross filter for Greater China region, which it doesn’t. But if I manually select all the countries, it will cross-filter the top chart appropriately. So in a way, I can take a screenshot of the exact same report showing 2 different data points – which would be very confusing for end users. What we need is for the cross-filtering to work intuitively when we are drilling down.

2 Cross-filtering during Drill down is counter-intuitive

And looks like I am not the only one as I can see 4 comments in the last month on this.

Comment from Microsoft Connect

Comment from Power BI Community

How I feel Cross Filtering should work

I didn’t want to sound pompous by saying this is how it should work; everyone has their own ideas and most of the times, no-one is wrong. But this is why it is important for a BI tool to give options to the end user, so that they can manage the options and choose to use it the way they like. That said, let’s take a look at another popular BI tool – Tableau and see how it handles the above scenarios.

1) Note how the entire report cross-filters as I keep on holding multiple selections.

3 Tableau cross filtering

This experience is very important as I can see what are the top countries for each business line, and then I can also choose a particular country and then see the information for the selected country and business line to analyze in detail.

2) Also note how the country in the bubble chart gets highlighted, when I click on a country in the bar chart. As the number of bubbles increases, it is difficult to see where the selected bubble is unless we have a highlighting feature. The reason why highlighting is important is because it will help us identify patterns by comparing with the rest of the categories.

3) As far as the drill down is concerned, it is not that straight forward in Tableau, but we have options that will help us achieve the end result as I have shown below. You can see that when I click on a particular product category, it drills into the subcategory and all the other charts (the bar chart on the right and the table under it) are also getting filtered appropriately.

5 Tableau drill down

In reality, they are 2 different dashboard sheets, and clicking on the first sheet takes us to the second sheet with the drill down parameters intact (just like with SSRS). But an end user will not be able to get this difference and at the same time achieve the functionality.

Conclusion

It’s amazing how much ground has been covered in Power BI ever since the release, and there have been some really great decisions (like the ability to add custom visualizations – you just have to look at some of the contest entries to see some really great dataviz) as well as features (44 new features in the last monthly release!!!). For all that we know, the team might have already made this change in their next monthly release, or maybe it is still not in the priority list because enough customers do not want this. Either ways, I just wanted to put this out in case you also think the same way, and if yes, make your voice heard in the Survey. Now would be a good time to scroll up to the Action Item part! Smile

Note

As I mentioned before, Power BI versions change rapidly and there are a lot of new features coming in monthly. So it is important to check your version and see if there are any changes. The version at the time of writing this blog is given below-

image

Posted by SQLJason, 3 comments
My Thoughts on Calculated Tables in Power BI

My Thoughts on Calculated Tables in Power BI

September 24, 2015

Yesterday was a terrific day for all of Microsoft Power BI fans. Microsoft released updates for Power BI Service, Power BI Mobile and Power BI Desktop (with an unbelievable 44 new features) – which basically means no matter whether you are a developer, BI professional or an end user, all of you got something new to play with along with this release. The blogs give a lot of details on what those new features are, so I wouldn’t be going over them. But I wanted to take a moment to pen down a few moments on my thoughts on a new modeling feature within this release – Calculated Tables.

Calculated tables in Power BI

Chris Webb has already posted his thoughts on Calculated Tables in Power BI and I am pretty sure Marco Russo / Alberto Ferrari will post some on the same pretty soon (if not, this is an open request from my side for Marco & Alberto to post something on the topic, pretty please Smile) – [Update 9/28/2015 Read Transition Matrix using Calculated Tables]. As usual, a combination of posts from these folks are going to be the best resource for any topic in the Modeling side, and I learn most of my stuff from them. So you would be thinking – what exactly am I trying to achieve in this post? Well, I would like to add my 2 cents on the same and try to see if the community in general agrees with what I think and if not, to learn from the comments and the interaction this might generate.

I) How to build Calculated Tables in Power BI

Before I start on my thoughts on calculated tables, it might be a good idea to quickly see how we can create calculated tables.

1) First make sure that the version of Power BI is equal to or higher than 2.27.4163.351. (I am making a fair assumption that this feature will be enabled in all higher versions also released in the future). If not, download it from here

Power BI version

2) Now open any of your existing models in Power BI (or get some new data), and after that, click on the Data tab. You should be able to see the New Table icon in the Modeling tab on the top.

New table - calculated table

3) Click on the New Table icon, and then enter any DAX expression in the format that returns a table TableName = DAX Expression that returns a table Once you do that, then you should be able to see the resultant columns in the new table.

Calculated table

II) When is the data in a Calculated Table processed

The official blog says quite a few things on the same-

    • A Calculated Table is like a Calculated Column.
    • It is calculated from other tables and columns already in the model and takes up space in the model just like a calculated column.
    • Calculated Tables are re-calculated when the model is re-processed.

So based on this information, I am going to go a step further and assume that the data in a calculated table is processed during the ProcessRecalc phase of processing. Also, this means that every time any of the source tables changes (like a new calculated column or new data), the data in the calculated table will also change. To prove this, let us try a simple experiment-

1) Make a calculated table called Test which will be the same as the Date table (which currently has just the Year column).

make same calculated table

Note that measures from the source table are not brought along to the calculated table, which is as expected.

2) Now go to the Date table (which is our source table in this case) and then add a new calculated column called TestColumn with 1 as the value.

column replicated in calculated table

Note that when we added a calculated column in the source table, the column was replicated in the calculated Table also with the same name. The only difference is that the source table shows an icon for calculated column. This shows that the ProcessRecalc that happens in the source table when a new calculated column is made, also recalculates the calculated table.

III) My thoughts on Calculated Tables

Based on my understanding so far, there are times when I think I should use calculated tables and times when I should not use calculated tables. So here it goes –

a) When NOT to use calculated tables

If you have a way of replicating the calculated table in some form of ETL or source query (even a SQL View), you should not use a Calculated table. Why? A couple of reasons

  • If done from ETL / source query, the engine will see the result as a regular table, which means parallel processing of the tables can be done (unlike now, where the ProcessData phase of the source tables have to finish first before the calculated tables can be processed). So calculated tables could lead to slower data processing time.
  •  A ProcessRecalc happens every time you do a process full, and adding more calculated tables (as well as calculated columns) unnecessarily will increase the processing time. Also, during development of very large models, you might have to wait for a long time after each calculation is made for the data to appear, since all dependent tables will also have to be recalculated (unless you turn off the automatic calculation mode).
  • This one is more an assumption and further research will be needed to validate this, but I am putting it forward anyways. Just like a calculated column is not that optimized for data storage compared to a regular column, I suspect that a calculated table will also not be optimized for storage compared to a regular table. If this is true, this might affect query performance.

b) When to use calculated tables

There are a lot of scenarios where you would want to use calculated tables and I have listed a few of the scenarios below

  • During debugging complex DAX expressions, it might be easier for us to store the intermediate results in a calculated table and see whether the expressions ate behaving as expected.
  • In a lot of self-service BI scenarios or Prototype scenarios, it is more important to get the results out there faster and hence, it might be difficult or not worth the effort to fiddle with the source queries. Calculated tables can be a quick and easy method to get the desired table structures.
  • A kind of scenario that I see very often during prototyping / PoCs is when there are 2 facts in an excel file which are given for making reports. As seasoned users of Power Pivot / Power BI, we know that we will have to create a proper data model with the dimensions. Now, I might need to create a dimension which gets me the unique values from both the tables. For eg, in the below example, I need the Country dimension to get values from both the tables (USA, Canada, Mexico). It might be easier to write an expression for a calculated table like shown below
    Country = DISTINCT(UNION(DISTINCT(Table1[Country]), DISTINCT(Table2[Country]))) 

2 fact tables with no dimension data

  • There are times (like in some role playing dimensions) where you will need to replicate the columns / calculated columns (or even delete) in another table also, even if the columns are built at a later phase in the project. Calculated tables are a perfect match for that, as you will only need to make the changes in one table, and the changes will flow down to the calculated table during ProcessRecalc phase (remember our previous example where we created a TestColumn in the Date table, and the change was reflected in our calculated table also).
  • Calculated tables can help in speeding up DAX expressions. This is very similar to the technique of using aggregate tables in SQL database to speed up the calculations in SQL.
  • Quick and easy way to create calendar tables (like Chris showed in his blog)

This is still a very early stage as far as Calculated tables are concerned, and I am pretty sure we are going to see some very innovative uses as well as benefits of calculated tables in the days to come. I might also learn that some of my assumptions are wrong, and if I do, I will come back and update this post to the best I can. Meanwhile, feel free to comment on your thoughts / concerns / feedback.

Update – 9/28/2015

Transition Matrix using Calculated Tables – Alberto Ferrari
Use Calculated Table to Figure Out Monthly Subscriber Churn – Jeffrey Wang

Posted by SQLJason, 0 comments
Power BI Tip: Making similar sized KPI Boxes / Charts

Power BI Tip: Making similar sized KPI Boxes / Charts

August 20, 2015

Recently, I got asked by one of my readers if there is an easier way to make similar sized KPI boxes or charts in Power BI, other than manually resizing each individual visualization. As you know, making similar sized KPI boxes and / or charts are a design technique to make your reports symmetric and more aesthetically pleasing. Currently Power BI does not offer us a way to key in the width / height of the visualizations and it might seem like manual resizing is the only option. This tip is a much more simpler and precise way to do the same.

image

I) Making Similar Sized Charts

Making similar sized charts are easy. For that, click on the completed chart that you want to copy and then press CTRL + C on the keyboard to copy the chart. After that, press CTRL + V to paste the chart.

1 Copy paste chart

Now, you can go ahead and change the dimensions and measures of the second (and also the chart time), but one at a time. You should have a same sized different chart now.

2 Changing chart

The reason why I said to do it one at a time is because you will lose the chart if you remove all the dimensions and measures.

3 Losing hcart when you remove all dimensions and measures

II) Making Similar Sized KPI Boxes

Now this is a little bit more trickier. Let’s say I use the same CTRL + C, CTRL + V to copy paste the textbox.

4 Copy paste textbox

Now when I try to change the measure, notice how I can’t just replace the existing measure with the new one. I tried putting it on the card as well as on the Fields section. And when I try to remove the measure from the Fields, the entire card disappears.

5 Trying to replace measure

To get this to work, first change the chart type to something else, say a pie chart. Then drag and drop the new measure into the Values section. Make sure to remove the old measure and then revert back to the card visualization. Voila, now you have two KPI boxes of the same size.

6 Duplicating card by changing chart type

Now that you know this trick, go forth and make some pretty dashboards in Power BI!

Power BI Dashboard

You can also make some cool indicators on your KPI box with this trick that I showed in a previous post.

Note : Created using Power BI Desktop version listed below

image

Posted by SQLJason, 0 comments
Using DAX to make your Power BI DataViz more meaningful

Using DAX to make your Power BI DataViz more meaningful

August 13, 2015

One of the best features I like about Power BI Desktop is that the data acquisition, data modeling and data visualization functionalities are all integrated in a user friendly way. I don’t have to leave the Power BI desktop to perform any of these common operations when I am playing with my data, unlike so many other tools. Why is this important? Because you tend to be more productive when you have everything you need in one place and also, you tend to be more creative when you have the power to model the data along with making your visualizations. DAX has some really powerful data modeling capabilities, and when you couple that with Power BI, you can start giving more meaning to your visualizations and get faster insights. I recently made a video on my company’s blog site on how to analyze Promotion Effectiveness and for the same, I was using a dashboard made in Power BI Desktop. I am just highlighting two examples of how I used DAX to make my visualizations better.

I) Make better Sparklines by Highlighting

In my Promotion Effectiveness Analytics demo, the sparklines highlight (instead of just filtering) the value for the months where the selected promotions ran. This gives us a better understanding of what is happening before, during and after the promotion. Now, this is not possible out of the box in Power BI, but with just a little bit of DAX magic, we can make this work.

image

1) First let us see how to build a simple sparkline in Power BI. For that, select the month & sales and make it as a line chart.

1 Line Chart

2) Next, let us remove all the format options so that it looks like a sparkline. Also feel free to resize it

2 Power BI Sparkline

3) Make a new bar chart for Sales by Promotions, so that we can use it to filter the sparkline.

3 Bar chart for Promotions

You can see that the sparkline automatically gets cross-filtered to only the months where the promotion ran. This is great but what would add more value is if we could see the months highlighted instead of just filtered. That would let us know how the sales are before, during and after the promotions instead of just during the promotions.

4) Make a new measure by clicking on the dropdown next to the table, and then use the formula below

Sales Amount Total = CALCULATE([Sales Amount], ALL(Promotion))

4 Add new measure

Basically, we are making a measure which will show the Sales irrespective of whether the Promotions are filtered.

5) Now add the new measure to our sparkline (make sure to give it a lighter color like light grey for a better effect).

5 New Sparkline

Now you can see that the the grey line shows the sales for all the promotions while the green line highlights just for the selected promotion. You can also use this technique in other creative ways, for e.g., to highlight the max and min points of a sparkline.

6 Low High

II) Waterfall charts for Measures

In my Promotion Effectiveness Analytics demo, I created a Waterfall chart to show the breakdown of Customer Visits. Basically,

Customer Visits (This Year) = Customer Visits (Last Year) – Lost Customers + New Customers

I have individual measures for each of those, and in this case, a waterfall chart would be a great way to show the breakdown. However, we can only put columns in the category axis for waterfall chart in Power BI. But with some data modeling, we can get this done.

image

1) Make a dummy dimension called Customer Retention with just one column and 3 values – Last Year, New and Lost. I just made the dummy table in a text file and imported it to Power BI.

image

Note that this is a disconnected table and will have no relations to any other table.

2) Create a new measure called Customer Visits as shown below

Customer Visits = IF(HASONEVALUE(‘Customer Retention'[Customer Retention]),
                        SWITCH(VALUES(‘Customer Retention'[Customer Retention]),
                                “New”, [New Customers],
                                “Lost”, [Lost Customers],
                                “Last Year”, [Visits (LY)]))

Basically you are assigning the appropriate measures for New, Lost and Last Year based on the values for the disconnected Customer Retention table.

image

3) Now just make a Waterfall chart with the Customer Retention column and Customer Visits measure to clearly see the breakdown.

7 Waterfall Chart Power BI

Hope you got some ideas on what all we can do when we combine DAX with dataviz. Stay tuned for more as we expect to see Microsoft release more functionalities around the tool.

Note : Created using Power BI Desktop version listed below

image

Feel free to watch my video on analyzing promotion effectiveness by clicking on the image below

Demo Day: Analyzing the Effectiveness of Promotions in Retail

Posted by SQLJason, 7 comments
How to add an Indicator to Power BI Desktop

How to add an Indicator to Power BI Desktop

July 25, 2015

Yesterday was an exciting day – Microsoft released the GA version of Power BI and with it comes an impressive list of new features and functionalities. One of the new features that I was really interested in was – Rich control over visual coloring, including conditional formatting in Reports. This opens up a whole new possibility of tweaking Power BI even when the feature is not available out of the box. Today’s blog is going to be an example of that! (NOTE: This workaround is needed to add an indicator on the version that was released on 7/24/2015. The Power BI team moves really FAST with updates and there is every possibility that some features which make this workaround redundant, might be added sooner than later.)

PBI

Adding a KPI to a card is easy and helpful. But what makes it more useful is if there is an indicator which gives an idea on how the KPI is performing. Right now, there is no way to add an indicator out of the box in Power BI. But thanks to the new features, we can implement a workaround which is not that perfect, but will work for now. The finished dataviz looks like below

image

Note that there are 3 features here that look like it is not possible out of the box:-

1) There is an up / down arrow which changes based on the Year over Year (YoY) change.

2) There is a bar indicator on the top of the arrow which changes color (red or green) based on YoY

3) The background for the entire KPI box has a custom color (Neither the card data visualization nor the text box has formatting capabilities). So how is this done? Follow the steps below:-

Up / Down Arrow

Currently, there is no way to add a dynamic image in Power BI. You can’t import a binary type image data into Power BI neither add the image URL. A static image will not do for our purpose as we want the arrow to change based on data.

1) Let’s say that I have a measure called YoY which is basically the difference of Current Year sightings and Previous Year UFO sightings. Now make a new measure called YoY change which is YoY Change = [YoY] & ”  ” &  IF(SUM([Sightings]) >= [Prevyear], “⇧”, “⇩”) Yes, the entire trick is based on using the Unicode characters for arrows as the indicator. Unicode characters will work without any issue and can be used in regular DAX measure expressions. The above measure will show an up arrow if the current year is greater than or equal to previous year, else down arrow. You can also explore the other Unicode characters if you want to look for alternative symbols.

2) Now we can just use this new measure in a card. The measure expression will ensure that the Up arrow or Down arrow gets displayed accordingly.

1 Power BI Indicator

Bar Indicator with Color on top of Arrow

Currently, there is no way to format a text box or a card viz. Else that would have been a good way to achieve this functionality.

1) For this demo, I have made a table with 1 column called Meaning with 2 rows – Up and Down. And then I made a simple measure that displays 1 for Up if the current year is greater than or equal to previous year or 1 for Down if the reverse is true.

PosNeg = SUMX(VALUES(temp[Meaning]), IF([Meaning]=”Up” && SUM([Sightings]) >= [Prevyear] || [Meaning]=”Down” && SUM([Sightings]) < [Prevyear],1))

2) Make a bar chart out of the Meaning column and the new PosNeg measure.

2 Power BI Indicator

3) Click on the Format tab (on the right hand side panel) and then ensure that all properties except Y axis has been turned off. Then expand the data colors and choose the default color a Green. You can also click on the Show All property and manually make sure that the Up value is Green. (If the default value for your data is Down, then make sure that the color is Red).

3 Power BI Indicator

4) Now choose a filter condition which will show the bar chart for Down value. After that, go to the Format option and choose the color for Down as Red.

4 Power BI Indicator

Power BI remembers that Up has a color of Green and Down has a color of Red. You can test it by toggling the filters / slicers.

5 Power BI Indicator

5) Make sure the transparency of the background is set to 100% (don’t turn it off as shown in the image below) and also turn off the Y axis now. Resize the bar chart and place it above the previous card viz for Up / Down arrow indicator.

6 Power BI Indicator

Now you have got a nice color indicator also!

Background for KPI Box

Right now, there is no way to add a background color to the KPI box, as we cannot format the card data viz or the textbox viz. But what we can o as a workaround is to select any column, make it into a bar chart and then turn off all the options so that we get a blank rectangle. Then go to the background option and choose the color of your choice.

7 Power BI Indicator

Feel free to drop in the previous two indicators that we made into the colored rectangle and now we have got a much prettier and useful KPI box. There is a lot of different ways in which you can mix and match this technique. For example, how about using a bubble indicator instead of the bar?

8 Power BI Indicator

Note that these dataviz can not be pinned to a Dashboard, as we can not overlay visualizations in a dashboard, However, these will work just fine if you upload it to a normal Power BI Report. Having the control over formatting has greatly increased the ways in which you can tweak the reports and I am loving it. Looking forward to what all surprises the Power BI team has for us now!

Posted by SQLJason, 10 comments
QR Codes in Power View

QR Codes in Power View

November 19, 2013

Today, I was reading a good article by Florian Mader – A GPS Photo Gallery in Power BI. That inspired me to write something on Power View and since I had not originally planned to write a blog today, I decided to write something short.   QR Codes in Power View QR Codes are not available by default in Power View and it is normal to hear many people say that it is not possible in Power View. The reason behind such a conclusion is because Power View is not at all flexible and you don’t even have simple options to change your chart colors, let alone program a new chart type (which is not a bad thing, as the focus is on ease of use and simplicity. More options would make the tool more complex). However, in this case, we can utilize the power of the internet and Image URL feature of Power Pivot to generate QR Codes. For this post, I am using the model below with some sample data. Model + Sample Data The fact table consists of Customer name, Brand Name, Date and Sales. The requirement is to display the QR code for the selected Brand. For that, follow the steps below:- 1) To create the QR code, it is necessary to create a calculated field in the data model. Since the QR code should be there for each brand, the calculated field should also be in the Brand table. The formula for the calculated field QRCode is given below =”http://qrcode.kaywa.com/img.php?s=8&d=” & [Brand] & “
” & [URL] QR Code Formula My regular readers might remember this URL from my post – Generating QR Codes in SSRS. There are other QR code generating sites also, I am just using this as an example. 2) Once the URL is generated, make sure that the Data Category property in the Advanced tab is set to Image URL. Data Category property 3) Now we can use the QRCode field in Power View to display the QR Codes. You might get a warning to enable external content, which is normal. QR Code in Power View 4) We can also use it along with other visualisations like shown below. QR Code along with other visualizations in Power View The output of the QR Code from my smartphone is given below:- Output Limitations Now that said, there are a few limitations. It is absolutely necessary to have a calculated field to make the QR Codes. This means that the values will be pre-computed and we can not add a measure or some value that changes during the run time of the report to the URL of the report. Also, we can not make much changes to the size of the QR Codes as we don’t have a lot of control on that. This technique can also be used to get other chart types into Power View. However, the same limitations will apply. That is all for now, time for me to get back to baby-duty Smile.

Posted by SQLJason, 0 comments
Category Shading for Regions in Power Map

Category Shading for Regions in Power Map

November 12, 2013

It’s been a really busy month for me professionally (with the PASS Summit 2013,  SQL Saturday Charlotte BI Edition, etc) as well as personally (with a steady stream of family flying in to visit the new born baby as well as looking after a new born baby – how I miss my 8 hrs. of sleep!). I remember people saying to me that things are going to change drastically once you have a baby. Well, what can I say other than it’s 100% true Smile. Understandably, I don’t get the same amount of time to spend on blogging and with all the stuff that has been happening behind the scenes, I decided to procrastinate till everything gets a little bit more calmer. That is when I was reading through an article and fell upon this gem of a line from Zip Zaglar – “If you wait until all the lights are “green” before you leave home, you’ll never get started on your trip to the top”. So I decided to shake off my laziness and get started on blogging about a cool feature in Power Map that has not been explored or blogged much – Category Shading for Regions.

Category shading within Regions in Power Map

The objective of this post is to explore the category shading feature in Power Map as well as try to see what kind of questions can be answered (after all, a feature that just adds flashiness and no business value is not desirable). For this post, I will be using the stats for my blog for visitors from the USA by browsers.

Sample data from my blog stats- 2013 data

With this data, I created a simple Power Map visualization which shows the number of visitors by state. (Check out this link if you are new to Power Map or need more info on how to create this visualization).

Region shading in Power Map

With the above visualization, I can answer questions like – Which state in the US has the most number of readers? I can easily see that California and Texas are where I have got the most number of readers. Now that I have a general understanding of the visitors, I am going to ask a few more specific questions based on the browsers that they are using:-

I) What is the dominant browser for my readers in each of the states?

To answer this question, bring the Browser field to the category and now you should notice an icon which appears on the top-right of the category textbox (denoted by 2 in the image below). Click on the icon and select the No shading option as shown below.

Category shading in Power Map - Show full value

Now, I can see the results for myself. Interestingly, the dominant browser in almost all the states is Internet Explorer. There are a couple of states (6 to be precise) where Chrome is the dominant browser. This brings me to my next question.

II) How dominant are the leading browsers within each state with respect to their competing browsers in that state?

Click on the category shading icon and select the “Shade based on category value within location” option. Now you should see the below visualization.

Category shading in Power Map - Shade within location

From this, you can easily see the leading browsers based on the color as well as the shading shows the share for the leading browser. The darker the shade for the color, the more dominant the browser is within the region. For eg, you can see that Internet Explorer is pretty dominant in Alaska as well as North Dakota, while Chrome is pretty dominant in Wyoming. In California, you can see that IE is leading but not by much.

tooltips

However, when I saw the actual numbers in the tooltip, I found it to be really less. This brought me to my third question.

III) Which states have more visitors and what is the dominant browser there?

Click on the category shading icon and select the “Shade based on category value compared to all other values” option. Now you should see the below visualization

Category shading in Power Map - Shade compared to all other values

You can see that it is a refined version of the map which was obtained initially by not putting the category field. The darker the color, the more number of visitors I have. So from the above image, we can easily see that the top 4 states by readership counts are California (Internet Explorer), Texas (Internet Explorer), Washington (Internet Explorer) and North Carolina (Chrome). And now looking at the previous image (for Question II) in combination with this image, we can see that the difference is not much and that the competition between the browsers are pretty tight across the states. Also, for the three states where the difference is really big (Alaska, North Dakota and Wyoming), we can see that the counts are pretty small and hence not that significant.

IV) How do the states within the same dominant browser measure up against each other?

For this, click on the category shading icon and select the “Shade based on category value compared to other values in the category” option. Now you should see the below visualization.

Category shading in Power Map - Shade compared to other values in category

From this image, I can see that for Internet Explorer, the top 3 states are California, Texas and Washington & for Chrome, the top 3 states are North Carolina, Iowa and Utah. It is important to understand that there might be other states which has more value for that browser. For eg, California might have more readers using Chrome than North Carolina, but it is not taken into account since Chrome is not the dominant browser there. Time to finish this lengthy post and this will be all from me today. But before I sign off, I wanted to let you know that you can animate this kind of visualization across time by dragging the date field to the time-play axis. This can answer the same 4 questions we asked above across time, which is pretty powerful. Also, we can look at the existing set of data with Power View and answer more interesting questions – a sneak peek of a simple dashboard is given below. Have fun with Power BI till we speak again! Smile

Power View dashboard - Browser Wars

Posted by SQLJason, 3 comments
Power Map Preview – Exploring the NEW features!

Power Map Preview – Exploring the NEW features!

September 11, 2013

Woot! Microsoft just announced the pre-release availability of the refreshed builds for Power Map with some really cool new features and boy, I must say that I am PUMPED! For folks that know me, I have been doing quite a number of presentations on Power Map (and geospatial analytics in general) and there were a couple of features that I really really wished for in Power Map. Well, looks like the Power Map team heard my requests and I feel this release is a big step forward.

Power Map preview for Excel - new features

First of all, here are some useful links (including the download link for the refreshed bits):-

Now let’s see some of the new features that has come up since my last post on Power Map.

I) Recognizing geographic data and Plotting them on launching Power Map On launching Power Map, the columns with geographic data are automatically recognized and plotted. For example, I had some data of the population of North Carolina by zip code as shown below

sample data

Now when I launched Power Map, this is what I saw

recognizes geo data and plots them

The best part is that the geographic columns seemed to be recognized correctly even if the they are named something other than City / State / Zip Code, etc. For eg, I renamed the City to CVDS and still was able to see it correctly mapped to City.

works with different column names also

And anyways, you still have the option to change it from the drop down. All in all, great feature and saves me a couple of unnecessary clicks.

II) Support for Flat Map

I know, I know, all of you would be thinking – what’s so special about this? After all, what made Power Map unique was that it had the ability to do 3-D geospatial analysis. Well, that is pretty cool but there are certain times when only a flat map would do. For eg, when you are looking at a worldwide data like population by countries, you want to see both sides of the globe. Earlier in Power Map, it used to look like shown below:-

3D map - power map

We could see only one half of the globe and had to rotate to see the other half. Now we have this icon to convert the 3D map to a flat map (and back to 3D if required!)

Flat map - Power Map

This is REALLY cool! Open-mouthed smile

III) Support for Regions Visualization (Chloropeth Mapping)

Now this is the most awesome enhancement according to me – support for chloropeth mapping or shading a geographical area (and I had actually requested as a Connect suggestion during the beta release of GeoFlow). Let me show you how the visualization looks for the population data for North Carolina

Region Visualization - Power Map

Best part is that it is damn right simple – just change the visualization from Column to Region (and yes, that also means that we have a fourth visualization type now – apart from Column, Bubble and HeatMap)

new visualization type in Power Map - Region

The Region visualization is only present at a Country / Region, State / Province, County or Zip / Postal Code level (which means City is not available) and is available across all countries (not just the USA). If you try to use a geographical level which is not one of the above, you will receive the following error and the chart type will be changed to Column.

Power Map - Region charts work with Country/Region, State/Province, County, or Zip/Postal Code data

I LOVE this feature and more than that, adore the way how simple it is for users to geocode at that level (no need to bring in complex shapefiles to geocode county/zip code level data like in SSRS!). I can already see a lot of use for it.

IV) Change Color for Data Series

Now this is another simple but very useful new feature – the ability to change colors for data series. You can see this new option in the Layer options.

change color for data series

You can also see that the display options of the Themes button has changed and now the colors also show up.

themes

V) Exporting Tours as Videos

A question that consistently makes the rounds when I present on Power Map is – “How do I “unlock” the tours from the Excel workbook? Is it possible to share it through any other medium?” Well, the answer has been No until now. The current build allows you to export your tours as a video and now this can be shared across all your channels. For creating videos, there is a new Create Video button on the ribbon and then you can select the video quality also.

image

To demonstrate the feature, I have exported a tour from one of my recent presentations. This tour is around 5 mins long and investigates the myth – Are Refugees flooding Industrialized countries? It took quite some time for me to export the tour as a video even in the 360p mode, but that is understandable I guess.

Power Map Tour exported as a video

Conclusion

Overall, I feel this is a build with some really awesome features. Apart from the new features I described, there are also some UI changes but these changes are simple enough to understand, so I am not going over them. There are still some really basic functionalities that are desired like filtering your data and drilling down on the geographic levels (and I found out at least one bug which I will be filing on Connect soon), but we should not forget that this is not the final version. So I am happy and I would encourage everyone to try out the new build.

Posted by SQLJason, 11 comments