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
byrow
FILTER
- Add new condition.
- Restricts the number of
rows
of a table. - Need a table as
input
andreturns
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 isempty
else returnsFALSE
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
: Used to apply filters on existing columns.Evaluate
a table expression andReturns
a table.- Useful for
Slicer
,Page
filter orReport
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 )
)
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
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
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 )
)
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
: 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]
)
- 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 includeMeasures
coming from different Tables. - Automatically removes the
empty
rows.
SUMMARIZECOLUMNS (
'Product Category'[Category] // Group By Column
'Product'[Color]
)