Analyst in Action

Let's consider a situation where you have one big fact table and you need to create several tables out of it using different columns. The most obvious way is to do it in the backend but it can be time-consuming. What if we can do the same in DAX itself without going back to the source. The solution is quite simple and DAX calls it "Summarize".  It's quite evident what this function does from the name. You guessed it right!!!! It gives a summary of the fact table based on your selection of fields. To get a better grasp of this function we will take a couple of use cases. We are considering Sample Superstore data and the only fact table is "SampleSuperstore". I want to explore only the geographical data and associated quantity and profit with every city.  First, we need to create a new table {you can do so by going to Modelling and there in calculations, you can find a new table}. Once you select a new table you have to provide a DAX formula before doing so re...

Let's consider a situation- you have 4 fields and you want to depict the flow with a help of visuals. The first thought can be to use a table or matrix in Power BI. It can depict the data but how about the intuitiveness? The solution for such scenarios is Sankey Charts which can help you to show different fields and the flow associated with them. As a beginner in the BI world anyone can question- Is there a need for a Sankey chart when you already have a pool of options? Yes, we do need such charts if you are trying to visualize a process with respect to a particular field. Protip - Sankey Charts are not available in your default library. You need to get it from the online library and keep in mind to only add certified visuals. So, enough of the introduction let's showcase how the Sankey chart works. I am considering the SampleSuperstore dataset. The use case for the first visual is we are going to show the flow between Region and Category. The flow size will be governed by the...

Do you struggle to spot the difference between different types of filters that are available in Power BI? It can turn out to be vague for beginners in Power BI. In today's article, we will refer to these filters and explain the purpose of each one of them. If you are a novice to Power BI then you must refer to our previous blog which covers an overview of different filters that exist in Power BI. ( Different Filters in Power BI ). As most of you are aware there are countless methods to attain the same result using DAX but the key thing to keep in mind is the efficiency of the DAX formula in that particular situation. For this article I am using Sample Superstore data (you can download it from kaggle.com). I will demonstrate the purpose of every formula. Let's get started!!! so the basic idea is to calculate sales for every sub-category and by using the filter formula I need to see sales in the corporate segment.  The above-mentioned formula is the basic approach where you just ...

In today's blog, we will take a deep dive into the basic functionality of Power BI. We will talk about the types of filters available in the Power BI whether in the query editor or at the visualization level. Let's start with the purpose of using filters that is limiting the rows on the basis of a condition or restrict the data which you don't want to showcase in your visualization. There are many types of filters available in Power BI. We will start with the filters available in the query editor. I am considering the sample superstore data. So, I have imported the Orders and Returns table and we will be applying the filter on the order date. When we select the date filters it will lead to a pop-up where you can select basic and advanced filtering currently I am selecting the advanced version. The filter will limit the data for a particular duration as you can see in the image below. You can remove the filter just by selecting the clear filter. Just to check the code of the...

This blog may seem to be very elementary but I firmly believe it is the foundation of DAX in Power BI. Our nucleus for today is Calculated columns and measures in Power BI. If you are a Power BI user you must have come across these terms. You can find both of them in the home tab (placed next to each other). We will highlight the difference between both of them and the limitations associated with them. Let's get started!! You can easily increase the table size by adding calculated columns to it and you can provide a DAX or logic for that column. The most pivotal thing we need to be aware of such columns is that they are calculated at the row level. There are situations when you can't create a relationship between tables in such cases calculated columns can come to the rescue. But be aware that the calculated column occupies a space in the memory which can be good or bad in different scenarios. If the DAX for your calculated column is complex then it can provide you a much bette...

You must be aware of the purpose and significance of Vlookup in Excel. But when it comes to Microsoft Power BI there is no Vlookup in it. Power BI provides you Related and Lookupvalue which is quite similar to Vlookup in Excel. If you aren't familiar with the Vlookup kindly refer to our blog . Let's get started with the purpose of both functions. You will be shocked to know that both of them will give you the same result. Because it follows the same principle of Vlookup i.e. searching for a particular value in a column and returns a value from a different column (different table). In this blog, we will showcase how and when to use related and lookup values. We will be using Sample Superstore data. The question that comes to my mind is when to use the related functions? So there are certain criteria to be met before creating a column with related. One of the conditions is that both the tables (one where we are creating a column and the other will be from where the value will com...

Let's discuss another problem of the week. As a Power BI user, there are times when you want to combine queries. What are the ways to do so? In most cases, you can attain it by using either append or merge and both serve different purposes. Let's understand what do these terms mean in Power BI and how they are functionally different from each other.  It is quite common to get data from various sources and you need to combine those data depending on a particular column which is common in both tables so that you can add extra information or column to your big table. In such cases, we use merge queries. How to perform merge queries? For instance, I am considering Sample Superstore data and we will merge the returns table to the order table. You will find both merge and append in the home tab in extreme right in the power query editor. ProTip - You will find two options when you click on the drop-down in merge which are merge queries and merge queries as new. When you use merge que...

In today's blog, we will deep dive into the Power query editor. The interface of the power query editor is a replica of an excel spreadsheet but in the former things are much easier as compared to excel. Power query editor offers a lot of features which we have discussed in the guide to power query editor blog .  In this blog, we will focus on one of the features i.e. add columns. You can find it next to the transform tab in the power query editor. We will be working with the sample superstore data. The idea is to explore the add column tab. Let's start with duplicating the column which is just a click away feature available in the power query editor. You just need to select the column of which you need to make a duplicate and select the duplicate column tab available on the top. Unlike in Excel, you need to apply shortcuts to achieve the same. The power query editor is all about making your life easier. From duplicating the columns we will move to index the columns which can b...

Are you looking forward to dealing with large datasets in excel? If that's the case then you must consider pivot tables. If you are a novice in this data world then you must focus on the pivot tables as they will be a stepping stone for you as a data analyst. So what is a pivot table? The pivot table allows you to understand the data where you can generate certain tables and graphs. I consider it as a baseline for business intelligence tools. If you look at the features of the pivot table it is quite similar to BI tools with limited options. It is an integral part of many analyses in excel and at our blogs, we have worked in-depth with pivot tables in RFM segmentation . In today's blog, the focus will be on exploring features that a pivot table can offer for a data analyst. Let's dwell around with it!!! I will be using the Sample Superstore data for the analysis. As you must be aware of inserting a pivot table. You need to select the dataset completely and in the insert tab...