Infographic using Power BI

With more demand and acceptance of data visualization, we have to deal with different kind of audiences. Some of those are not having analytical minds and simply grab information through graphics and cosmetics. There is also a need of awareness for masses for which statistical analysis and graphs are sometimes too much for general audience. In this scenario Inforgraphic is the best candidate to address these concerns.

I have experimented a report baased on open data of the government entity DEWA of Dubai. The result was amazing and really provide a simple information about operations and customers’ segmentation.

Report can be viewed through following link

Click here to view

Dynamic Infographics

Using Tickers in Power BI

Tickers are useful when you want to show information in floating form. Almost everyone is used to ticker like visuals in reporting as in situations like stock exchange information, game scores, weather report, news headline or otherwise tickers are fairly useful.

Click here to see report in action. 

In Power BI we have limited possibilities of animated content like tickers. In order to produce ticker like visual, I have tried two different approaches.

  • Using Card Visual and Play Axis Custom Visual
  • Using SVG through HTML Viewer Custom Visual

 

Using Card Visual and Play Axis Custom Visual

Ticker Power BI

In this method I have created different custom columns and measures.

“What if parameter” was used to create a table to help in animation of the text.

Length = GENERATESERIES(0, 150, 1)

“Ticker” measure was used to create content for ticker which will run from left to right. DAX REPT was used to create empty spaces on the right side as card visual always keep text in center. While next measure “LeftToRightTicker” will add spaces to the left of the ticker content based on changing values of “Length Value” in response to Play Axis visual. You can set play axis animation settings based on your needs.

Ticker = CONCATENATEX(VALUES(Outlets[CityOutlets]), Outlets[CityOutlets], ” | “) & REPT(” “,60)

LeftToRightTicker = REPT(” “, Length[Length Value]) & [Ticker]

Play Axis visual was used to change values in Length table created through “What if parameter”.

Using SVG through HTML Viewer Custom Visual

tickers in power bi

In SVG example, HTML is generated through custom columns for both simple and TV like visual. A custom column “Ticker” is used to create concatenated text for use in ticker which is ingested in the HTML to produce ticker.

SVGContentSimple = “<svg width=””100%”” height=””100″” style=””background: white””>

<text x=””10″” font-size=””10pt”” y=””50″” fill=””black”” >” & SummaryTable[Ticker] & ” 

<animate attributeName=””x”” from=””-120%”” to=””120%”” dur=””25s”” repeatCount=””indefinite”” fill=””freeze”” >

</text>

</svg>”

Source file can be reviewed for more details. Click here to download.

Reference for SVG images used in this example http://thoughtbot.github.io/foundry/

 

 

 

 

Animated Tooltips in Power BI

Tooltips are always good when you wish to keep your report viewers focused on a report by providing extra information on the same page through mouse over. Although default tooltip and custom tooltips are good for showing static information, you sometimes need to show animated content like trends and behaviors based on historic data.

Recently I did an experiment with tooltips by incorporating animated content. The experiment was fairly successful as lot of viewers on LinkedIn viewed my post and requested details about how this was implemented. For those who wish to experiment tooltip I have decided to pen down steps to help enthusiasts and professionals to try animated tooltips in their reports.

Mall Traffic Image

Click here to see report in action!

For this example I have created a sample report with fictitious data to show mall traffic. Nowadays, lot of tools are available to capture mall traffic with intelligent cameras and applications to recognize type of visitors. This example assumes that data file is having records of mall visitors bifurcated based on gender and age.

I have used calculated columns and measures to create dynamic titles of visuals to behave according to the selected content. A source pbix file is also included to enhance this example further.

Visitors were classified through following table. Unicode characters were used to show respective icons.

Table: Classification

Classification Unichar
Infants 128118
Boys 128102
Girls 128103
Men 128104
Women 128105
Cars 128662

 

Mall Traffic is shown in following table assuming that data is classified based on visitor type. Additionally a column is added to show traffic in terms of cars parked in the mall parking area. This kind of example may help mall management in identifying peak parking months.

Table: Mall Traffic (Note: this is not a full list)

