Home Quick Tip
Category:

Quick Tip

USING TREE MAP AS LEGEND ON A PAGE in Power BI

I recently worked on two projects where the client wanted to show multiple metrics sliced by the same categorical data. For example, seeing how various metrics are performing over different regions or different product groups. A use case like this can be achieved in many ways; probably the best approach is to use small multiples functionality or to keep it simple, five same visuals with different metrics.

Let’s look into it with energy consumption data. Here, I want to show metrics 1 to 5 on different income levels over the years.

Five different visuals

When you know exactly how many categories you have in the requirement and how you want to display your data, then going for a certain number of visuals is my favourite approach.

UntitledImage

Using Small multiples

When there is no clarity about the number of categories, the small multiples feature is significant. For this use case, I went for two columns. Due to five categories, I get an extra blank space. It doesn’t look that bad, but I would like more control over the placement of visuals on the canvas. 

SmallMUltiples

When I compare Five individual visuals to small multiples, small multiples may have better performance from the number of queries point of view, and it also looks a bit tidier with one legend, one axis.

To have the best of both, we can use TreeMap visual as a legend

Tree Map visual as a legend

For the tree map, I used the income level column as a category and distinct count of the same column as value. I have turned off data labels, header icons and tool tips. I ensured I used the same “Max Axis” value for all visuals, making the comparison seamless. 

This approach is cleaner than showing the same legend for each visual and serves the same purpose as the usual legend: a user can identify what is what based on colour or shape and interact by clicking on the legend. 
https://prathy.com/wp-content/uploads/2022/06/2022-06-25_14-49-20-1-3.gif

The benefits of this approach are that your report stands out, a nice big legend is easy to interact with on touch screen devices and looks sleek rather than repeating the same legend. I hope the blog inspires someone out there.

Until next time,

Prathy 🙂

0 comment
3 FacebookTwitterPinterestEmail

The inspiration behind this post solely came from WHO website https://www.who.int/. I went onto to check some COVID data and found quick links on the main page quite interesting, hence this post. Using Buttons in Power BI is straight forward, and we see many Power BI report designers using it more often. What I observed, not everyone uses the amazing extra functionalities of Power BI Buttons. In this post, I want to use properties of Buttons, Shapes and try to achieve the look and feel I saw on the WHO website. The look I am going for is below:

 

Let’s see how we can create it in Power BI:

  1. Create a button, follow this article to create a button in Power BI Desktop – https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-buttons
  2. Add Text to Button. For the sake of the blog post, I am just going for Button 1, Button 2, etc.
  3. Add line shape and put it behind the Button, Send it back!
  4. Add Fill to All buttons same as the background colour of the page and no transparency.
  5. Select all buttons, change the fill transparency to 100 % on hover. Now your buttons will look something like below.
  6. Now change the text of each Button on Hover with a prefix of Empty Characters. Yes, empty characters!

https://emptycharacter.com/ is your friend when it comes to empty characters. From here copy an empty character and paste infant of your text in the Button. In this example, I pasted Empty Characters before Button Text on Hover. I went for three empty characters before each Button Text. That’s it, and you will have a subtle animated effect on your buttons.

I am sure someone out there must be thinking, why don’t we add a space before the text, I am sorry but that doesn’t work. Don’t know why! I am sure, Power BI doing something cleaver to not to show empty spaces before the text. Hope someone finds this post inspiring.
PBIX – https://1drv.ms/u/s!Avm7gbgZtlMlyhlTp2zkQxArZsOG?e=Fcd1Lm

Until next time,

Prathy 🙂

6 comments
6 FacebookTwitterPinterestEmail

A quick post, how many times in Power BI Desktop, have you clicked on “No, remove the files.” and then say OOPS! Well, I did plenty of times to discover this trick.

In short, you can find those removed files under Temp folder like many other windows application files. Usually, the location will be somewhere like this – C:UsersprathyAppDataLocalMicrosoftPowerBI DesktopTempSaves. This location depends upon which version of Power BI Desktop you have. Beware, these files will be removed whenever you clear your Temp Directory.

In long let’s see what happens with Power BI Desktop Auto Recovery and where we can find PBI related files. By default Power BI Desktop saves Auto recovery files; you can find Auto Recovery settings under Power BI Desktop options.

