# Comprehensive Guide to DAX Table Functions in Power BI

Here is a comprehensive list of DAX table functions that you can use in Power BI. These functions help in creating and manipulating tables within your data model. Each function has a unique purpose that can aid in advanced data analysis. I’ve organized these functions into categories for better clarity.

## Table Functions in DAX

### 1. Table Creation Functions

These functions create new tables from existing tables or expressions.

**ADDCOLUMNS**

Adds calculated columns to a table.`ADDCOLUMNS(`

Table,

ColumnName1, Expression1,

ColumnName2, Expression2

)**CALENDAR/CALENDARAUTO**

Creates a date table.`CALENDAR(`

StartDate,

EndDate

)

CALENDARAUTO()**DATATABLE**

Manually creates a table with specified columns and rows.`DATATABLE(`

ColumnName1, DataType1,

ColumnName2, DataType2,

{Value1, Value2},

{Value3, Value4}

)**GENERATE**

Joins two tables by combining each row of the first table with each row of the second table.`GENERATE(`

Table1,

Table2

)

### 2. Table Transformation Functions

These functions transform or manipulate existing tables to return new tables.

**FILTER**

Returns a table with rows that meet a specified condition.`FILTER(`

Table,

Expression

)**ALL**

Returns all rows in a table, ignoring any filters that might have been applied.`ALL(`

TableName

)**VALUES**

Returns a one-column table that contains the distinct values from the specified column.`VALUES(`

ColumnName

)**SELECTCOLUMNS**

Returns a table with specified columns.`SELECTCOLUMNS(`

Table,

ColumnName1, Expression1,

ColumnName2, Expression2

)**SUMMARIZE**

Groups a table by specified columns and computes the aggregations.`SUMMARIZE(`

Table,

GroupByColumnName,

[ColumnName, Expression]

)**ADDCOLUMNS**

Adds new columns to a table using the specified expressions.`ADDCOLUMNS(`

Table,

ColumnName1, Expression1,

ColumnName2, Expression2

)

### 3. Joins and Relationships

These functions help in joining tables or managing relationships between tables.

**RELATEDTABLE**

Returns a table containing all the rows related to the current row from another table.`RELATEDTABLE(`

TableName

)**CROSSJOIN**

Returns the Cartesian product of all rows in the specified tables.`CROSSJOIN(`

Table1,

Table2

)**NATURALINNERJOIN/NATURALLEFTERJOIN**

Returns a table that is the result of an inner or left outer join of tables.`NATURALINNERJOIN(`

Table1,

Table2

)

NATURALLEFTERJOIN(

Table1,

Table2

)

### 4. Filtering and Evaluation Contexts

These functions operate within the context of the filters applied to the model.

**KEEPFILTERS**

Modifies the filter context of calculations.`KEEPFILTERS(`

Expression

)**REMOVEFILTERS**

Clears filters from the specified tables or columns.`REMOVEFILTERS(`

[ColumnName | TableName, ...]

)**ALLSELECTED**

Returns all rows in a table, or all values in a column, ignoring any filters that might have been applied but preserved from the selected context.`ALLSELECTED(`

[TableName]

)

### 5. Other Useful Table Functions

**UNION**

Creates a union of two tables.`UNION(`

Table1,

Table2

)**INTERSECT**

Creates a table with the intersection of two tables.`INTERSECT(`

Table1,

Table2

)**EXCEPT**

Returns the rows from one table that are not present in another table.`EXCEPT(`

Table1,

Table2

)**ROW**

Creates a single-row table.`ROW(`

ColumnName, Value

)

Understanding these DAX table functions can significantly enhance your ability to model and analyze data in Power BI.

# Comparing SUMMARIZE and ADDCOLUMNS in DAX

### SUMMARIZE vs. ADDCOLUMNS in DAX

When working with table manipulations in DAX within Power BI, two key functions that often come into play are `SUMMARIZE`

and `ADDCOLUMNS`

. Both functions are powerful tools for creating and transforming tables, but they serve different purposes and offer distinct functionalities.

#### 1. Purpose and Use-Case

**SUMMARIZE**

**Purpose**: To group data by specified columns and optionally compute aggregates.**Use-Case**: Ideal for creating summary tables by grouping and aggregating data similar to SQL’s GROUP BY clause.

**ADDCOLUMNS**

**Purpose**: To add new calculated columns to an existing table without altering its structure.**Use-Case**: Useful for adding additional calculated data to an existing table, like appending columns with custom calculations.

#### 2. Syntax

**SUMMARIZE**

```
SUMMARIZE(
table,
groupBy_columnName1,
groupBy_columnName2,
"name1", expression1,
"name2", expression2
)
```

