My DAX Days…

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

DAX stands for Data Analysis Expressions.  It is widely used in Analysis Services, Power BI etc. 

DAX looks similar to Excel Formulas.

DAX Measure

DAX Measure is the actual Formula.

DAX Variables

DAX Variables will be executed once they are Invoked

EVALUATE

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

ROW

Returns a Table with a Single Row.

Eg: EVALUATE ROW(“Total Sales”, SUM(FactSales[Price]), 
         “Total Quantity”, SUM(FactSales[Quantity]))

image

SUMMARY

Returns a summary table.

EVALUATE
SUMMARIZE(
        DimProducts 
      , DimProducts[Id] 
      , DimProducts[Class] 
      , DimProducts[SubClass] 
      , “Sales Total”, SUM(FactSales[Price]) 
      , “Quantity Total”,  SUM(FactSales[Quantity]) 
      ) 

image

CALCULATE

Evaluates an Expression when modified by a Filter.

FILTER

Returns a Subset of Table or Expression.

Eg: EVALUATE
FILTER(DimProducts,DimProducts[Class]=”Home Theatre”)

image

*Note the double quotes

More Filters: https://bit.ly/2L3iOdr

SUM

Sum is an Aggregator.

Eg: EVALUATE
SUMMARIZE (
DimProducts,
“Sum”, SUM ( DimProducts[Price] )
)

image

SUMX

SumX is an Iterator.  Can Add Multiplications into this.

Eg: EVALUATE
SUMMARIZE (
DimProducts,
“Sum”, SUMX (DimProducts, DimProducts[Price] * 100)
)

image

ADDCOLUMNS

Adds calculated columns to the given table

References

https://www.sqlbi.com/

https://msdn.microsoft.com/en-us/query-bi/dax

More DAX Days

In this post I would  like to Publish few more DAX scenarios I have encountered.

Join Tables

We can use CROSSJOIN function on tables to use All Columns required by the SUMMARIZE function.

DEFINE
    VAR JoinTable =
        FILTER (
            CROSSJOIN ( Products, FactSales ),
            DimProducts[Id] = SalesFactSales[ProductId]
        )

    VAR Result Table=
        SUMMARIZE (
            JoinTable ,
            DimProducts[Choice],
            DimProducts[Id],
            FactSales[StoreID],
            FactSales[WeekId],
            “Quantity”, SUMX (
                FILTER ( ‘FactSales’, ‘DimProducts'[Id] = ‘FactSales'[ProductId] ),
                FactSales[Quantity]
            )
        )

 

EVALUATE
ResultTable

SUMMARIZECOLUMNS

SUMMARIZECOLUMNS is recommended than SUMMARIZE because it is Faster & Optimized.

SUMMARIZE still exists for Backwordk Compatibility.

Let us see the Cross Join using SUMMARIZECOLUMNS.

Cross Join with Matching ProductID

DEFINE VAR Results =
SUMMARIZECOLUMNS (
    ‘DimProducts'[ProductID],
    “Sales Amount”,
    SUMX (        FILTER (
            CROSSJOIN ( DimProducts, FactSales ),
            DimProducts[ProductId] = FactSales[ProductId]
        ), FactSales[Quantity] * FactSales[Price] )

)

EVALUATE Results

image

Another way to see the results.

EVALUATE FILTER(CROSSJOIN (
                        DimProducts,
                        FactSales
                    ),
                    DimProducts[ProductId] = FactSales[ProductId]

)

— Returns as many rows as in Sales table

image

Working on Table Variables

We can work on Table Variables to do more Aggregations. Example is given below.

For example to find Average, Use AVERAGEX function.

VAR ResultsTable =
        ADDCOLUMNS (
            SUMMARIZE (
                ProductJoinSales,
                DimProducts[Choice],
                DimProducts[Id]
            ),
            “CalcValue”,  DIVIDE(
                DIVIDE (
                    SUM (
                        FactSales[Quantity]
                        ),
                        COUNT ( FactSales[StoreID] )
                )
            )
        )

VAR Result = AVERAGEX (ResultsTable, [CalcValue]) 

Note Ensure to display the value using ROW function.

EVALUATE ROW(“Average”, Result)

OR Condition in Filter

Or Conditions in Filters are pretty easy.  Use the || operator of C#.

FILTER ( FactSales, FactSales[Price] > 0 || FactSales[Quantity] > 0)

Reference

https://azure-arena.com/2018/08/03/my-dax-days/

https://azure-arena.com/2018/08/01/sql-tables-used-in-dax/

SQL Tables used in DAX

Following are the SQL Tables Schema used in the DAX exercises.

CREATE TABLE [dbo].[DimProducts](
    [ProductID] [varchar](50) NOT NULL,
    [Name] [varchar](50) NULL,
    [Class] [varchar](50) NULL,
    [SubClass] [varchar](50) NULL,
    [Price] [float] NULL,
    [Choice] [varchar](50) NULL,
CONSTRAINT [PK_DimProducts] PRIMARY KEY CLUSTERED
(
    [ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

 

CREATE TABLE [dbo].[FactSales](
    [SalesId] [varchar](50) NOT NULL,
    [Date] [date] NULL,
    [Quantity] [int] NULL,
    [Price] [float] NULL,
    [ProductID] [varchar](50) NULL,
    [WeekID] [int] NULL,
    [StoreID] [int] NULL,
CONSTRAINT [PK_FactSales] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Relations

The one-to-many relation is shown as below.

image