In the second part of the series on how to solve business problems using DAX we looked at some patterns for calculating averages different grains in the model. In this series we will look at patterns where we need sales amount filtered for a value(s) of an column of the dimension/fact table(s). Think of this pattern as equivalent to applying a WHERE clause to a SQL query. The WHERE clause can  be applied either on a dimension column or on a fact column

 

The Business Problem – Sales Amount for Contoso Brand

 

A common business problem is to slice and dice Facts by their Dimensional attributes. A problem like “What is the sales amount for Brand XXX” is a common one. Similar examples are like the following

• What is the tonnes mined by a CAT excavator ?
• What is the total units manufactured in 2021 ?
• What are the expenses incurred by external consultants ?

 

Coming back to the problem of “What is the sales amount for Contoso Brand ?”, we need to filter the [Total Sales] measure where Brand = ‘Contoso’. The pattern is as follows

 

Contoso Sales =
CALCULATE ( [Total Sales], DimProduct[BrandName] = “Contoso” )

 

The result is as follows

 

 

Internally, the DAX measure is  written as

 

Contoso Sales =
CALCULATE (
    [Total Sales],
    FILTER ( ALL ( DimProduct[BrandName] ), DimProduct[BrandName] = “Contoso” )
)

 

This is important to know, as many a times I see the measure written as below.

 

Contoso Sales DO NOT USE =
CALCULATE (
    [Total Sales],
    FILTER ( DimProduct, DimProduct[BrandName] = “Contoso” )
)

 

While technically this measure produces the same result as the first one, there are two problems with it.

1. Instead of materialising just one column, BrandName, the entire table DimProduct may be materialised (depending on the version of Analysis Services you are running) which can make the measure slower to execute
2. The measure will return blank when a value apart from ‘Contoso’ is selected from the BrandName column slicer in the report. This may not be the result needed as in most cases we always need to display ‘Contoso Sales’ regardless of what value is selected from the BrandName column slicer

 

The Business Problem – Sales Amount for Contoso Brand AND Silver Colour

 

Businesses often want to report on metrics which are filtered to values from multiple entities in the dimension tables. The questions can take the following examples

• What is the sales amount for Contoso Brand and Silver Colour ?
• What is the total tonnes for CAT excavator in 2021 ?
• What is the expenses incurred by external consultants in the last quarter ?

The entities can come from the same or different table. The generic pattern when the AND condition is involved becomes the following for the problem “What is the sales amount for Contoso Brand and Silver Colour” becomes

Contoso Silver Sales =
CALCULATE (
    [Total Sales],
    DimProduct[BrandName] = “Contoso”
        , DimProduct[ColorName] = “Silver”
)

 

When the entities are from the same table which is the case in the above example this can also be written as

Contoso Silver Sales =
CALCULATE (
    [Total Sales],
    DimProduct[BrandName] = “Contoso”
        && DimProduct[ColorName] = “Silver”
)

 

The result is as follows

 

 

It is pertinent to note that the result is a subset of Contoso Sales as we want the Total Sales where BrandName = ‘Contoso’ AND ColorName = ‘Silver’. We can keep adding entities from the same table by appending ‘&&’ to them or from different tables by appending ‘,’ to them.

 

The Business Problem – Sales Amount for Contoso Brand OR Silver Colour

 

Sometimes the business problem is to determine the sales amount which involves an OR condition. Businesses want to know the sales amount where Brand is ‘Contoso’ or Colour is ‘Silver’. The pattern then becomes

 

Contoso OR Silver Sales =
CALCULATE (
    [Total Sales],
    DimProduct[BrandName] = “Contoso”
        || DimProduct[ColorName] = “Silver”
)

 

The result then becomes

 

 

Note that the ‘ || ‘ operator can only be used if we are filtering attributes from the same table. If you are using different you cannot use it, as it equates to using an AND operation and hence you should use ‘ , ‘ instead.

 

The Business Problem – Sales Amount for Brand Not Equal to Contoso OR Colour Not Equal to Silver

 

Sometimes the business problem is to calculate sales which does not equal values from one or more entities. In this case instead of focusing on the logic for the negation, focus on the logic for the reverse which is easier to do and then wrap it around the NOT operator. The pattern for the problem “What is the sale amount for brand not equal to Contoso or colour not equal to Silver ?” becomes

 

NOT Contoso OR Silver Sales =
CALCULATE (
    [Total Sales],
    NOT ( DimProduct[BrandName] = “Contoso”
        || DimProduct[ColorName] = “Silver” )
)

 

The result then becomes

 

 

How can we validate this result ? We know that the total sales is 2.72bn. We also know that the sales for “sales amount when brand is Contoso OR Color is Silver” is 1.06bn. So the difference of the two (2.72 – 1.06 = 1.66) should be the negation which is what the above measure gives.

 

The Business Problem – Average Sales Amount for Brand Not Equal to Contoso OR Colour Not Equal to Silver

 

What if we are interested in the Average Sales Amount by Sales Order but only for brands which are not equal to Contoso OR colour is not equal to Silver. We use the same patterns as above but instead of [Total Sales] we substitute it with the [Average Sales by Sales Order]. For clarity we will define the previous measure inside CALCULATE but you could define it as a measure on its own and call it inside CALCULATE.

 

Average Sales Amount for Brand Not Equal to Contoso OR Colour Not Equal to Silver =
CALCULATE (
    AVERAGEX ( VALUES ( FactOnlineSales[SalesOrderNumber] ), [Total Sales] ),
    NOT ( DimProduct[BrandName] = “Contoso”
        || DimProduct[ColorName] = “Silver” )
)

The result then becomes

 

 

 

 

Summary

In this third part of the series, we have seen how to use the WHERE equivalent of a SQL query or SUMIFS, COUNTIFS, AVERAGEIFS of Excel in DAX. Use the following patterns to answer the following questions.

Any problem involving the filtering measures in the fact table by one or many dimensional/fact table attributes and calculating totals

• What is the tonnes mined by a CAT excavator ?
• What is the sales for Contoso Brand and Silver Colour ?
• What are the expenses incurred by external consultants or internal consultants ?

 

Contoso Sales and Silver Colour =
CALCULATE ( [Total Sales], DimProduct[BrandName] = “Contoso” , DimProduct[ColorName] = “Silver”)

 

Contoso Sales or Silver Colour =
CALCULATE ( [Total Sales], DimProduct[BrandName] = “Contoso”  || DimProduct[ColorName] = “Silver”)

 

If you want the negation of the above measure

 

NOT Contoso OR Silver Sales =
CALCULATE (
    [Total Sales],
    NOT ( DimProduct[BrandName] = “Contoso”
        || DimProduct[ColorName] = “Silver” )
)

 

Any problem involving the filtering measures in the fact table by one or many dimensional/fact table attributes and calculating totals

• What is the average daily sales for Contoso Brand in 2008
• What is the average monthly tonnes mined by CAT excavator in Mine A1
• What are the average quarterly expenses incurred by external consultants last year ?

 

 

Average daily sales for Contoso Brand in 2008 =
CALCULATE (
    AVERAGEX ( VALUES ( DimDate[Datekey] ), [Total Sales] ),
    DimProduct[BrandName] = “Contoso”,
    DimDate[CalendarYear] = 2008
)