When a user creates or opens a Power BI Model, Power BI desktop saves a copy under Auto Recovery and Temp Saves. When the user closes without saving, Power BI Desktop will save an Auto Recovered Power BI file under the Auto Recovery file location given under Power BI Options. When you click on “No, remove files”, It will remove files from the Auto Recovery folder. But, files under Temp Saves are still there.

Remember, every time you open a recovered file or open a file from Temp Saves, Power BI Desktop will refresh all queries again. I think it’s for security purpose, not great when your queries take a long time to reload all the data but it’s better than losing all the hard work.

That’s all, Temp Saves; saved my life many times. Hope it helps someone out there too.

Stay Safe!

Prathy

 

 

5 comments
8 FacebookTwitterPinterestEmail

A quick post today. I received an interesting question in a webinar I did recently for “Best of Power BI World Series”.  During the webinar, someone asked me “How to deal with a Table or a Matrix visual, when you have a dark background image, light font colour in Focus Mode?”.

So what exactly he/she meant?

As you see in the below video, when I go in focus mode, I can not see any values because my font colour and the background colour of Focus mode are white.

To overcome this, especially when you are using dark backgrounds, it is better to use the same colour as background as your visual background too, like below. That way when you go on focus mode, you will still see the font. I will be talking more about visual backgrounds in my next post, so stay tuned.

Whenever I work with font colours, I find ColorCombos website very useful. It has a Font Colour Test option, which comes very handy to choose right foreground and background colours.  –https://www.colorcombos.com/color-schemes/27/ColorCombo27.html

Keep smiling,

Prathy 🙂

0 comment
1 FacebookTwitterPinterestEmail

In my current project, one of the user requirement was to have a filter on the Year Slicer. To explain in detail, we have various measures to show metrics for current and earlier measures. For example 2016,2017 and 2018. In 2016, we always have blank values for Last Year metrics, having empty values don’t tell the story well. So to tell the story, we need to pull three years worth of data but display only two years in the Slicer. The easiest way to handle this situation would have had a visual level filter on the Year slicer.

Power BI Slicers doesn’t support Visual Slicers. However, with the help of Selection Pane and Sync slicers, I did a quick workaround. I created another slicer for Year attribute, let’s call it Slicer-0. Updated the Visual Interactions of the original slicer (let’s call it Slicer-1) to not to filter the Slicer-0. As I have Synced Slicers on all pages, so I synced Slicer-0 on all pages. And I made sure; I updated the Visual Interactions on all Pages. Then hidden the Slicer-0 on all pages

Things to remember:

A hidden slicer means hidden logic in the Model. Whenever I design Power BI Models, I try to keep up a page with Designer Notes by specifying things like this. Basically, things which are helpful during troubleshooting. So if you are using hidden slicers, make sure you note it somewhere.

Another thing to note is, under Filters and Slicers affecting this Visual will show extra slicer information in the Pop-Up. If you want you can disable the Filter Icon under Visual Headers. But I prefer having the Filter option enabled.

Prathy 🙂

1 comment
0 FacebookTwitterPinterestEmail

Have you ever ended up in a situation where someone already did all the work to get the right brand colours, created a Power BI theme file and left the company. Then you go there to do some quick reports; business wants that long time signed off  theme that they used in other reports, but no one knows where that theme file exists? I have!  I was asked to create Power BI files which looked similar to the ones they had; existing ones had themes and backgrounds, but no one knows where they were.

Where are my Power BI theme file and background images?

By now, many of us know Power BI file can be renamed to a .zip file to see the contents. I previously blogged here how to zip PBI file and how we can use this for Drill Through functionality. So, I was pretty sure; if I imported a Jason file as a theme into Power BI Desktop, it should be saved somewhere inside. I can unzip the  model and find all imported files; I was not wrong 😊

So the steps to see imported files are simple:

  1. Copy the existing Power BI file
  2. Change file type to .zip
  3. Unzip it
  4. In the unzipped file, go to \Report\StaticResources\RegisteredResources
  5. That’s all; that’s where your theme file, background images or any other images you used in the model resides

Hoe this helps!

Prathy 🙂

0 comment
2 FacebookTwitterPinterestEmail

In my recent talk for PASS BA Marathon, I showed few formatting tricks in Power BI with my super slow laptop. I promised to write a blog post but due to some other priorities, I didn’t manage to publish this blog post in time, well better late than never!

You can watch recorded Session here:

or just some highlights here:

Adding Gif images to Power BI:

