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

Identify and Delete Unused Columns & Measures

Heavy dashboards and a bad data model is a nightmare for every BI Developer. Heavy dashboards can be slow due to multiple reasons. It is always advised to stick with best practices. Are you still figuring out about those best practices then you should definitely have a quick read on Best Practice Analyser ( link ). One of the most common issues with slow dashboards is unused columns and unused measures. It is very normal to load some extra columns and create some test measures in your dashboard but as a part of cleanup process those unused columns and unused measures should be removed. Why we are removing them? Because if you keep them then ultimately it will increase the size of your data model which is not a good practice. How to identify the culprits (unused columns and unused measures)? In today's blog we will provide you with 2 most common external tools which will help you in identifying the culprits. More external tools😒. Who's going to pay for this? To your surprise...

Best Practice Analyser (BPA) Guide

Do you want to save tons of efforts to check if your data model and PBIX file follows the standard best practices and norms? Then this blog is for you. If you are a follower of our channel we already deep dive into the importance of the DAX Studio as an external tool. If you are a beginner I would highly recommend to visit this blog . In today's blog we will check how Tabular Editor can help to optimize the data model. Best Practice Analyser allows to define or import best practices. It will make sure that we do not violate the best practices while developing a dashboard. Isn't it exciting!! Before we start make sure you already have Tabular Editor version 2.24.1 installed on your system. To install it do visit this link and select the link for windows installer. Once Tabular Editor is installed it will reflect in your PBIX file under external tool. Also, we need to define the standard rules. To do so in your advanced scripting or C# script copy this and save it via Ctrl+S. An...

Copying Bookmarks from one Power BI report to another

Let's think of a scenario, where you want to copy the bookmarks from one report to another. Most obvious approach is to just do a copy paste of the bookmarks. What's wrong with this approach? This approach only works for all visuals but not for bookmarks and field parameters. If you are not familiar with basics of bookmarks and field parameters do refer to the beginners guide for bookmarks and introduction to field parameters . Then how do you copy the bookmarks? Power BI enhanced report format (PBIR) for Power BI Project files (PBIP) will help you in achieving this. Let's check it out, I have 2 reports one contains the bookmark called Bookmarks PBIR Test (origin) and other one is Rolling Average PBIR Test (destination) . Before we get started, you have to enable Power BI Project save option under preview features. Once enabled, restart Power BI desktop. There is a TMDL icon appearing on the left pane. What is TMDL and what's in it for me? There's a lot of possi...

Analyst in Action

Search This Blog