Advanced DAX : Table and Filter Functions

So today we are going to learn what are Table and Filter function in advanced Data Analysis Expression.

Table Filter : 

To display a subset of the items in a table, define filter rules that show only the items in which you are interested. Filtering does not refresh the table; therefore, you are filtering only the items currently listed in the table.

It Includes : 

• Filter Data
• Add Data
• Create Data

Let’s discuss each of them now

A  Filter Data : 

The filter and value functions in DAX are some of the most complex and powerful, and differ greatly from Excel functions. The lookup functions work by using tables and relationships, like a database. The filtering functions let you manipulate data context to create dynamic calculations.

It include:-

• ALL       
• FILTER
• DISTINCT
• VALUES
• ALLEXCEPT
• ALLSELECTED

let's look on these functions 

1. ALL:-

• Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied

Syntax : 

ALL ( [<TableNameOrColumnName>] [, <ColumnName> [, <ColumnName> [, … ] ] ] 

2. FILTER : 

• Returns a table that represents a subset of another table or expression.

Syntax : 

FILTER ( <Table>, <FilterExpression> )

Example : 

Employees (coffee wrangler) =

CALCULATE('Sales by Store'[total sales],

FILTER(

ALL('Employee Lookup'[position]),

'Employee Lookup'[position]= “coffee wrangler”)

)

Output : 

3. DISTINCT : 

• Returns a table by removing duplicate rows from another table or expression.

Syntax :

DISTINCT ( <ColumnNameOrTableExpr> ) 

Example : 

Distinct_product_ID =

COUNTROWS(

DISTINCT('Product Lookup'[product_id]

))

Output : 

4. ALLEXCEPT :

• Removes all context filters in the table except filters that have been applied to the specified columns.

Syntax : 

ALLEXCEPT ( <TableName>, <ColumnName> [, <ColumnName> [, … ] ] )

Example :

Using_All_Except =

CALCULATE(

[total sales],

ALLEXCEPT(

'Sales by Store',

'Calendar'[Transaction_Date],

'Store Lookup'[store_id],

'Customer Lookup'[customer_first-name],

'Product Lookup'[product_group])

)

Output :

5. VALUES : 

When a column name is given, returns a single column table of unique values. When a table name is given, returns a table with the same columns and all the rows of the table (including duplicates) with the additional blank row if present.

Syntax : 

VALUES ( <TableNameOrColumnName> )

6. ALLSELECTED:

Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied inside the query, but keeping filters that come from outside.

Syntax : 

ALLSELECTED ( [<TableNameOrColumnName>] [, <ColumnName> [, <ColumnName> [, … ] ] ] )

Example : 

Using_All_Selected =

CALCULATE(

[total sales],

ALLSELECTED(

)

)

B Add Data : 

Functions used to specify or add columns based on existing data in the model.

It include:-

• SELECTCOLUMNS
• ADDCOLUMNS
• SUMMARIZE

let's look on these functions 

1. SELECTCOLUMNS : 

Returns a table with selected columns from the table and new columns specified by the DAX expressions.

Syntax :

SELECTCOLUMNS ( <Table> [[, <Name>], <Expression> [[, <Name>], <Expression> [, … ] ] ] )

2. ADDCOLUMNS :

Adds calculated columns to the given table or table expression.

Syntax : 

ADDCOLUMNS ( <Table>, <Name>, <Expression> [, <Name>, <Expression> [, … ] ] )

Example : 

Add_columns =

ADDCOLUMNS(

FILTER(

'Store Lookup',

'Store Lookup'[store_id] IN {3,5,8}),

"Manager Name & Store",

'Store Lookup'[store_city] & " " &

'Store Lookup'[store_postal_code] & "-" & 'Store Lookup'[store_type]

)

3. SUMMARIZE :

Returns a summary table for the requested totals over a set of groups

Syntax : 

SUMMARIZE ( <Table> [, <GroupBy_ColumnName> [, [<Name>] [, [<Expression>] [, <GroupBy_ColumnName> [, [<Name>] [, [<Expression>] [, … ] ] ] ] ] ] ] )

Example :

Summarize Table =

SUMMARIZE('Product Lookup',

'Product Lookup'[product_group],

'Product Lookup'[product_id],

'Product Lookup'[product_category])

Output : 

C Create Data : 

Functions used to generate new rows, column & tables from scratch 

It include:-

• ROW
• DATATABLE
• GENERATESERIES
• TABLE CONSTRUCTOR

let's look on these functions

1.ROW :

Returns a table with a single row containing values that result from the expressions given to each column.

Syntax :

ROW ( <Name>, <Expression> [, <Name>, <Expression> [, … ] ] )

2.DATATABLE :

Provides a mechanism for declaring an inline set of data values.

Syntax :

DATATABLE ( <name>, <type> [, <name>, <type> [, … ] ], <data> )

Example :

DataTable Demo =

DATATABLE(

"Total Sales",CURRENCY,

"Total Profit",CURRENCY,

"Total Cost",CURRENCY,

{

{125000,75000,50000}

})

Output :

3.GENERATESERIES :

Returns a single column table containing the values of an arithmetic series, that is, a sequence of values in which each differs from the preceding by a constant quantity. The name of the column returned is Value.

Syntax : 

GENERATESERIES(<startValue>, <endValue>[, <incrementValue>])

Example : 

Geneate_series =

GENERATESERIES(

10,

20

)

Output : 

4.TABLE CONSTRUCTOR :

Returns a table of one or more columns.

Syntax : 

{ <scalarExpr1>, <scalarExpr2>, … } 
{ ( <scalarExpr1>, <scalarExpr2>, … ), ( <scalarExpr1>, <scalarExpr2>, … ), … }

Example : 

I hope you liked this.

Thank you,

Happy Learning!