Year Month Boys Girls Men Women Infants Cars
2016 1 23000 19000 34000 39000 5000 800
2016 2 18900 18000 40000 42000 7000 1100
2016 3 14800 17000 46000 45000 9000 1200
2016 4 10700 16000 52000 48000 11000 1450
2016 5 6600 15000 58000 51000 13000 900
2016 6 11000 14000 64000 54000 15000 1000
2016 7 21000 17000 59000 49000 14000 1100
2016 8 31000 20000 54000 44000 13000 670
2016 9 41000 23000 49000 39000 12000 800
2016 10 23000 19000 34000 39000 5000 930
2016 11 18900 18000 40000 42000 7000 1060
2016 12 19000 22000 46000 45000 9000 1190
2017 1 10700 16000 52000 48000 11000 1185
2017 2 21000 15000 58000 51000 13000 1180
2017 3 11000 14000 64000 54000 15000 1175
2017 4 21000 17000 59000 49000 14000 1170
2017 5 31000 20000 54000 44000 13000 1165
2017 6 41000 23000 49000 39000 12000 1160
2017 7 11000 15000 58000 51000 13000 1200
2017 8 11000 14000 64000 54000 15000 1215
2017 9 21000 17000 59000 49000 14000 1230
2017 10 31000 20000 54000 44000 13000 1245
2017 11 41000 23000 49000 39000 12000 1260
2017 12 23000 19000 34000 39000 5000 1000

 Model Diagram

Model Diagram below is showing relationship between two tables used. I have used additional columns and measures to perform analysis. Tables were transformed through edit query to make it analysis friendly. You may find query editing steps in sample pbix file.

Mall Traffic

 Play Axis Visual

I have used “Play Axis” custom visual to create animation on tooltip pages (You can import the same from the marketplace).  It was deliberately kept behind the clustered bar chart to work in background. “Year” column was selected in the field well to run tooltip based on years. In other tooltips, I have also used visitors’ classifications for animation.

PlayAxis Tooltip

Following settings were done in the format section of “Play Axis”.

PlayAxis Tooltip2

Calculated Columns and Measures

Following calculated columns were used for different reasons.

Calculated columns for creating icons

UnicharIcon = UNICHAR(Classification[Unichar]) 

Measures used for dynamic titles of different visuals and tooltips. These measures show values based on selections made through “Play Axis”.
AllYears = CONCATENATEX(VALUES(MallTraffic[Year]),MallTraffic[Year],” | “, MallTraffic[Year])
AllClassifications = CONCATENATEX(VALUES(MallTraffic[Icon]),MallTraffic[Icon], “| “)
AllClass = CONCATENATEX(VALUES(MallTraffic[VisitorClassification]),MallTraffic[VisitorClassification],” | “)
How to see animated tooltips.
Following visuals (marked with red arrows below) were configured for tooltips. You can check with mouseover to see tooltips in action. Keep holding your mouse to see animation effects. For cards, you have to keep your mouse over the figures and for line chart, keep mouse over the required node.
tooltips
This document is not covering method for creating tooltips as this is assumed that you are aware of the normal procedure of creating tooltip pages.
Following picture indicate how you can associate tooltip with your visual. However, you have to be sure that the field(s) used in your visuals are used in your tooltips otherwise it will not work.
tooltipsconfig
You may download complete files including sample Excel file from the following link.
Keep following my blog for more exciting examples.

How I passed my exam 70-778 Analyzing and Visualizing Data with Microsoft Power BI

When I passed my Power BI exam and shared my badge on LinkedIn I received hundreds of likes and numerous comments with views crossing 90,000 (still counting!). This shows immense interest in passing this exam which is sometimes difficult for those who are new in BI arena.

In order to help Power BI community I decided to pen down my journey to help other test takers follow similar path to achieve certification.

My First Attempt was unsuccessful

This is to be noted that I passed this exam in my second attempt. I am sharing this because this is not unusual for a person to fail in first attempt. I myself after having plenty of experience in Power BI failed to pass this exam and scored around 665 when passing score was 700. This has given me lot of hope that if I fine tune my knowledge then I will be able to clear this exam in my next attempt. What I learned from my first attempt that there were plenty of scenario based questions. These questions may eat your allotted time because if you are not reading it properly then you will try to read it every time. It is clearly mentioned from where repeated scenarios starts and ends so reading it once and understanding clearly will help you save your time. I spent a lot of time in my first attempt leaving less time to attempt remaining questions so I had to rush and that caused confusion and errors.