People who knew me in person, I am sure they know I like pictures. However, so many pictures on a dashboard are undoubtedly not a good idea. Stephen Few mentioned, “Art has its place but not on the Dashboard”. Here, I am not at all recommending to put loads of GIF’s on the dashboard, but if you want to go crazy with your dashboard or want to get people attention, it’s not a bad idea. Well as Stephen Few mentioned in another article, It’s nice to highlight what needs attention.

Adding GIF images is quite easy. First I created a measure with value 0. I named this measure as BGGif, so I know I have to hide it from users, and it will be easy to identify in the model. It is not mandatory to create a measure, you can use an existing one, but creating a separate one makes it easy. Then I placed Line Chart( you can use other charts, but Line Chart makes it a bit easy). Then I went to format options of Line Chart, under Plot Area, for Add Image option I chose the GIF image. Based on the data colours, there is a chance of spotting a Lil dot; if you don’t want it, just move BGGif value to tool tips. I like the idea of putting Gif images; you can create some cool ones like below 🙂  I read or heard using Plot Area of charts for gif images, I don’t remember where exactly it was. Whoever it was, thank you 🙂

Dynamic Background:

There are several ways we can do this based on our use case. In my demo, I just wanted to show a background in green if the price <=100 if not Red.

First I created a measure with a bit of DAX. Again, I am going to prefix it with BG and make sure I hide it for business users.

PriceBG = if([Sum Of Price]<=100,"http://cdn30.us1.fansshare.com/image/wallpapergreen/pix-for-gt-plain-mint-green-wallpaper-mint-green-plain-background-wallpaper-wallpaper-mint-green-999507947.jpg" , "http://www.publicdomainpictures.net/pictures/200000/velka/plain-red-background.jpg")

In the measure, it is just a URL with Red and Green backgrounds. I changed the data category of measure to Image URL. Then I used Image custom visual to display PriceBG measure. One problem with this approach is if URL brakes, visuals will not work.

Using Unicode:

I first saw this technique when Will Thomson showed at UK Power BI Summit also recently Chris Webb blogged about using Unichar() function.  In my demo, I just copied Emoji’s from the Web and placed them in my measure, but if you want you can use UNICHAR() function as Chris explained.

To show #Beds – I created a measure like below:

#BedsEmoji = if((AirBnB[bedrooms])>=5,"?️ +",

IF(AirBnB[bedrooms]=1,"?️",

IF(AirBnB[bedrooms]=2,"?️ ?️",

IF(AirBnB[bedrooms]=3,"?️ ? ?️️",

IF(AirBnB[bedrooms]=4,"?️ ?️ ?️ ?️","0")))))

And for an overall review, I used Sentiment Analysis, based on sentiment analysis value; I created another measure:

ReviewEmoji = if(SUM([Score])<=.50,"?","?")

These emoji looks great on Power BI Service than PowerBI Desktop. I love the functionality of using strings in DAX measures, it enables us to use Unicode and advantages are unlimited. As I blogged in one of my previous blog posts, you can use these to highlight a specific data point in a chart. Or as Chris blogged, we can use Unicode in many ways.

Hope this blog post gives you some ideas, and again I am sorry for not publishing blog post earlier. You can download the PBI file here – https://1drv.ms/f/s!Avm7gbgZtlMlpx4qFeK1Qu15NcpE and view reports here – https://app.powerbi.com/view?r=eyJrIjoiNzUxYzRmNjUtNDA4Mi00MWI0LWJiNWQtMDRiZWQwZmFjYjE0IiwidCI6IjM4YmU3MjU5LTMyMGQtNDZkYy04YzNiLWNkZDA5OTM1NGExMyIsImMiOjh9

Keep Smiling,

Prathy ?

 

0 comment
1 FacebookTwitterPinterestEmail

For me, Stephen Few has always been the guru of Data visualisations. Recently, while I was reading one of his paper, I came across most talked Bullet Chart and Highlighting a particular value. Then the first thought that came to my mind was, Can I do it in Power BI?

Bullet Chart is not one of the built-in visual in Power BI Desktop, but it is available as a custom visual. In fact, we have two bullet charts available one by Microsoft, and one by SQL BI / Ok Viz and they both are excellent.

Highlight a Specific Data Point:

My idea was to create something similar to following report which was originally shared by Stephen Few at http://perceptualedge.com/articles/misc/WhyMostDashboardsFail.pdf .

As of now, with handy bullet charts and formatting options, highlighting is not something out of the box. However, by using a bit of DAX, I can do something similar.

