Professional Tooltips in Microsoft Power BI

Recently I have conducted an online session titled “Professional Tooltips in Microsoft Power BI”. This session was well attended and lot of use cases were shared. To facilitate participants and other Power BI community members who wish to explore the same can download sample pbix file through the link shared below.

The video link is also shared with complete walk-through of the file. This is to be noted that there are lot more possibilities but due to shortage of time only few were shared to give better understanding to session participants who can later explore and enhance these examples.

You are recommended to subscribe my blog and YouTube Channel to stay abreast with the latest content shared on Microsoft Power BI.

Live Link to Report

Click here

Sample File Download Link

Click here

YouTube Video Link

Click here

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

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

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.

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.

 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.

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

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.

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.

You may download complete files including sample Excel file from the following link.

Download Sample File

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!

 

Data Spadework for Power BI Visualization

If you have decided to work on a report for your organization or customer, make sure that your spadework is up to the mark. This will help you in saving lot of hassle when making a report or visualization when making without proper spadework.
First of all you have to see what type of control you have on your data source.

1- Full Control
This must not be taken wrongly as this is related to access to data, here it means that how much you can influence the way and form you are getting data for your reports. If you are mandated to implement business intelligence in your company, then you have to set standards for your data. This may fall in data governance domain but to some extent you have to play a role to get data in meaningful format without unnecessary and inconsistent arrangements. This may be very cumbersome to check for the sanity of data after imported in Power BI especially when your data is in bad shape. This may adversely affect your facts and figures and may contribute in raising false flags due to incorrect data. If you have full control on your data then you can easily achieve maximum accuracy level in your reports.

2- Partial Control
When you don’t have much of a control on the way of getting data, you might have to face a lot of challenges in producing quality results. This may be true when organizations are not ready to change the way the data is gathered and produced. This also belongs to change management where organizations are reluctant to experiment new ways because of working culture and limitations in terms of workforce capabilities. In order to minimize effects of inconsistent data format, you need to list down possible variations to cater the same in your queries. Things may be very difficult in cases where consolidation of data is needed. The best way of getting good reports is to ensure that each data provider is ensuring consistent data entry. Proper standards can be enforced when referring similar data like department names and designations where consistent data entry can help in getting correct figures when data is consolidated from different sources.

3- No Control
In cases where you have simply no control on data sources, you have to set expectations correctly with the recipient of these reports. There are always chances of errors especially when data consolidation is required and data is constantly refreshed and updated. Such cases requires constant testing and maintenance if report is not a one off report. Whenever fresh data is imported, proper auditing is needed to check if any real data is ignored by your query rules. This is also true when your report needs data from external/third party sources. If your external source has changed the format of data or moved to a different URL, you will have to go back and fix your reports.