3 DAX Table Functions You Must Master
Writing DAX in Power BI is always against tables isn’t it?
Our data are stored in tables, so analyzing them with DAX requires us to send DAX functions against the tables to carry out the computations we need.
You may say there is no problem there, since our tables have names. We can just refer to the names of the tables in our formulas. That truly works. But we won’t get pass default reporting if all we do is use table names as they are in our formulas. By defaults, I mean the default Filter Context as explained in my previous post.
This post is all theory, and a primer to the next couple of posts with technical examples.
Contents
What You Should Know About DAX Functions
Scalar Functions: Some DAX functions return single values as their results. Those are called Scalar functions. Eg, SUM function returns a single value of the sum of a column.
Scalar functions are useful in Measures and Calculated Columns since each reported category using a measure can have just a single value and each row in a calculated column can also have only a single value.
Table Functions: Some other DAX functions return more than one value as their results. They return a table so they are called Table functions. Eg, FILTER function returns a filtered Table.
These functions cannot be used alone to define a measure because a measure should output single values. The results of Table functions, however, can be passed into Scalar Functions to summarize into single values required for a Measure.
For example, you can not define a measure starting with = FILTER(… , …..). Instead, you can use that as an argument in SUMX that requires a Table argument like SUMX(FILTER(… , ….) , …). So SUMX can summarize the filtered table.
Another way to look at Table functions is, they can be used to manipulate table arguments in other functions. When a function requests for a Table as an argument, instead of supplying the default table names, a Table function can be used in place of that.
So, rather than the default table, it will be a table already modified by the Table function. Just like in the SUMX example previously stated.
Basic DAX Functions You Must Know
These are everyday use DAX functions we can’t run away from. When used within a Measure, they can be used as arguments of Functions that accept Tables as arguments.
The top 3 Table functions you must know are:
- FILTER
- ALL
- VALUES
FILTER Function
The FILTER function returns a table that has been filtered. It has 2 arguments: A Table and a Filter Expression. Of course, we know that for the table argument, we can also make use of another Table Function.
FILTER syntax
ALL Function
ALL returns all the rows in a table, or all the (distinct) values in a column regardless of the filters applied in the visual.
So ALL can take a Table Name or a Column Name as arguments. If a table name is supplied, it returns all the rows of the table, if a column is supplied, it returns all the values in the column and it ignores any filters coming from such column(s) in the reports.
Because ALL asks for a Table Name or a Column Name, and NOT a Table, we can not substitute that for another Table Function. It has to be a physical table name.
ALL syntax
VALUES Function
If there is a need to get all the rows in a table or the values in a column, just like the ALL function does, but doing so while maintaining the filter context (unlike the ALL function which ignores the filters context), then VALUES is the go-to function.
VALUES also takes either a Table Name or a Column Name as arguments. In most practical cases, using Column Name as argument is more popular.
Also, like ALL, only physical Table Name or Column Name can be supplied as arguments to VALUES.
VALUES syntax
Conclusion
You have enough theoretical information to dive into the practical examples in my post next week without having to consume too much information. Say No to Information Overload.
See you next week, when I will provide some simple practical examples on the usage of FILTER, ALL and VALUES within the context of DAX Measures.
