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

As a Power BI developer, whenever you connect a dataset, you often see sigma in front of different fields in your table. What is the significance of this sigma and how will it hamper the performance of your dashboard? We will answer all those questions in this article. Let's dig deeper into all the fields with some symbols such as sigma called Implicit measures. Such measures are automatically created by Power BI which is a default setting so whenever you drag such a measure into a table or any visual it will provide a sum (if the sigma is in front of that field). Let's try it with a sample dataset. I am using the SampleSuperstore dataset. We will be checking the profit associated with different categories. I am taking both category and profit in the table. In the below-mentioned image, you can see we are getting profit with every category. Easy peasy!!!! in such a case default does the job but what if we don't want a sum. To obtain that go to the columns and select the dro

Variables!!!! doesn't it sounds familiar? It is an integral part of almost every computer programming tool. Also, it plays a significant role in DAX. Let's start from square one if I introduce Var in the DAX statement that means introducing a variable. Generally, you can use variables with different combinations in DAX but there is a mandatory syntax to pass at the end of it i.e. Return where you define what value that DAX should return if the defined condition matches and the alternate result to it. So let's clear out the ambiguity I will show how the variable works by using them in DAX under different conditions. I am considering Sample Superstore data to test the VAR. The use case is getting the West region Sales. For that, we are defining the Total Sales or Overall Sales by using the Sum of Sales and we will showcase all different regions and their respective sales. Now, I am looking for West Region Sales. You can achieve the same by using the Calculate and filters ( Ho

We have several types of ALL available in the DAX and it is quite difficult to write a DAX using any of them if you aren't aware of each of them and the purpose they serve in different situations. In today's article, we will mainly focus on ALL, ALLSelected, and AllExcept. I will highlight the purpose of each of them and the difference between them. Let's get started with the basic DAX which every BI Analyst or DAX expert is familiar with. Before diving into the DAX let's define the use case. We are currently using the Sample Superstore data. The idea is to look for corporate sales in the east region. If you want to know how to calculate Corporate Sales using filters and keep filters you can refer to one of our old blogs ( link ) Corporate Sales is another measure that we defined using simple DAX. If we are using only ALL( ) that means we are avoiding every type of filter whether it is coming from the query or it is coming from the slicers applied for that filter. You c

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