#### October 9, 2020

## X-functions in DAX

*For Power BI version 2.76.5678.782 (December 2019).*

X-functions in DAX are iterator functions. This means they are aware of rows. For example, function SUMX() calculates the sum of expression for each row separately. You can find more on row context in the article Row Context in Power BI and Context Transition in Power BI. Let’s look at the the most common x-functions.

# SUMX()

Calculates the value of the expression for each row and returns the sum of all the rows.

## Syntax

SUMX(Table, Expression)

Parameter | Description |

Table | Name of the table |

Expression | Expression that is calculated for each row |

## Example

Table of sold products **Sales** with fields **Price** and **Quantity**.

We want to calculate total income from sold products. We can’t use **SUM()**, we have to calculate the product for each row separately and then sum the rows into final result. That is what **SUMX()** does.

SUMX_Example = SUMX(Sales, Sales[Price]*Sales[Quantity])

For mathematically fluent readers: **SUMX()** acts like dot product in this case.

# AVERAGEX()

Calculates the value of the expression for each row and returns the average of all the rows.

## Syntax

AVERAGEX (Table, Expression)

Parameter | Description |

Table | Name of the table |

Expression | Expression, evaluated for each row |

## Example

AVERAGEX_Example = AVERAGEX(Sales, Sales[Price]*Sales[Quantity])

Example calculates the product of **Price** and **Quantity** column in **Sales** table for each row and returns the average of all the rows.

# MINX()

Calculates the value of the expression for each row and returns the lowest value.

## Syntax

MINX(Table, Expression)

Parameter | Description |

Table | Name of the table |

Expression | Expression, evaluated for each row |

## Example

MINX_Example = MINX(Sales, Sales[Price]*Sales[Quantity])

Example calculates the product of **Price** and **Quantity** column in **Sales** table for each row and returns the minimum of all values.

# MAXX()

Calculates the value of the expression for each row and returns the highest value.

## Syntax

MAXX(Table, Expression)

Parameter | Description |

Table | Name of the table |

Expression | Expression, evaluated for each row |

## Example

MAXX_Example = MAXX(Sales, Sales[Price]*Sales[Quantity])

Example calculates the product of **Price** and **Quantity** column in **Sales** table for each row and returns the maximum of all values.

# COUNTX()

Returns the number of non-empty rows in a column or number of non-empty result of the expression.

## Syntax

COUNTX(Table, Expression)

Parameter | Description |

Table | Name of the table |

Expression | Expression, evaluated for each row |

## Example

COUNTX_Example = COUNT(Sales, Sales[Price]*Sales[Quantity])

Example calculates the product of **Price** and **Quantity** column in **Sales** table for each row and returns the number of non-empty results.

# CONCATENATEX ()

Calculates the value of expression for each row and returns concatenated values.

## Syntax

CONCATENATEX (Table, Expression, [Delimiter])

Parameter | Description |

Table | Name of the table |

Expression | Expression, evaluated for each row |

Delimiter | Delimiter used to separate expression (optional) |

## Example

Table **Customers** contains columns **FirstName **and **LastName**.

We use **CONCATENATEX() **function to get a list of all the customers.

CONCATENATEX_Example = CONCATENATEX(Customers, [FirstName] & ” ” & [LastName], “,”)

Function returns “Craig Ferguson, James May”.