Skip to content
Carmel Eve By Carmel Eve Software Engineer I
Learning DAX and Power BI – Table Functions

So we've looked at the different evaluation contexts, and an overview of aggregator functions, next on the list is table functions!

Table functions are ones that return… A table!

These include:

  • FILTER
  • ALL
  • VALUES
  • DISTINCT
  • RELATED TABLE

A quick reminder of the current state of the dataset we are working with:

Showing view of data set.

We have a dataset of people who live in different cities, who have different numbers of children and we have created a calculated column to signify whether or not each person has children.

FILTER

The FILTER function does exactly what it says on the tin. You apply it to a table, pass it a condition, and it returns you a "calculated table", which is a filtered version of the table you provided.

For example:

Showing table created using filter function which shows only those with children.

This creates a table which just includes the people who have children. However, this adds another table into the model, which as we've seen means that the report will use more memory on disk.

Instead, if we wanted to do something with this table, we could instead just temporarily store it whilst doing the calculation we require. For example, if we wanted to calculate the average number of children, but ignore those who didn't have children at all, we could do:

Showing table stored as variable in calculating the average number of children.

This will give us an average of 2.33, whereas if we include those without children it brings the average down to 2.00.

We can see here that we have defined a variable inside the processing. This becomes incredibly valuable as we build up more complex processing to make the calculations a lot more readable. (We also must use the X-aggregator operators here because the simple aggregators expect a base table as an input.)

We can see that by using this technique we can perform aggregate operations over a conditional subset of the data.

Again, I want to emphasise that this works within the filter context that is applied. E.g. if we applied a page filter so that we were only showing the people who live in London, then the average number of children will change as we have changed the filter context:

Showing average numver of children.

Showing average number of children, filtered to people who live in London.

DISTINCT

The DISTINCT operator just returns all the distinct values in a column.

For example:

Showing all distinct city values.

This can be very powerful when building up transformations.

We can also use it combined with COUNTROWS to calculate the number of distinct values in a table:

Counting distinct values in a table.

This will count the number of distinct names there are in the table. We can then apply this measure to find how many distinct people there are in each city:

Showing number of people aggregated by city.

For each row of this table, a filter context of e.g. People[City] = "London" has been applied.

ALL

The next table function is the ALL function. ALL is special in that it ignores all filter context. If you use ALL it will always work over the whole dataset, no matter what external filters have been applied.

This can be useful for calculating percentages/ratios.

Say that you created a measure to calculate the percentage of the people that live in London:

Showing percentage of people who live in London calculation.

When there are no filters applied you get:

Showing 40%.

(We can format this as a percentage when we create the measure).

However, if we apply a page filter so that we only show people who are in, say, Bristol, we get:

Showing (Blank).

Because the measure is now operating over a dataset which is filtered to just those who live in Bristol.

But if we add ALL to the table references:

Showing calculation of percentage using ALL in the denominator.

Then the percentage will always show 40.0% because the calculation will ignore any applied filter context and always use the entire dataset.

The ALL operator can also be used to display all the distinct values in a column, regardless of the filter context.

For example:

Showing using ALL to calculate the total number of people.

This is similar to the functionality of DISTINCT, but it will ignore any applied filter context. Therefore, in this case it will always return all of the distinct people in the table.

So, using this:

All people column shows 10 for every row in city table view.

We can see that for the column created by the DISTINCT operator, each cell has scanned the dataset which is defined by the filter context and returned the total number of rows. Whereas for the column which uses the ALL operator, all filter context is ignored and the total number of rows in the whole dataset (10) is always returned.

The final table operator I want to talk about is RELATEDTABLE, but that involves introducing a few new concepts… So, we shall save that for next time!

Power BI Weekly is a collation of the week's top news and articles from the Power BI ecosystem, all presented to you in one, handy newsletter!

Carmel Eve

Software Engineer I

Carmel Eve

Carmel is a software engineer, LinkedIn Learning instructor and STEM ambassador.

Over the past four years she has been focused on delivering cloud-first solutions to a variety of problems. These have ranged from highly-performant serverless architectures, to web applications, to reporting and insight pipelines and data analytics engines.

In her time at endjin, she has written many blog posts covering a huge range of topics, including deconstructing Rx operators and mental well-being and managing remote working.

Carmel's first LinkedIn Learning course on how to prepare for the Az-204 exam - developing solutions for Microsoft Azure - was released in April 2021. Over the last couple of years she has also spoken at NDC, APISpecs and SQLBits. These talks covered a range of topics, from reactive big-data processing to secure Azure architectures.

She is also passionate about diversity and inclusivity in tech. She is a STEM ambassador in her local community and is taking part in a local mentorship scheme. Through this work she hopes to be a part of positive change in the industry.

Carmel won "Apprentice Engineer of the Year" at the Computing Rising Star Awards 2019.

Carmel worked at endjin from 2016 to 2021.