Importance of DAX – Data Analysis Expressions

I can easily say that most of the questions revolve around DAX so you must have sound knowledge of it. Understanding syntax, parameters and sample implementations will help you grip it fully. Hands on experience of DAX is absolutely necessary if you want to clear this exam. I did lot of practice for time intelligence functions because these are present in exam in good numbers. I downloaded a book namely “Data Analysis Expressions (DAX) Reference SQL Server 2012 Books Online” which helped me a lot. This is to be noted that some new functions introduced in DAX are not present in this book but most of the contents are relevant and seems that exam questions have referred this book a lot.

Data Transformation

Data transformation is very important in practical scenarios because your BI depends a lot on clean data. You must do proper practice on all data transformation techniques especially covering all menu options in Power BI desktop.

Maximum reading and learning

Microsoft provide tons of content for Power BI. You must plan your learning by going through common topics as some answers are hidden within the contents. I read lot of material and I must say that subscription plans was the most tricky part for me as it determine what can be shared with internal and external users. Secondly, you must look into configuration and setup part as well along with Office 365 and Azure related topics involving Power BI. There are plenty of videos and textual content available from Microsoft. Some links are shared below.

https://docs.microsoft.com/en-us/power-bi/guided-learning/

https://docs.microsoft.com/en-us/power-bi/

Brain dumps are good and bad

Brain dumps are good in a sense that they gives you real picture of how the test is arranged. You can say that 20 to 30 percent of questions do match in your real exam but frankly speaking this is sometimes deceiving because of constant changes in Power BI with monthly updates nullifying previous statements. Like if a feature was previously available in Power BI service only is now available in Desktop version as well so if you are not following recent updates, you may select a wrong answer. I must say that all brain dumps are not accurate. I have noticed many incorrect answers which may contribute in your failure. Relying on brain dumps without having hands on experience and knowledge can be disastrous. Most importantly relying on brain dumps cannot help you in future when you want to prove your certification at workplace. You must have practical experience and knowledge and skills to prove your worth.

Important Link

Following post of MSSQLTIPS by Daniel Calbimonte provides wealth of knowledge of topics covered in the exams. However, this is a very old article but you will be able to find relevant links from the same. This is well compiled and helped me a lot in covering topics.

https://www.mssqltips.com/sqlservertip/5050/exam-material-for-microsoft-70778-analyzing-and-visualizing-data-with-microsoft-power-bi-beta/

Important Tips

As you know that apart from drag and drop, most of the answers are in the form of multiple choice questions. In order to select correct answer you must read all options. However, in some cases you realize very early that the answer is incorrect so skip to next option immediately. Secondly, some options are absolute opposite of what is required. Like if an answer is expecting approach which should not create a new table in the model then you can easily ignore such options suggesting the same. This will save you lot of time and will help you concentrate on more difficult questions.

Secondly, if you are finding it difficult to get answer, then skip this question or mark it for review so you can come back. For scenario based questions, you cannot come back and review so such questions should be solved with utmost attention without spending too much time.

My Successful Attempt

It took me almost two months after my first attempt to appear again for the exam. I knew about my weak areas so tried my best to cover all those. I worked on sample files and polished my DAX to a large extent. I managed to score 845 out of 1000 in my second attempt. However, I must say that I need a lot more knowledge to cover administration and configuration part as my focus was mostly on practical implementation.

I hope my experience will help other test takers make a successful attempt to get certified.

Best of Luck!

 

Fuzzy Matching / Merge – Power BI

A new video is recently added by EZPowerBI.com to explain Fuzzy Matching / Merge feature of Power BI. This is understood that this feature is presently in preview stage but it is fairly important as there are lot many use cases where you have to merge data where you can expect variations in connecting table. This can be resolved through fuzzy merge logic implemented in Power BI. You cannot say that the accuracy is perfect but it will save you a lot from extra efforts needed to clean your data in such scenarios. This video is highly recommended for those who want to get hold of this feature at this stage. Subscribe to my YouTube Channel as well as this site to keep watching up to date contents.