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...

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 ...