All, AllSelected and AllExcept in DAX

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 can also achieve the same result as mentioned above by using filters just by selecting the east region and corporate segment in the filters for this table.

Pro Tip- You can use All with and without the argument that means if I am providing any condition such as filter along with it then it will work as an expression to filter out otherwise if I am using it with the CalculateTable it will remove all sorts of filters and then we will get all values in the table.

Now let's see ALLSelected. As we are aware All won't include any filters but ALLSelected will include filters that are applied via slicers (on the visuals) and exclude the filters that are applied in the query itself.

We will take the same example as above and we will calculate sales with ALLSelected. The result will be the same. As mentioned in the below image we only passed one argument/condition in the formula but you can pass multiple arguments.

You can easily replace the ALL by using the RemoveFilters but you can't do the same with AllSelected. It is quite difficult to summarize AllSelected as it can be very complex in different situations. This is just an overview of what it does we will try to come up with a dedicated article on AllSelected in the future. I would say AllSelected is much better than All but make sure you are using it in the right situation.

At last, we will take a look at ALLExcept which is also most commonly used in DAX. I am considering the same situation in the matrix.

In the above image you can see the same value for every subcategory in the highlighted column it is because AllExcept removes the filter from all the columns in the orders table except the region and hence we are getting the same value. Here is the formula that I used.

Thanks for Reading Let's connect on LinkedIn. For more such blogs and pro tips do follow us.

Comments

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

Append v/s Merge in Power BI

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

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

Analyst in Action

Search This Blog