In this post I would like to Record my DAX Learnings during my SQL Server Analysis Services days.
SQL Server Analysis Services
It is 1000X faster than SQL Server since the Data is stored in Memory in Compressed Format.
DAX stands for Data Analysis Expressions. It is widely used in Analysis Services, Power BI etc.
DAX looks similar to Excel Formulas.
DAX Measure is the actual Formula.
DAX Variables will be executed once they are Invoked
Evaluates the Expression.
Eg: EVALUATE ( ‘DimProducts’)
Eg: EVALUATE( ‘DimProducts’ ) ORDER BY ‘DimProducts'[Date] DESC –Sorting Example
For Scalar Values, Use EVALUATE (ROW(“Column”, Variable)) syntax
Note: Add an EVALUATE in front of the Expression
Returns a Table with a Single Row.
Eg: EVALUATE ROW(“Total Sales”, SUM(FactSales[Price]),
“Total Quantity”, SUM(FactSales[Quantity]))
Returns a summary table.
, “Sales Total”, SUM(FactSales[Price])
, “Quantity Total”, SUM(FactSales[Quantity])
Evaluates an Expression when modified by a Filter.
Returns a Subset of Table or Expression.
*Note the double quotes
More Filters: https://bit.ly/2L3iOdr
Sum is an Aggregator.
“Sum”, SUM ( DimProducts[Price] )
SumX is an Iterator. Can Add Multiplications into this.
“Sum”, SUMX (DimProducts, DimProducts[Price] * 100)
Adds calculated columns to the given table