`table`

: The source table.`groupBy_columnName`

: Columns by which the data will be grouped.`"name"`

: Aliases for the calculated columns.`expression`

: DAX expressions to compute the summaries.

**ADDCOLUMNS**

```
ADDCOLUMNS(
table,
"new_columnName1", expression1,
"new_columnName2", expression2
)
```

`table`

: The source table.`"new_columnName"`

: Names of the new columns to be added.`expression`

: DAX expressions to compute the values for the new columns.

#### 3. Example Usage

**SUMMARIZE**

- Suppose you have a
`Sales`

table and want a summary of total quantity sold by product category:

```
SUMMARIZE(
Sales,
Sales[ProductCategory],
"Total Quantity", SUM(Sales[Quantity])
)
```

This results in a table grouped by `ProductCategory`

with a column named “Total Quantity” containing the sum of quantities.

**ADDCOLUMNS**

- Using the same
`Sales`

table to add a calculated column for the sales tax:

```
ADDCOLUMNS(
Sales,
"SalesTax", Sales[TotalPrice] * 0.1
)
```

This adds a new column “SalesTax” to each row in the `Sales`

table, calculated as 10% of the `TotalPrice`

.

#### 4. Differences Summarized

**Grouping**:`SUMMARIZE`

groups data and can perform aggregations;`ADDCOLUMNS`

does not group but adds calculated columns.**Aggregation**:`SUMMARIZE`

is primarily used for aggregation;`ADDCOLUMNS`

is used for row-wise calculations.**Output Structure**:`SUMMARIZE`

can potentially reduce the number of rows by grouping;`ADDCOLUMNS`

maintains the original number of rows.

#### 5. Best Practices

- Use
when you need to create a summary table with aggregated data.`SUMMARIZE`

- Use
when you need to append calculated columns without disrupting the structure of the existing table.`ADDCOLUMNS`

- Avoid misuse: Applying aggregation functions in
`ADDCOLUMNS`

might lead to row context issues; use`SUMMARIZE`

or other aggregation appropriate functions instead.

Understanding the distinctions between `SUMMARIZE`

and `ADDCOLUMNS`

enhances your ability to manipulate tables effectively in DAX. By leveraging these functions appropriately, you can achieve precise and efficient table transformations and aggregations.

# Understanding INTERSECT and EXCEPT in DAX

### Difference Between INTERSECT and EXCEPT in DAX

In DAX, `INTERSECT`

and `EXCEPT`

are two important functions used to perform set operations on tables. Each has distinct functionality and use cases. Below, I outline their main differences, usage, and examples.

#### Overview

**INTERSECT**:- Purpose: Returns the common rows between two tables.
- Usage: Useful for identifying overlapping or matching rows.

**EXCEPT**:- Purpose: Returns the rows in the first table that are not present in the second table.
- Usage: Useful for identifying rows that are unique to the first table.

#### Syntax

**INTERSECT**

```
INTERSECT(<Table1>, <Table2>)
```

**EXCEPT**

```
EXCEPT(<Table1>, <Table2>)
```

#### Key Differences

**Commonality vs. Uniqueness**:`INTERSECT`

finds rows common to both tables.`EXCEPT`

finds rows unique to the first table.

**Row Matching**:`INTERSECT`

requires an exact match of rows between the two tables.`EXCEPT`

checks for rows in the first table that do not exist in the second table.

#### Examples

**INTERSECT Example**

Consider two tables representing sales data:

Table1

ProductID | Sales |
---|---|

1 | 100 |

2 | 150 |

3 | 200 |

Table2

ProductID | Sales |
---|---|

2 | 150 |

3 | 200 |

4 | 250 |

To find the common rows in Table1 and Table2:

```
CommonRows =
INTERSECT(
Table1,
Table2
)
```

Result:

ProductID | Sales |
---|---|

2 | 150 |

3 | 200 |

**EXCEPT Example**

Using the same tables, to find rows in Table1 that are not in Table2:

```
UniqueRows =
EXCEPT(
Table1,
Table2
)
```

Result:

ProductID | Sales |
---|---|

1 | 100 |

#### Usage Considerations

**INTERSECT**: Use when you need to find overlapping data points.**EXCEPT**: Use when you want to isolate data points unique to one table.**Performance**: Both functions may have performance implications on large datasets, so consider this when applying them.

#### Conclusion

Understanding the distinct purposes of `INTERSECT`

and `EXCEPT`

in DAX allows you to perform more precise and effective data manipulations. Whether identifying common entries or unique data points, these functions are instrumental in advanced data analysis in Power BI.

For further learning and best practices, consider exploring courses on the Enterprise DNA Platform.