# Excel Magic Trick 1433

## Materialise DAX Table Functions: Existing Connections, DAX Studio, Power BI?

### DESCRIPTION

Because we don’t have an F9 key to evaluate formula elements like we do in Excel, learning how to materialize or evaluate or “see” DAX Formula elements is important. In this video see three methods to materialize DAX formula elements with one of the following: Excel’s Existing Connections & Edit with DAX feature, DAX Studio (free download), or Power BI Desktop (free download). Also learn about the DAX Table functions: ALL, ADDCOLUMNS, ALLNOBLANKROW, ALLEXCEPT, FILTER, and CROSSJOIN. Here is what you will see:
(00:14) Introduction to the F9 key of DAX, and intro to Data Model in downloadable file, and intro to the DAX Table functions we will see in this video.
(03:53) How to access tables in Data Model using the Existing Connections command in the Data Ribbon Tab in order to export data from the Data Model to an Excel Sheet.
(04:28) The difference between an Excel Table and a Data Model Excel Table created by the Existing Connections command.
(04:57) Edit DAX command in a Data Model Excel Table that allows use to write DAX Code to manipulate a Data Model Excel Table, or to visualise or “see” or materialise DAX Functions and DAX Formula elements to help us better understand how DAX Formulas work.
(05:11) EVALUATE command in DAX to allow us to materialise a table rather than a scalar value.
(05:30) ALL DAX Function- Removes all Filters on a Table, Column, or Set of Columns.
(05:42) See that the DAX Code, ALL(Table), will materialise the entire table.
(06:00) See that the DAX Code, ALL(Column), will materialise a Unique List of items when the column comes from the Many Side of the Relationship.
(06:17) Description of the difference between when the ALL Function uses a Column from the Many Side of a relationship and the One side of a relationship when there is an unmatched item on the Many side of the relationship.
(07:16) See that the DAX Code, ALL(Column,Column), will materialise a Unique set of records from the columns.
(08:13) When you use the Existing Connections command in the Data Ribbon Tab in order to export data from the Data Model to an Excel Sheet, you are not connecting to just a single table, you are actually connecting to the entire Data Model.
(08:40) See that the DAX Code, ALL(Column), will materialise a Unique List plus an extra blank row for any unmatched items in a relationship when the column is from the Lookup Table or Dimension Table.
(09:21) See a DAX Formula that uses the DAX Functions: ADDCOLUMNS, ALL, CALCULATE and SUM to add a new column to our materialised table, including the blank row that is designed to catch values for any unmatched items in the relationship. ADDCOLUMNS function adds new column/s to a table.
(12:17) See that the DAX Code, ALLNOBLANKROW(Column), will materialise a Unique List while excluding the extra blank row for any unmatched items in a relationship when the column is from the Lookup Table or Dimension Table.
(13:32) Using DAX Studio to materialise DAX Tables
(14:41) See that the DAX Code, ALLEXCEPT(Table,Column), will materialise a Unique Set Of Records from the columns in table, without the excluded column.
(16:26) See that the DAX Code, FILTER(Table, Condition), will materialise a Returned table that satisfies condition. The FILTER function filters out all rows that do not meet the condition.
(17:24) Power BI Desktop to materialise a DAX Table Function, including importing Excel Data Model into Power BI Desktop.
(18:37) See that the DAX Code, CROSSJOIN(Table,Table), will materialise Cartesian product, which multiplies rows in two or more tables to get total number of combinations, or a resultant table that has each record in the first table match up with each record in the second table.
(19:50) Summary