Skip to main content

Posts

Highlighting Top 10 with RankX and TopN

Happy Friday!! If you have worked with other BI tools such as Tableau setting up the Top 10 is relatively easy but in Power BI you have dedicated functions such as RankX and Top N to do this. In this blog we will see how to highlight and filter out the TopN Sub Categories based on the Sales recorded in 2013. Before we start. Here are a few pre-requisites we are using Sample Superstore data and we have created an explicit measures called "Total Sales". If you aren't familiar with implicit and explicit measures do read this article . Total Sales is equivalent to Sum of Sales.  We will start with RankX. The goal here is to highlight the Top 10 subcategories based on the Total Sales occurred in the year 2013. Refer to the image below. How to make this? We will start creating a basic DAX which will provide rank to different sub categories. Make sure you provide an order in the DAX itself. In this case we have provided "DESC- descending". Once you have the DAX ready p
Recent posts

Dealing with blanks in Power BI

Blanks in the dashboards is one of the pain and impacts the user experience. It can occur due to multiple reasons - due to data unavailability which is something is out of control of Power BI developer. One of the best practices suggest to avoid blanks with different approaches in Power BI. In this blog, we will showcase the most common approaches to tackle the blanks. This blog is mainly divided in two parts - dealing with blanks in the KPI cards and dealing with blanks in table or matrix.  Let's start from the KPI cards with the updates dealing with blanks in the cards became very easy. In the new cards it is now possible to provide a separate value when a blank appears in the card. This feature is available under the formatting option of new KPI cards. Do keep in mind this feature is currently available for the new cards.  In the above example we are showcasing the total sales and total sales last year in the reference. Under the highlighted section we can provide the value that

Optimising the Measures with DAX Studio

Another week another blog!! This week we will be focusing on the optimising the bad performing measures and how to tackle the measures generating Call back ID. Before starting, this blog is intended for Users who are a bit familiar with DAX Studio if not then you can do a quick read on our most read blog on our website ( link ). Performance of report is a great factor in improving in the user experience and to check how well your visuals and DAX formulas are performing you can utilise the benefits of Performance Analyser. In my experience Performance analyser along with the DAX Studio is a match made in heaven. You can copy the query from Performance analyser and evaluate in DAX Studio. To know more about how performance analyser works do visit this interesting article ( link ). Let's get our hands dirty and see how you can evaluate your DAX formulas. To do so I have created a basic DAX with help of Filter function  Now, we have included this measure in a table along with the year

Dealing with High Cardinality columns

Are you still struggling with slow reports and a complex data model and you have tried every approach to optimise it? One of the major reason behind your slow reports are high cardinality fields. What is this cardinality now? The simplest definition for it would be the unique values present in a particular column.  Do note we are talking about the unique values not distinct values there is a slight difference between these two. Want to learn more about cardinality here is a great article from Arno. If you have a background in data analysis then the term cardinality may not be alien for you. When we talk about optimising the data model then it is always advised to reduce the cardinality as more the unique values available in a particular column  more it will take the memory and impacts the performance. In this article, we will focus on a few easy steps to reduce the cardinality of the data model. The very fundamental and the simplest step will be to only include the fields that are nec

PBIX vs PBIT

As a Power BI user whenever you create a dashboard it saves under a particular extension. Let's imagine if you just started with Power BI then your first priority will be to make dashboards uniform looking. Also, let's suppose if you build a perfect looking dashboard and one of the client requested to make a separate version of it with all the available measures from existing dashboard. How to do it? This article will address all such questions.  Note- This article is only intended for users who just started their Power BI Journey In all such cases your go to option will be PBIT file. Is it same as the PBIX file? When you open PBIT file it looks exactly a replica of the original PBIX but you should focus on the file size. PBIX is comparatively bigger in size than PBIT. Let's understand why this difference exist - When we consider PBIT files it contains all the visuals, data model schema (only the schema), all measures and all the query definitions. One thing or you can say

Ultimate Beginners Guide to DAX Studio

There are zillions of external tools available with Power BI but DAX Studio is one of the most commonly used tools to work with DAX queries. It is a perfect tool to optimize the DAX and the data model. In this blog let's shed some light on the basic functionalities that can take your report to the next level. ARE YOU READY?  To start you will need the latest version of the DAX Studio. You can download it from their website . Don't worry you don't have to pay for the license. Fortunately, DAX Studio is a free tool As a BI Developer, I am using DAX Studio regularly. Based on my experience I use it for several purposes but in this blog, I will highlight the most common ones. Extracting a dump of all the measures used in your PBIX. Why do we need to do this? It can be used for documentation purposes also sometimes we try to reuse the DAX and such a dump comes in handy in this scenario. How to achieve it? Open the DAX Studio it is located under the external tools once you open t

Use Relationship in DAX

Data modeling is an essential part of creating perfect visuals. While creating complex data models there can be a case where you can find an inactive relationship represented by dotted lines and it occurs because you already have an active relationship between the two tables. But as a developer, you need to use both the relationship. How can it be done? You can use "Use Relationship" in such cases. Use relationship can be added to your DAX and act as a modifier or enhancer for calculation. It activates the inactive relation. But make sure you have an inactive relationship in place before using the use relationship function. Let's see how it works on Sample Superstore data. In my fact table I have two dates- Order date and Ship date. I am making the two relations between my date table and fact table. The relation between the sample superstore (date) to date table (date) is active while the relation between the sample superstore (ship date) to date table (date) is inactive

New Card Visual in Power BI

A new card with the recent update Power BI has released this visual. This is one of the most awaited visuals by users. Let's take a glimpse of what this new card looks like. Doesn't it look good? In old cards, it is always been a struggle to add an image and sparkline. With the new cards, you can do it just with a click. And the best part is it acts as a single visual so loading time is improved. There are cases where we want to visualize multiple cards and it should be part of a single text box or card. Let's check how to do it? The first step is to create the base measures- Furniture Sales, Office Supplies, and Technology Sales. How to write the DAX using filters? If you aren't familiar with writing DAX do visit our blog . Put all the measures in 3 different cards and fix the alignment. Now we will be introducing a text box with the background if needed.  Alignment needs to be fixed. Once your alignment is fixed select the text box go to format under send backward sel

Do we really need Keepfilters in DAX?

Writing a DAX can be tricky at times and making it efficient for the system is always a challenge for a BI Developer. The most prominent example is the use of filters in DAX. There are zillion ways to filter something in DAX and attain the same result but when you look behind the scenes you will get to know what effect it makes on performance. Looking at the above image it all looks the same. In this blog, we will cover the most common scenarios to use filters including the use of filter with all, filter with values, and keepfilters. I am using the sample superstore data for demonstration. The idea is to get sales for "Tables". The DAX is as follows. The most common and basic approach will be this. Looking at the matrix it gives the sales amount of tables as the total sales for "Furniture". If we use this in a table or matrix results can be perplexing for the user. Considering the total it does make sense. But we want every sub-category to get their respective sales