Some Random Thoughts

Sep

2015

My Thoughts on Calculated Tables in Power BI

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.

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 ) – [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

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.

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.

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).

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.

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])))

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

Sep

2015

SSRS Line Chart with Data Tables (Excel Style)

It takes a lot of discipline and dedication to run a blog properly and that is one of the main reasons why I admire bloggers and tech gurus like Chris Webb (who has been putting out high quality blogs for ages in a consistent manner!). Sadly, I am not very disciplined when it comes to writing blogs and it takes a significant external force to make me write nowadays. I had written a blog almost 3 years ago on how to create SSRS charts with data tables like in Excel and from then onwards, I have had a lot of readers ask me on how to do the same with line charts (both through comments as well as emails). I knew it was possible but was too lazy to write a blog on it, until I had 2 readers ask me the same question yesterday in the comments. Finally, I decided to check it out and write about it. The solution is not perfect and is more of a workaround but should be ok for most of you I guess.

The allure of Rolex watches is undeniable. Renowned for their precision, luxury, and timeless style, Rolex has become a symbol of status and success. However, owning a genuine Rolex watch comes with a hefty price tag, making it unattainable for many watch enthusiasts. This is where Rolex replica watches come into play. In recent years, high-quality Rolex replicas have gained immense popularity for their exceptional craftsmanship and resemblance to the real deal. In this article, we’ll explore the world of Rolex replica watches, where to find the best super clone 1:1 copies, and what you should consider before making a purchase.

The Rise of Rolex Replica Watches

The demand for Rolex replica watches has grown steadily over the years. These replicas have become more than just imitations; they are often referred to as “super clones” due to their astonishing accuracy in replicating the original Rolex design, movement, and functionality. The rise of super clone Rolex watches can be attributed to several factors:

Affordability: Authentic Rolex watches come with a price tag that often exceeds the budget of the average consumer. Rolex replicas, on the other hand, offer a cost-effective alternative for those who desire the prestige of a Rolex without breaking the bank.

Quality Improvements: Advances in manufacturing techniques and materials have enabled replica watchmakers to produce highly detailed and meticulously crafted super clones that are almost indistinguishable from the genuine Rolex timepieces.

Accessibility: With the advent of e-commerce, it has become easier than ever to find Rolex replica watches online. Numerous websites and sellers cater to this growing market.

Where to Find the Best Super Clone Rolex 1:1 Copies

While there are numerous sources for Rolex replica watches, it’s essential to exercise caution when making a purchase. Counterfeit products and low-quality imitations are abundant in the market, so it’s crucial to do your research and buy from reputable sources. Here are some tips to help you find the best super clone Rolex 1:1 copies:

Reputable Online Sellers: Several trusted online stores specialize in high-quality replica watches. Look for websites with a good reputation, customer reviews, and clear policies regarding the quality and authenticity of their products.

Ask for Recommendations: Seek advice from fellow watch enthusiasts who have experience with replica Rolex watches. They may recommend trustworthy sellers or websites.

Study the Details: Pay close attention to the product descriptions, specifications, and high-resolution images provided by the seller. The best super clone Rolex watches will closely resemble the authentic models, down to the finest details.

Reviews and Feedback: Read reviews and feedback from previous customers to gauge the quality and reliability of the seller. Genuine customer testimonials can provide valuable insights.

Warranty and Return Policy: Ensure that the seller offers a warranty or return policy, as this indicates their confidence in the product’s quality.

Considerations Before Purchasing a Rolex Replica

Before purchasing a Rolex replica watch, it’s essential to consider the following:

Legal and Ethical Considerations: Rolex is a protected trademark, and selling counterfeit Rolex watches is illegal in many jurisdictions. Ensure that you understand the laws in your area and the potential consequences of owning a replica watch.

Your Motivation: Be clear about your reasons for buying a replica. If you’re looking for a quality timepiece that emulates Rolex style, a super clone 1:1 copy may be a suitable choice. However, if your intention is to deceive or pass it off as an authentic Rolex, this is both unethical and potentially illegal.

Maintenance and Care: Just like genuine Rolex watches, replicas require maintenance to ensure their longevity and accuracy. Be prepared to invest in regular servicing.

Conclusion

Rolex replica watches, especially super clone 1:1 copies, have become a popular choice for watch enthusiasts who appreciate the elegance and craftsmanship of Rolex timepieces but may not have the financial means to own an authentic Rolex. While replica watches offer an affordable alternative, it’s crucial to exercise caution, do thorough research, and buy from reputable sources to ensure you receive a high-quality product that meets your expectations. Keep in mind the legal and ethical considerations surrounding replica watches and enjoy your Rolex-inspired timepiece responsibly.

For illustrating the solution, I am using a simple dataset which shows the sales by Product and Month.

