This section will provide the best usage scenarios for various functions.

Using Functions ADDCOLUMNS, SELECTCOLUMNS, SELECTATTRIBUTES

All of these functions return a table that has special columns as per the requirement.

• Case A: Only original columns can use the SelectAttributes function.Example: SELECTATTRIBUTES('Product'[Name])

• Case B: Only calculated column or only extract date can use SelectColumns.Example: SELECTCOLUMNS('Product',"calculatedcolumn",'Product'[Name] & "1", "extractDay",Day('Order'[OrderDate]))

• Case C: When combining Case A and Case B, this case need to use AddColumns and SelectAttributes functions.Example: ADDCOLUMNS(SELECTATTRIBUTES('Product'[Name]),"calculatedcolumn",'Product'[Name] & "1")

Choosing a base table

If the first parameter needs a table in an aggregation function or window function, this table is the base table.

Example:

Case A: WAX expression is:

 countx('Sales','Product'[ProductID])

In this case, when we calculate this aggregation, the SQL generated will be like the one shown below:

Select count(product.productid) from sales left join product on sales.productid=product.productid

Case B: WAX expression is:

countx('Product','Product'[ProductID])

In this case, when we calculate this aggregation, the SQL generated will be like the one shown below:

Select count(product.produtid) from product

Using Table Manipulation functions

Users always generate two types of tables as listed below:

• First Type: Containing scalar columns.

• Second Type: Containing dimension columns and aggregation function result or window function result.

For the first type of usage: AddColumns, SelectColumns, or SelectAttributes can be referred to.

For the second type of usage: Always use SUMMARIZECOLUMNS and SUMMARIZE.

Using Filter functions

In WAX, there are a lot of functions that can filter data. We always use Filter or Calculate function for this.

But, there is a difference in which the filtering works in both the Filter and Calculate functions:

The Filter function is "After" filtering and Calculate function is "Before" filtering.

Examples

Case 1

calculate(sumx(All('FactInternetSales'),'FactInternetSales'[SalesAmmount]),'DimProductCategory'[EnglishProductCategoryName]="Clothing")

In this case, the program sends the filter expression( 'DimProductCategory'[EnglishProductCategoryName]="Clothing") to the first parameter of sumx, which means sending it to the inner table of the All function. But the All function will ignore every context(filter context, dimension context). So, in this case, the filter expression won't act on the first parameter of sumx. This expression equals the below expression:

sumx('FactInternetSales','FactInternetSales'[SalesAmmount])

Case 2

sumx(Filter(All('FactInternetSales'),'DimProductCategory'[EnglishProductCategoryName]="Clothing"),'FactInternetSales'[SalesAmmount])

Even if we don't use ALL function, the result is the same.

In this case, the filter expression will act on the result of the first parameter of sumx function. Which means, it will act on the result of the ALL function.

Using All function

This function is very tricky. It will ignore every context(dimension context, filter context) sent from outside.

Example

SUMMARIZE(
        CALCULATETABLE(
            SELECTATTRIBUTES(
                'DimProductCategory'[EnglishProductCategoryName]
            )
        ),
        'DimProductCategory'[EnglishProductCategoryName],
        "SUMTEST",
        COUNTX('DimProductCategory',1)
)

The WAX in the above example, if translated to SQL will be like this:

Select EnglishProductCategoryName, count(1) from  DimProductCategory group by  EnglishProductCategoryName

But , when we add All function as below:

SUMMARIZE(
        CALCULATETABLE(
            SELECTATTRIBUTES(
                'DimProductCategory'[EnglishProductCategoryName]
            )
        ),
        'DimProductCategory'[EnglishProductCategoryName],
        "SUMTEST",
        COUNTX(ALL('DimProductCategory'),1)
)

It will ignore dimension context from outside, so, the SQL will be like this:

Select count(1) from  DimProductCategory

DIVIDE function vs Divide operator(/)

As a data modeler, when you write a WAX expression to divide a numerator by a denominator, you can choose to use the DIVIDE function or the divide operator (/ - forward slash).

DIVIDE(<numerator>, <denominator>)

The DIVIDE function was designed to automatically handle division by zero cases. when the denominator is zero or NULL, the function returns NULL.

But the divide operator doesn't handle this scenario, when the denominator is zero or NULL, it will show an error.