Home Tags Posts tagged with "Quick Tip"

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

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

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