Analyst in Action

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

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

As a tableau enthusiast, I always see similar questions on the tableau community and in today's blog, we will address that. If you are new to tableau then you must be perplexed about the use of groups and sets in tableau. It troubled me a lot in the beginning and after using tableau for a significant period I realized that it's not that difficult and you can identify the purpose after using it. Let's dwell around it and highlight the differences.  Before highlighting the differences we will understand what is a group in tableau? As the name suggested you can group different members into a particular group without any condition associated with it and it will create a new dimension every time. The definition can be confusing at times so let's dig deep into it by taking a closer look at how a group works in the Tableau public. I am using sample superstore data. Before creating a group let's understand the idea behind it. I am taking the subcategory and we are looking t

What if I told you there's no need to write laborious DAX expressions in Power BI? Doesn't that sounds great? I personally enjoy writing the DAX expressions from scratch because it helps to get to know the data well and you can tailor your calculations. But if you don't like writing the DAX expressions from square 1 then you should consider "Quick Measures". Power BI always makes your task easy in visualizing your data and quick measures are something that helps you to create measures in few seconds. Doesn't that sound amazing? Yeah!! As the name suggests creating measures just by dragging and dropping is an accentuating feature of quick measures. I consider it to be the future of measures in Power BI. In this blog, I will showcase step by step process to create quick measures. I will be working on the Sample Superstore data. You can find the quick measures right next to the new measure on the top or else you can right-click any of the available table options

Are you spending a big chunk of your time cleaning the data in excel? If yes then this blog is for you. All the cleaning and transforming of data can be achieved in Microsoft Power BI. Power BI offers a wide variety of options to clean your data. Power BI comes with a power query editor where you can play around with the data and I consider it as an ETL tool where you can transform your data according to the needs. We have discussed the data cleaning process in the last blog and in today's blog the focus will be on transforming data and the features that the power query editor offers. These steps are solely subjective according to the data type and to your needs. I am highlighting 6 steps to transforming the data. I am working on the sample superstore data. So let's get started!!!! Removing columns and rows-  We are aware that the datasets can have a lot of unwanted columns and rows. To get better insights you need to get rid of it. To do so you need to select the column. If y