To follow this solution, you must be familiar with the technique I mentioned in the previous article. If you have not read that, please the previous article first and then follow the steps below 1) A lot of readers already found out that if the technique described in the previous article was used, then we will only get points and not actual lines. So, the very first step here is to modify the source query such that for every actual point in the line chart, we get 2 more points which gives the start and end for that point. With this, now we will have a line joining 3 points for what would just have been one point before.

;WITH   Src
AS     (SELECT Product,
               MonthNo,
               Month,
               Sales,
               CASE
WHEN MonthNo = 12 THEN NULL ELSE (lead(Sales, 1, NULL) OVER (PARTITION BY Product ORDER BY MonthNo) + Sales) / 2
END AS LeadSales,
               CASE
WHEN MonthNo = 1 THEN NULL ELSE (lag(Sales, 1, NULL) OVER (PARTITION BY Product ORDER BY MonthNo) + Sales) / 2
END AS LagSales
        FROM   (<Source Query>) AS O)
SELECT Product,
       MonthNo,
       Month,
       ‘1’ AS Type,
       CAST (LagSales AS FLOAT) AS Sales
FROM   Src
UNION ALL
SELECT Product,
       MonthNo,
       Month,
       ‘2’ AS Type,
       CAST (Sales AS FLOAT) AS Sales
FROM   Src
UNION ALL
SELECT Product,
       MonthNo,
       Month,
       ‘3’ AS Type,
       CAST (LeadSales AS FLOAT) AS Sales
FROM   Src;

Note that LeadSales column is actually the (Sales for next point + Sales for Current Point) / 2 and LagSales column is actually the (Sales for previous point + Sales for Current Point) / 2. This will help us get a smooth line when we join our different lines. Also, we have to ensure that for the first and last points, NULL values are assigned. The bottom part of the query brings all three columns (Sales, LeadSales, LagSales) into a single column called Sales but each one is assigned a different Type.

2) Repeat the steps 2 and 3 in previous article to make the matrix and the two rows above it.

Also add the Type column to the row group, delete the columns only and then filter the Type group for 2 only. The reason is that we only want the actual Sales to be shown in the data table, which is 2. Type 1 and 3 are used for the sole purpose of making the line chart.

3) Now you should be able to follow the rest of the steps in the previous article with the sole exception that you will be using a line chart and not a bar chart.

Make sure that you set the CustomInnerPlotPosition and CustomPosition appropriately like in step 10 in the previous article, so that graph appears continuous. I used the below settings for this line chart.

4) Instead of step 11 in the previous article, I chose to make a new column to the left for the vertical axis, and just made sure that the vertical axis for the line charts all have the same scale.

Note that the series expression for this column is just 0, and there are no category or series group. This ensures that we just get a dummy line for the axis. You can start hiding the orders to ensure that the graph looks continuous.

5) I also added an expression such that the markers and tooltips only show if the type is 2.

6) With all these changes and a bit of formatting, we can get the below result

This should be good for most people. However, there is one minor drawback which is that the lines do not join that smoothly. I have just zoomed in one part so that you can see the issue. Maybe, this could be solved by fiddling along with the properties some more, but I feel this is not that big of an issue.

Hopefully this will put to rest some of the questions I keep getting on data tables in SSRS, so that I can go back to my lazy self (just kidding)

Aug

2015

Power BI Tip: Making similar sized KPI Boxes / Charts

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.

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.

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.

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.

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.

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.

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.

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

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

Aug

2015

Using DAX to make your Power BI DataViz more meaningful

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.

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.

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

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

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))

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).

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.

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.

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.

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.

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

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

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

Jul

2015

How to add an Indicator to Power BI Desktop

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.)

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

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.

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.

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).

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.

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) 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.

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.

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?

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!

May

2014

Ranking within Slicer Selection in Power Pivot

I know it’s been ages since I posted something, and I do have lots of lame excuses so maybe it deserves a post on it’s own. But for now, I want to jot down a workaround on how to rank a column based on the slicer selections for the same column.

The other day, I was working on an excel dashboard and I got a requirement to rank a field while the field values are there on the slicer. While I am used to getting requests on ranking a field based on a measure, this was the first time someone was asking me to rank a field based on a measure while the column was on the slicer. The user just wanted to rank within the selected values of the field in the slicer and not as a whole. To explain this, I just made a very simple example with the model given below

The FactLicense table contains data on new liquor licenses and I made a simple measure License Count to count the number of licenses. The FactLicense table is related to the Geo table through the Zip Code column. The requirements were that

the City field should be ranked by License Count
there should be a slicer displaying the values for City
the ranks should update based on the Slicer selections

Solution

1) To solve the first part of the requirement, I made a simple ranking measure by City as shown below.

LC Ranked by City:=RANKX(ALL(Geo[City]), [License Count])

You can see the results in the pivot table below.

2) For the second requirement, we can just add a slicer for the City field and then connect it to the Pivot Table.

3) Now the third requirement is the interesting one. When I try to filter by some cities, I get the result below.