For this example, I imported top grossing movies data; I just want to see which movie has the max difference when I compare between overseas and worldwide.  Whatever the observations I do on my dashboard, I want to analyse it with the difference measure. Hence that’s the key on my dashboard. So I would like to distinguish that value with others data items.

If you look at the following example, on the right, I can easily see which movie has the most difference. But on the left, having so many categories, it is a challenging task to identify the single category which has the max value.

So what have I done:

First I created a calculated column to calculate the difference

Difference = 'World Wide Gross'[Worldwide]-'World Wide Gross'[Overseas]

Another measure to calculate the MaxDifference of only data which has been selected

MaxDiff = CALCULATE(MAX([Difference]),ALLSELECTED('World Wide Gross'))

Then I created a flag, with a simple if statement to show a circle icon when the difference is equal to Max difference otherwise 0.

Flag = IF([MaxDiff]=sum([Difference]),"? ",0)
Followed by a new column to show the Circle icon, which has the max difference.
Title with Flag = IF([Flag]=0, [Title], CONCATENATE([Flag],[Title]))

I can hide all the measures and columns I don’t need and use the Title with Flag, where ever I want. With Lil bit of DAX, I created following reports.  Now I can easily find the Star Wars movie among many other movies. Thanks to Will Thomson, who showed me the trick of using Unicode characters in the measures.

Power BI Model Download
Till next time,

Prathy 🙂

4 comments
0 FacebookTwitterPinterestEmail
Find level of Directories / Find String in Power BI

Directories at different levels are critical for some organisations. One of the requirements I received recently was to import Flat Files from directories ( Hundreds of them ) at different levels. Apparently Power BI makes it easy to import files from a folder, however in my case, I needed to choose the files to import based on the folder level and many other conditions which are also related to the level of the folder and file names.  Folder level was a key, so let’s see how to find folder level:

When we choose Folder as a data source in Power BI, it imports some metadata along with file content. For my requirement, I used Folder Path column to find the level. In the Folder Path, if I count the number of backslashes (), that will give me the level of the folder.

Instantaneously I started using List.FindText. First I converted Folder Path column to a list, then I used List.FindText to find the position of “\”, then List.Count function to find no of items in the list. It works but notably slow. What I wanted was a Text function than List Functions.

Even though the description was a bit misleading in the documentation, I decided to look into Text.PositionOf. Like many Power Query functions, this function also has optional arguments like Occurrence and comparer. When we use Text.PositionOf with no occurrence, it returns a number. But if we want all occurrences of a substring, then it returns a list.

For my scenario, I just needed no of occurrences, so I used List.Count to find no of items in the List. And this approach was significantly faster than using List.FindText.

At the end I used List.FindText to find the level, in my real example, I used a lot more custom filters to fulfil my requirement. Here are the formulas I used:
Using List.FindText – List.Count(List.FindText(Text.ToList([Folder Path] as text) as list, “\” as text) as list) as number
Using Text.PositionOf – 
List.Count(Text.PositionOf([Folder Path],”\”, Occurrence.All) ) as number

Summary: Hope this helps, one thing I learned working with Power Query is “Most of the functions has more functionality than I presume, and it often hidden under optional commands “

Prathy ♀️

0 comment
0 FacebookTwitterPinterestEmail

Quick Power BI Tip:

Case: How to add extra / new columns to an existing Power BI file which has CSV as data source?

Solution:

When I was working with Power BI today, first I created a model with my CSV files, while I was doing the report; that is when I realised I need one more column from the data source. After I had received the new file, I thought I would just refresh the data source and power BI automatically refreshes my query. In fact, Power BI does automatically update my query but what I noticed was, it excluded the last column. When I looked in the advanced editor query looked like my example here

let
    Source = Csv.Document(File.Contents("\\Mac\Home\Desktop\Blog\2.csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source)
in
    #"Promoted Headers"

2016-08-04_06-08-57

I can see, it is strictly bringing two columns only. So the tip is to simply get all the columns into the existing Power BI file, just change the number of columns to the new number of columns in the CSV file, which is 3 in my case.

Updated Query:

2016-08-04_06-21-21

let
    Source = Csv.Document(File.Contents("\\Mac\Home\Desktop\Blog\3.csv"),[Delimiter=",", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source)
in
    #"Promoted Headers"

2016-08-04_06-21-49

That’s all for now 🙂

Prathy

2 comments
0 FacebookTwitterPinterestEmail