05.Table and Filter Functions.md

Table and Filter Functions

DAX has more than 250 functions, many of them manipulate tables.

Filter Tables or filter results of Measures

Filters are Tables ( Boolean expressions are shortcut for table expressions )

Table expands only from Many to One side.

Always apply FILTER on single column rather than applying on entire Table ( Best Practice )

CALCULATE (
    SUM ( Sale[Amount] ),     
    Sale[Amount] > 100        // Filter ( Boolean table expression )
)    

Is equivalent to : ( What happens internally )

CALCULATE (
    SUM ( Sale[Amount] ),
    FILTER (
        ALL ( Sale[Amount] ),
        Sale[Amount] > 100
    )    
)   

Filter Data
ALL	Returns all the rows in a table, or all the values in a column, ignoring any filters.
FILTER	Returns a filtered table based on one or more filtered expressions.
DISTINCT	Returns a single column table of unique values
VALUES	Returns a single column of unique values when a column name is passed. if a table is passed, VALUE returns the entire table including duplicates and blank rows.
SELECTEDVALUE	Returns a value when there is only one value in a specified column.
ALLEXCEPT	Removes all context filters in a table except the filter is applied to the specified columns.
ALLSELECTED

ALL

• No need to use ALL, if you are creating a Calculated Column.
• Because in Calculated Column every operation is performed row by row

FILTER

• Add new condition.
• Restricts the number of rows of a table.
• Need a table as input and returns a table.
• The input can be another FILTER
• Can be iterated using X functions ( SUMX, AVERAGEX, MAXX, MINX, etc. )

ISEMPTY

• Check if a table is empty
• Returns TRUE if the table is empty else returns FALSE

Specify or add Columns based on existing data.

Add Data
SELECTCOLUMNS
ADDCOLUMNS
SUMMARIZE	Create a summary of table grouped by specified columns

Generate new Rows, Columns and Tables from scratch.

Create Data
ROW	Returns a single row table with new specified columns.
DATATABLE	Returns a new static data.
GENERATESERIES	Returns a single column table containing sequential values.
{ } Table Constructor	Returns a table containing columns and rows.

Calculated Table Joins
CROSSJOIN	Caretsian product of two tables ( All possible combinations )
UNION	Stacks two table together ( Vertically )
EXCEPT	Returns all rows from left table which do not appear in right table.
INTERSECT	Returns all rows from left table which also appears in right table ( Common )

Cartesian

• Resulting table contains m * n rows
• Resulting table contains m + n columns.
• Column names should be different in all the columns.

Union

• All tables must contain same number of columns
• Columns are combined by position
• Column names are determined by first table.
• Union creates Duplicate rows.

Except

• All tables must contain same number of columns
• Columns are combined by position
• Column names are determined by left table.
• Relations cannot be created with 3rd table.
• Useful in the case to find employee and customer churning

Intersect

• All tables must contain same number of columns
• Accepts only 2 Tables.
• Result of INTERSECT(T1,T2) will be different from INTERSECT(T2,T1)
• Union creates Duplicate rows.
• Column names are determined by left table.
• Relations cannot be created with 3rd table.
• Useful in the case to find active customer, repeat purchases and new employee or new customers in recent period.

CALCULATETABLE

• CALCULATETABLE : Used to apply filters on existing columns.
• Evaluate a table expression and Returns a table.
• Useful for Slicer, Page filter or Report filter.
• Slicer filters column not entire table.

CALCULATETABLE (
    Product,                // Table Expression
    Product[Color] = "Red"  // Any Slicer
)

For each product it compares whether the color of the product is red.

CALCULATETABLE (
    Product,                                      // Table Expression
    Product[Color] IN { "Red", "Blue", "Green" }  // Any Slicer ( Multiple Selection )
)

Evaluation Order

CALCULATE and CALCULATETABLE first evaluates the outer filter.

CALCULATETABLE (
    CALCULATETABLE ( 
        Product,                // 3rd ( All the products of all the colors )
        ALL ( Product[Color] )  // 2nd ( Ignores the filter on the color column )
    ),
    Product[Color] = "Red"      // 1st ( Add filter for product with only red color )
)

FILTER

FILTER should be used for row context ( Calculation for each row )

When we need to filter by using Measure then use FILTER

FILTER (
    Product,                                      
    Product[Color] IN { "Red", "Blue", "Green" } 
)

SELECTCOLUMNS

SELECTCOLUMNS is similar to SELECT query in SQL

Select the columns from an existing table.

Due to row context we get each and every rows ( including duplicate values )

Reduces the column ( select only those we need in a report )

SELECTCOLUMNS (
    'Product', 
    'Category', RELATED ( 'Product Category'[Category] ), // From another table with relationship corresponding to current product
    'Color', Product[Color],
    'Name', Product[Name],
    'Sales Amount', [Sales Amount]                        // Measure ( Sales Amount of the current product )
)   

ADDCOLUMNS

Includes all the Rows and Columns of the existing table and we can add new columns ( extends the columns )

ADDCOLUMNS (
    'Product',                      // Table 
    'Sales Amount', [Sales Amount]  // New Column
)

ADDCOLUMNS + SELECTCOLUMNS

ADDCOLUMNS (
    SELECTCOLUMNS (
        'Product',
        'Color', Product[Color],
        'Amount', [Sales Amount]   // Color and Amount is evaluated for each Product.
    ),
    'Product Color Amount', [Sales Amount]
)

SUMMARIZE

SUMMARIZE : Similar to SELECT DISTINCT and GROUP BY in SQL

Summarize Sales of Product by its Category.

SUMMARIZE (
    Sales,                        // Source Table
    'Product Category'[Category]  // Group By Column
    'Product'[Color]
)

We can add new column in SUMMARIZE

ADDCOLUMNS (
    SUMMARIZE (
        Sales,                        // Source Table
        'Product Category'[Category]  // Group By Column
        'Product'[Color]
    ),
    "Sales", [Sales Amount]
)    

SUMMARIZECOLUMNS

• Advance version of SUMMARIZE
• Similar to SUMMARIZE, but we don't have to specify the Table Expression.
• Cannot be used in a Measure
• But SUMMARIZECOLUMNS can include Measures coming from different Tables.
• Automatically removes the empty rows.

SUMMARIZECOLUMNS (
    'Product Category'[Category]  // Group By Column
    'Product'[Color]
)