DAX Table Functions
In this blog, we’ll continue looking at more DAX ‘bear necessities’ with a focus on the DAX table functions.
Today we are looking at the most used table functions in a two-part blog series. So be sure to check out both of these blog posts to get a grip on these table functions. In this first part, we’ll be covering the ALL, FILTER and a mix of the ALL and FILTER functions. In part two we look at the VALUES, DISTINCT, RELATED, the RELATEDTABLE functions. We will also show how these functions can be combined together in mixed examples.
If you prefer to watch the YouTube video, rather than reading the blog, please see the video below:
DAX ALL Function
Let’s look at the ALL function, one of our featured DAX table functions.
ALL( {<table> | <column>[, <column>[, <column>[,…]]]} )
The syntax for ALL returns all the rows in a table or all the values in a column ignoring any filters that might have been applied. ALL is a remove all filters function. It returns all the rows of the table or a column and ignores all existing filters, meaning it removes the filter context completely irrespective of your slices or filters. The result of this is a table that can be iterated by a function or other aggregations. The ALL function needs a table or column as an input and as mentioned it can also work with a single column.
It is worthwhile to look at the difference for ALL with a table as an input ALL(Table) and ALL with a column as an input ALL(Column):
- ALL(Table)
- When having a table as an input, all the filters from the specific table will be removed, thus ALL(Table) will remove any filters that might have been applied in the table.
- ALL(Column)
- Important to note the difference here, ALL(Column) will only remove the filters from the specific column. Filters that have been applied to other columns in the table will not be removed and will still have an effect on the column.
Let’s look at some examples now to see first-hand how this concept of Table functions works. Let’s go to the Modelling tab and select a new table.
For this first example let’s create a table and we call it ‘Sales ALL’ and call the ALL function and immediately you’ll notice it’s looking for a table or column name.
Let’s ask for the SalesFact table.
Sales ALL = ALL(SalesFact)
In the data view tab, we see the ‘Sales ALL’ table. We will see that it has returned the entire ‘SalesFact’ table, thus if you place this ALL function inside of a measure irrespective of the filters that you have it will return the entire ‘SalesFact’ table.
Let’s create another table. This time we are going to call it ‘Sales (Customer)’ and we want to only return the column for the customer names. To create this table, we’re going to use the ALL function and choose the ‘CUSTOMERNAME’ column from the ‘SalesFact’ table.
Sales (Customer) = ALL(SalesFact[CUSTOMERNAME])
Immediately we will see it has returned a unique list of customer names from the ‘SalesFact table’. It is very important to understand the difference between calling a table or a column with the ALL function.
DAX SUM
Next, for our DAX table functions, we will be looking at the SUM function, let’s go back to our report to create a few more measures. Let’s say we want to determine the total sales for each customer. We will need to create a measure, that we call ‘Sales’ and this will sum the ‘Total Sales’ from the ‘SalesFact’ table.
Sales = SUM(SalesFact[Total Sales])
Let’s see the result when we display the ‘Sales’ measure against the ‘CUSTOMERNAME’.
Great, we now have ‘Sales’ by ‘CUSTOMERNAME’ as we’d expect and a total at the bottom.
DAX SUMX and ALL
The next measure we want to create is with the SUMX function, but this time we’re going to use the ALL function within SUMX. Again, we want to determine the total sales for each customer using the functions SUMX and ALL. We are going to call it ‘Sales ALL SUMX’.
Firstly, we start with the SUMX function and add the ALL function to SUMX, as we want to get ALL from the ‘SalesFact’ table and iterate through the ‘SalesFact’ quantity [QTYNET] and multiply it by the SalesFact unit price [Unit Price].
Sales ALL SUMX =
SUMX(
ALL(SalesFact),
SalesFact[QTYNET] * SalesFact[Unit Price]
)
Great, let’s add the new measure to our canvas.
We see what ‘Sales ALL SUMX’ has done, it has given us the total of all the sales for every single row in our table. This means that for every customer it has returned all the Sales.
To summarize the difference between ‘Sales’ and ‘Sales ALL SUMX’ is that for ‘Sales’ you would see that each of these customer names is, in essence, a filter on the ‘Sales’ table which gives us the corresponding sales for that specific customer. ‘Sales ALL SUMX’ on the other hand returns the sales for all customers because we are using the ALL function and this ignores the fact that the Sales belong to a specific customer and returns the Sales for all customers.
You may wonder what the usefulness of this example is and what exactly is this going to help you with. Let’s make this a bit clearer with a practical example, we want to determine the % contribution of each customer to the total sales value. To show this we create another measure that we will call ‘Sales %’. In our function tab I want the ‘Sales Measure’ we calculated earlier, and I want to divide that by this sales measure ‘Sales ALL SUMX’ we’ve just created.
Sales % =
‘Sales Measures'[Sales] / ‘Sales Measures'[Sales ALL SUMX]
Let’s add it to our canvas.
What we have is a % of the sales for each customer, but the only way we could have gotten to this answer is if we had a value to divide the ‘Sales’ by. In this case, we divided ‘Sales’ by ‘Sales ALL SUMX’.
DAX FILTER
Moving on let’s have a look at the FILTER function. The FILTER function requires a table as input and returns a table that has been filtered, thus it looks for a table and then it wants a filter expression.
FILTER(<table>,<filter>)
So, FILTER adds a new condition, it restricts the number of rows in the table. It filters the table and the FILTER function then returns a table as it is a table function, very useful. Let’s give this as an example for better explanation. We create a new table and call it ‘FILTER Table’. When I start writing the FILTER function, we can see it ask for a table name, let’s use the ‘SalesFact’ table.
We are going to filter the ‘SalesFact’ table by the ‘CUSTOMERNAME’ column, where the ‘CUSTOMERNAME’ column is equal to the ‘The courtyard’.
FILTER Table =
FILTER(SalesFact,
SalesFact[CUSTOMERNAME] = “the Courtyard”)
In our view tab, we click on the table that we just created ‘FILTER Table’ and see that this has created a table where the ‘CUSTOMERNAME’ is equal to ‘The courtyard’ only.
Some good practice when working with FILTER functions in a mixed measure is to verify if the FILTER function returns the table as you’d expect after the function is applied view.
ALL and FILTER
Let’s combine ALL and FILTER together and to see how powerful this combination can become. Let’s start off with just a quick overview of mixing the ALL and FILTER table functions. Each function requires a table as input and each function will return a table
Let’s say we want to create a table where we calculate the total sales for the customer ‘The courtyard’. To see how this works we create a new measure called ‘Sales (Mixed Table Function)’.
Let’s start with the SUMX function and apply a FILTER function to SUMX. Within the FILTER function we call ALL and here we want to call ALL from the ‘SalesFact’ table and for the filter expression we specify the column name ‘CUSTOMERNAME’ as equal to ‘The courtyard’. Finally, we want to calculate the total sales in the SUMX expression, to do that we multiply the quantity ‘[QTYNET]’ and by the unit price ‘[Unit Price]’.
Sales (Mixed Table Function) =
SUMX(
FILTER(
ALL(SalesFact),
SalesFact[CUSTOMERNAME] = “the Courtyard”
),
SalesFact[QTYNET] * SalesFact[Unit Price]
)
Great so let’s now create a new table to visually display the above example. From the SalesFact table we insert the ‘CUSTOMERNAME, let’s add the ‘Sales’ measure as well as the measure ‘Sales (Mixed Table Function)’.
We see what happened here, the ‘Sales’ table gives us a list of all the Sales by ‘CUSTOMERNAME’ and the ‘Sales (Mixed Table Function)’, where the answer for all customer names is the same to that of ‘The courtyard’. This is because we filtered by ‘The courtyard’.
Let’s go through this mixed function step by step so that you can see how it works to clear any confusion if there might be any.
- We called SUMX which is our iterator as well as our aggregation
Sales (Mixed Table Function) =
SUMX(
FILTER(
ALL(SalesFact),
SalesFact[CUSTOMERNAME] = “the Courtyard”
),
SalesFact[QTYNET] * SalesFact[Unit Price]
)
- Then we applied a FILTER and we’ve asked to return the entire ‘SalesFact’ table through the ALL function, but with a ‘CUSTOMERNAME’ equals ‘The courtyard’
Sales (Mixed Table Function) =
SUMX(
FILTER(
ALL(SalesFact),
SalesFact[CUSTOMERNAME] = “the Courtyard”
),
SalesFact[QTYNET] * SalesFact[Unit Price]
)
- Finally, once the function has filtered by the ‘The courtyard’ it iterates row by row and calculates ‘QTYNET’ times ‘Unit Price’ to calculate total sales, and, ultimately adds it all up together
Sales (Mixed Table Function) =
SUMX(
FILTER(
ALL(SalesFact),
SalesFact[CUSTOMERNAME] = “the Courtyard”
),
SalesFact[QTYNET] * SalesFact[Unit Price]
)
This should be clear now on why the answer to the ‘Sales (Mixed Table Function)’ is the same as that of ‘The Courtyard’. The application for this is really very useful and powerful where you combine numerous table functions altogether in one measure. You can do calculations with these table functions that you wouldn’t be able to do normally.
For more great posts visit our page. To learn more, visit our training page.
That’s it for part 1 of our table functions blog please be sure to check out part two where we cover VALUES, DISTINCT, RELATED and the RELATEDTABLE functions as well as some mixed examples.
Leave A Comment