Professional Tooltips in Microsoft Power BI

Professional tooltips 2

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

Introduction to VCAD a custom visual for Microsoft Power BI

Recently I had a change to explore VCAD a custom visual by Blogic. I have seen their videos on LinkedIn and got inspired because of unlimited possibilities of clubbing BIM data available with the IFC file used for the selected model.

After doing my homework I did a sample and shared the same on LinkedIn which receives lot of appreciation. Based on my experience I am writing this short blog to get you started with Building Information Modelling and Industry Foundation Classes files to use along with relevant data to work on different use cases.

Before getting started with “VCAD – Custom Visual” it is better to get hold of some conceptual knowledge to be comfortable with the terminologies used.

What is BIM?

  • Building information modeling (BIM) is a process supported by various tools, technologies and contracts involving the generation and management of digital representations of physical and functional characteristics of places.
  • Building information models (BIMs) are files which can be extracted, exchanged or networked to support decision-making regarding a built asset.
  • Use of BIM goes beyond the planning and design phase of the project, extending throughout the building life cycle.

Source: https://en.wikipedia.org/wiki/Building_information_modeling

What is IFC?

  • The Industry Foundation Classes(IFC) data model is intended to describe architectural, building and construction industry data.
  • It is a platform neutral, open file format specification that is not controlled by a single vendor or group of vendors.
  • It is a commonly used collaboration format in Building information modeling (BIM) based projects.
  • The IFC model specification is open and available.

Source: https://en.wikipedia.org/wiki/Industry_Foundation_Classes

What is DWG?

  • DWG(from drawing) is a proprietary binary file format used for storing two- and three-dimensional design data and metadata.

What is VCAD?

  • Vcad is a software for the spatial management over dwg drawings, bim and asset management.
    Through specific modules Vcad allows to have total control over the structure as long as over any type of features related to it.

What is VCAD Custom Visual?

  • The Vcad Custom Visual allows you to visualize your IFC files in Power BI.

Source for Sample IFC Files

For my proof of concept shared with this blog, I have used sample IFC file from the following source. You may also select other files available but this is strongly recommended to start with the simple drawing.

Source: http://www.ifcwiki.org/index.php?title=KIT_IFC_Examples

Filename used in my example: FZK Haus

Prerequisites

Following are prerequisites for this example.

  • Power BI Desktop
  • Register at https://www.bimservices.it/
  • Select a Plan/Slot(s) Free (30-days Trial) or Paid
  • IFC Format File to use with VCAD Custom Visual

Steps to Incorporate VCAD Custom Visual

Assuming that you have already registered yourself at “BIMservices.it” website (https://www.bimservices.it/) you should be following steps to initiate your work.

  • Login to your BIM Services account by clicking following link
    https://www.bimservices.it/login/ (Ignore this step if already logged in)
  • Click on “VCAD” link on the page to open File Manager to upload your IFC format file. You can also use following link if you are already logged in. https://www.bimservices.it/vcad-file-manager/
  • Click on the “UPLOAD” button on the top right corner of the File Manager.
  • Click on the “CHOOSE FILE” button to open File Explorer screen of your Windows where you can select required IFC Format File already available with you.
  • After uploading of file, BIM Service File Manager will process your file for few seconds based on the size of your file. Once ready, you will find an icon on the left side of filename to preview the loaded file.
  • After verifying uploaded file through preview, you may download Power BI template file through download icon which is first from left among the list of icons available in the rightmost column.
  • Clicking on download icon will open a popup screen giving three options of templates to download. This is recommended to select the third one which is “Space Asset Theme” template. You may click on the download icon to start download.
  • Optionally you can also download PDF help file from the popup screen if needed. This can be downloaded through “PDF” icon on the left side of the template name.
  • Once template is downloaded you can move the same to your desired folder where you can open the template file in Microsoft Power BI Desktop installed on your PC.
  • After Opening the file you will able to see both Spaces and Assets related pages with pre-built visuals including VCAD.
  • You can save this file as PBIX in your desired folder or you can publish the same to your Power BI online account.

 

Using External Data

With the PBIX files saved you can view the data files imported having complete data of drawing used with VCAD. This includes following main tables along with other tables.

  • Assets
  • Spaces
  • Floors

You need to understand data model to incorporate external data as without proper data modelling experience one cannot built and integrated solution.

For this example I have used following table which can be made directly in Power BI or you can used Microsoft Excel to incorporate the same. For this example I have also added a custom column “FloorNameEnglish” with translated/customized names of the floors. This column was also created in Floors table which was later used to build relationship between Contractors’ table and Floors table.

FloorNameEnglish Contractor Work Assigned
First Floor Bravo Electric Electric Work
First Floor Ideal Woodwork Wood Work
First Floor Jimmy Technologies Communication
First Floor Pro Insulators Heat Insulation
First Floor Silk Builders Construction
First Floor Tulip Interiors Finishing Work
Ground Floor ABC Construction Construction
Ground Floor Blossom Interiors Finishing Work
Ground Floor Bravo Electric Electric Work
Ground Floor Ideal Woodwork Wood Work

Caution:

The report was tested on Google Chrome browser. I have tried it on Internet Explorer but it was not running properly. Regarding other browsers I cannot confirm if it works on browsers other than Chrome.

Note:

This is to be noted that since VCAD Custom visual is configured with your account, it is not possible to share source file due to account related settings involved. You may easily try using example available through the template file downloaded.

Report Snapshot

Following snapshot is shared to view the final report. Furthermore, live link to the report is also shared below.

Live Link to Sample Report

snapshot simple vcad

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!