You can see that the Ranks don’t get updated based on the slicer selections. The user wants the ranks to start from 1 but you can see that the ranks are still based on the entire city list and not just the selected city list. Now it is impossible (atleast as far as I know) to reset the ranking if the same field is on the rows as well as the slicer. The reason is that the Rank measure operates on ALL(Geo[City]) and hence removes any filter or selection that is already made. So what do we do?

4) A simple workaround is to make a calculated column (say City Slicer) and make the Slicer based on this new calculated column.

Make sure to remove the old slicer as well as connect this new slicer to the pivot table.

5) Now when we select the City in the slicer, you can see that the Ranks get reset.

You can further rename the Slicer such that it says only City and you can hide this calculated column so that it is not visible to the other users analyzing the model. This will help avoiding confusion among the end users (Eg – why do we have 2 city fields?) and at the same time, you can make your Excel dashboard with the fulfilled requirements.

Update

Within seconds of posting, Miguel Escobar (twitter) comes up with a much better way of doing this using the ALLSELECTED().

LC Ranked by City1:=RANKX(ALLSELECTED(Geo[City]), [License Count])

This would be the ideal way to do it as we don’t have to create a new calculated column and still achieve the same results. Thanks a lot for this Miguel!

Feb

2014

Download link for my 24 HoP Session

It’s been almost 3 months since I blogged and this has been the longest time I have been away from this space since I started blogging. There are a couple of reasons behind it (which included the suspension of my blog by Google for allegedly hosting malicious code!!!) but more on this later. For now I just wanted to post the download link for the demos used in my 24 Hours of PASS session – DataViz You Thought You Could NOT Do with SSRS. The report solution files are available for download here. The rdl files for the 8 reports that I showed as well as the shared data sources are present in the zip file. You can run the reports by pointing the shared data sources to your local database for most reports. However, the following reports will not run as additional information or data is required – Squarified Tree Map.rdl and MCFC.rdl. However, you can learn more about these two charts by visiting my earlier blogs on Heat Maps for SSRS using Map Control and Linking and Brushing Visualization with SSRS. This was a sneak peak of my session that I am presenting at the PASS BA Conference. For people who are wondering what this is all about – On February 5, BA and BI community experts presented a series of 1-hour webcasts delivering best practices and expert tips for getting the most from your data. This 24 Hours of PASS event provided a sneak peek at what you can expect at the PASS Business Analytics Conference May 7-9 in San Jose, CA. Sign up today and you can use my discount code BASF2A to save $150 off the registration price. The 24 Hours of PASS session recordings (which includes my session also) are now available for streaming. Access the recordings for free now.

Nov

2013

QR Codes in Power View

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 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.

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]

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.

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.

4) We can also use it along with other visualisations like shown below.

The output of the QR Code from my smartphone is given below:-

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 .

Nov

2013

Category Shading for Regions in Power Map

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 . 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.

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.

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).

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.

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.

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.

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

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.

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!

Oct

2013

PASS Summit & SQL Saturday Charlotte (BI Edition) 2013

In less than a week, I will be joining the biggest gathering of my #SqlFamily in my own place of residence – Charlotte, NC. The PASS Summit is easily the best conference for SQL Server professionals and is worth every single cent that you paid (or are thinking of paying). If you haven’t registered, make sure that you register for it now by following the link below- REGISTER NOW for PASS SUMMIT 2013 This year, we have also extended the #SqlFamily experience by conducting a SQL Saturday the day after PASS Summit in Charlotte. There is a balanced spread of BI, DBA as well as non-technical sessions and the schedule can be found here. So if you are here for the Summit, don’t forget to register for a FREE day of training and an extended experience with our #SqlFamily. The link to register is given below REGISTER NOW for SQL Saturday Charlotte (BI Edition) And if you are at either one of the events, don’t forget to say a hi to me at any of the below places:- – Pretty much floating all around the place during the Summit (Oct 16 – 18). – Oct 16 – Room 202 A-B 10:15 – 11:30 AM : Visualizing John Snow’s Cholera Map Using Microsoft BI – Oct 17 – Room 203 A – 1:30 – 2″:45 PM : Geospatial Analytics Using Microsoft BI – Oct 19 – Find me in the White Organizer shirt during SQL Saturday Charlotte BI edition Hope to see a lot of you pretty soon!

My Thoughts on Calculated Tables in Power BI

SSRS Line Chart with Data Tables (Excel Style)

Power BI Tip: Making similar sized KPI Boxes / Charts

Using DAX to make your Power BI DataViz more meaningful

How to add an Indicator to Power BI Desktop

Ranking within Slicer Selection in Power Pivot

Download link for my 24 HoP Session

QR Codes in Power View

Category Shading for Regions in Power Map

PASS Summit & SQL Saturday Charlotte (BI Edition) 2013

Follow me:

Subscribe to Blog via Email

Archives

Categories