AtScale Tabular Report Expression Examples

Introduction

This functionality begins with release AtScale I2023.4.1

Tabular models and the Data Analysis Expression (DAX) language offer Microsoft Power BI users enormous analytic and visualization capabilities. As powerful as these tools are, when using them, it can feel as though one must repeatedly "reinvent the wheel" to emulate industry-standard multi-dimensional BI features that have existed for a long time (see Table 1). If you find yourself hand-writing DAX code to implement solutions for long-solved problems like compound-key joins or custom calendar growth metrics, then AtScale's DAX + multi-dimensional modeling solution is for you!  With AtScale's support for client-side DAX expressions, Power BI data analysts avoid "reinventing the wheel" by leveraging the power of multi-dimensional models without sacrificing the flexibility of client-side report expressions.

 

Table 1: Subset of multi-dimensional features that are not standard Tabular model features

 

Feature DAX Expressions + AtScale Model DAX +  Tabular Model
Dimensional attributes with name and value column bindings Out-of-the-box Separate columns must be managed with look-ups.
Period-based time calculations (support for retail and manufacturing calendars) Out-of-the-box Custom coding is required to handle retail and manufacturing calendars.
Role-played relationships Out-of-the-box One active relationship between tables. Custom coding is required to switch active relationships.
Compound key relationships Out-of-the-box Custom coding is required.

Multi-table hierarchies Out-of-the-box Custom coding is required.
Microsoft Excel-friendly hierarchies Out-of-the-box Not supported.
Built-in first/last not empty aggregate functions (for modeling account balances) Out-of-the-box Custom coding is required.
Unrelated dimension handling Out-of-the-box Custom coding is required.

 

Important Principles

When writing DAX expressions that work with AtScale multi-dimensional models, it is important to keep in mind the following principles:

  1. DAX expressions reference the AtScale model's Dimensions, Hierarchies, and Measures, rather than database Tables or Columns.
  2. AtScale uses the relationships defined in the model to identify the correct tables and columns based on the Measures, Dimensions and Attributes used in the expression.  
  3. Unlike Tabular models, AtScale supports multiple active relationships between tables simultaneously through the concept of role playing. This means that you specify which relationship you wish to use by simply requesting the desired role-played name of a Dimension. The concept of manually activating and deactivating relationships using DAX functions does not apply when querying AtScale models.
  4. AtScale measures are equivalent to Tabular implicit measures. Most aggregation operations can be achieved by simply requesting the measure by name. Unless you are post-aggregating data, it is not necessary to wrap the measure in an aggregation function because you are not referencing a database column directly.
  5. Data granularity is controlled by the lowest level of the related dimensions. The model architect defines maximum and minimum data granularity by connecting fact datasets to dimension hierarchies in the AtScale model. As a result, report authors can access data down to the lowest connected dimension level.

Syntax

The rest of this document illustrates the syntax used when querying an AtScale model with DAX expressions. The syntax conventions are summarized as follows:

  1. When querying measure values, function table arguments are either: 
    • CubeMeasures (no quotes, literally this string)
    • '<Dimension Name>' (for example, 'Product' for the Product dimension)
  2. Measures may be referenced using square brackets and may optionally be preceded with the term 'CubeMeasure'. For example, both of these expressions reference the measure Internet Sales Amount:
    • [Internet Sales Amount]
    • 'CubeMeasure'[Internet Sales Amount]
  3. Dimensional attributes are referenced using the convention '<DimensionName>'[AttributeName]. For example, the Color attribute of the Product dimension is referenced as 'Product'[Color]. 
  4. Either measures or dimensional attributes can be used as function column arguments, depending on the specific function.

Prerequisites

Before executing the use cases described in this document, do the following:

  1. Import the AtScale AdventureWorksDW2012Multidimensional-EE project and datasets.
    If you have write access to the underlying data warehouse, you can do this in a single step by importing this data-loader bundle.
  2. Put the AtScale engine into Tabular mode, or plan on connecting to the deployed project using the Tabular connection string parameter. For example: http://<atscale_machine_ip>:10502?daxdialect=tabular
  3. Deploy the project to a running AtScale engine.
  4. Connect PowerBI to the model with the AtScale Models or SQL Server Analysis Services connectors.

AtScale Semantic Model

The use-cases described in this document are based on the AtScale ASAdventureSmall model (AdventureWorksDW2012Multidimensional-EE), which in turn is based on the Microsoft AdventureWorks sample database. If your AtScale data warehouse connection has write permission to your data warehouse, you can load the model, create the schema, and load the tables by simply loading the ASAdventureSmall data-loader bundle (see Prerequisites).

 

Measures are based mainly on the Internet Sales fact table, which contains the customer sales facts along with the relevant dimensions keys.

Internet Sales fact table with most common dimensions

You can also use the other fact table, Reseller Sales, which contains similar metrics from the reseller perspective.

There are a few dimensions that are common across the examples, namely: 

  • Product
  • DateCustom
  • Internet Sales Order Details

Filter Context Control

Use Cases

Removing Filter and Grouping Columns from the Filter Context

Example Scenario: Create an expression that returns an Order's contribution to the total of the displayed grouping dimensions. 

This calculation should work with whatever dimensions you include in the visualization, without modifying the expression. For example, when grouping the [Internet Sales Amount Local] measure by Date and Color, the calculation should show the sale's contribution to the Color and Day’s subtotal. If you remove Color from the display, the calculation should show the sale’s contribution to the Day's subtotal. For Tableau users, this is equivalent to Talbeau's LOD EXCLUDE functionality.

 

Key Functions:

  • CALCULATE
  • ALL(column1, column2, columnN) or optionally REMOVEFILTERS()

Steps:

  1. Create a visualization that groups [Internet Sales Amount] by Day and Color, filtered down to a single Month.

Sales Amount grouped by Reporting Day, Color, and Order Number

  1. Add a calculation that excludes the filtering on the OrderNumber and Line Item columns:
salesExcludeOrderIDGroupingALL = CALCULATE([Internet Sales Amount Local], ALL('Internet Sales Order Details'[Order Number], 'Internet Sales Order Details'[Item Description]))
  1. Add a calculation that divides the [Internet Sales Amount Local] measure by the sales amount (excluding the OrderNumber and Line Item filters), and formats the result as a percentage:
salesPctOfDisplayParent = [Internet Sales Amount Local] / [salesExcludeOrderIDGroupingALL]

 

The percentage of the visible parent displays.


Sales Amount, salesExcludeOrderIDGroupingALL, and salesPctOfDisplayParent grouped by Reporting Day, Color, Order Number, and Item Description

  1. Update the display and replace Color with Customer[country2].
    The expression operates the same way regardless of the grouping dimensions used.


Sales Amount, salesExcludeOrderIDGroupingALL, and salesPctOfDisplayParent grouped by Reporting Day, Country, and Order Number

  1. Try variations using REMOVEFILTERS instead of ALL(columnN, …).
    1. REMOVEFILTERS:
salesExcludeOrderIDGroupingREMOVEFILTER = CALCULATE([Internet Sales Amount Local], REMOVEFILTERS('Internet Sales Order Details'[Order Number], 'Internet Sales Order Details'[Item Description]))

Fine Grain Filter Expressions

Example Scenario: Create an expression that returns sales of a specific subset of Products identified by a combination of product characteristics: Category, Color, and Class.

Note: If you are interested in filtering by more than one dimension, see Filtering Measures by Two Different Dimensions.

Key Functions:

  • FILTER
  • && ("and" operator)
  • || ("or" operator)

Steps:

  1. Create a visualization that groups [Internet Sales Amount] by 'Product'[Category],   'Product'[Color], 'Product'[Class], and Order Number.


Sales Amount grouped by Class, Color, Product Category, and Order Number

  1. Add a calculation that filters on Product[Category], Product[Color], and Product[Class]:
targetProducts = CALCULATE ( [Internet Sales Amount Local], FILTER('Product', ('Product'[Category] = "Bikes" && 'Product'[Color] = "Black")  || 'Product'[Class]="High"))
The Sales amount for only targeted products displays.


Sales Amount, targetProducts grouped by Class, Color, Product Category, and Order Number

Fine Grain Filter Expressions (Variation)

Key Functions:

  • FILTER
  • AND
  • OR

Note: If you are interested in filtering by more than one dimension, see Filtering Measures by Two Different Dimensions.

Steps:

  1. Create a visualization that groups [Internet Sales Amount] by 'Product'[Category],   'Product'[Color], 'Product'[Class], and Order Number.

Sales Amount grouped by Class, Color, Product Category, and Order Number

  1. Add a calculation that filters on Product[Category], Product[Color], and Product[Class]:
targetProducts = CALCULATE ( [Internet Sales Amount Local], FILTER('Product', OR((AND('Product'[Category] = "Bikes",'Product'[Color] = "Black")),'Product'[Class]="High")))
The Sales amount for only targeted products displays.


Sales Amount, targetProducts grouped by Class, Color, Product Category, and Order Number

FILTER: Dimension Value IN Set

Example Scenario: Create a report that calculates the Sales for a specific set of Colors: Red, Black, and Blue.

 

Key Functions:

  • IN

Steps:

  1. Create a table with Sales Amount grouped by Color.


Sales Amount grouped by Color

  1. Create a calculated measure with the following formula:
ColorIN = CALCULATE([Internet Sales Amount Local],'Product'[Color] in {"Red", "Black", "Blue"})
  1. Update the display to include the newly created measure.
    The sales in the newly created measure are limited to the specified colors.


Sales Amount and ColorIN grouped by Color

  1. Change the displayed table to include Style.
    The total for promotional products remains unchanged.


Sales Amount and PromotionalProducts grouped by Style

Subquery Aggregation Using SUMMARIZE and FILTER

Example Scenario: Compute the fraction of sales revenue that comes from small, multi-quantity orders. Orders are “multi-quantity" if they have an order quantity > 1, and are considered “small” if the total order size is less than $50.

 

Key Functions:

  • SUMMARIZE
  • FILTER

Note: Be aware of the following:

  • AtScale automatically determines the correct source tables and join paths needed to satisfy the SUMMARIZE function based on the measure and dimension arguments passed to it. Because the resulting query can span many tables, AtScale recommends using the term "CubeMeasures" as the SUMMARIZE table argument to avoid any confusion regarding source table selection.
  • AtScale does not support nested SUMMARIZE calls.

Steps:

  1. Create a visualization that groups [Internet Sales Amount] and [Internet Order Count] by Order Number. 


Sales Amount and Internet Order Count grouped by Order Number

  1. Add an expression named SmallMultiQuantityOrders that does the following:
    1. Uses SUMMARIZE to aggregate the implicit AtScale model measures 'CubeMeasures'[Internet Sales Amount Local] (named orderSalesAmount) and 'CubeMeasures'[Order Quantity Long] (named IOQ) over the model's dimensional attribute 'Internet Sales Order Details'[Order Number]. 
    2. Uses the FILTER function to restrict the results to only those rows where [IOQ] > 1 and [orderSalesAmount] < 50.  This result set represents small, multi-quantity orders.  
    3. Uses the CALCULATE function to return the SUM of the '[Internet Sales Amount Local]' measure as a scalar value that is constrained by the filter table tab1 (built using the SUMMARIZE and FILTER functions).

SmallMultiQuantityOrders = 

VAR tab0 = SUMMARIZE(‘CubeMeasures’, 'Internet Sales Order Details'[Order Number], "orderSalesAmount", 'CubeMeasures'[Internet Sales Amount Local], "IOQ", 'CubeMeasures'[Order Quantity Long])

VAR tab1 = FILTER(tab0, AND([IOQ] > 1, [orderSalesAmount] < 50))

VAR sumTab0 = CALCULATE(SUM(CubeMeasures'[Internet Sales Amount Local]), tab1)

RETURN sumTab0

  1. Add a card visualization to display SmallMultiQuantityOrders. 
  2. To confirm that the SmallMultiQuantityOrders amount displayed in the card is correct: 
    1. Add a table to display Order Number, Sales Amount, and Order Count. 
    2. Apply a visualization filter to constrain the results to rows having [Order Quantity Long] > 1 and [Internet Sales Amount Local] < 50. 

The [Internet Sales Amount Local] Grand Total matches the SmallMultiQuantityOrders expression amount displayed in the card.


Sales Amount and Internet Order Count grouped by Order Number, filtering out orders with less than $50 in sales

Logic Based on Calculated Columns

Example Scenario: Compute the fraction of sales that comes from female clients with certain occupations: Management or Professional. Use ADDCOLUMNS so the records are flagged in an entirely new column.

 

Key Functions:

  • ADDCOLUMNS
  • IF
  • FILTER

Steps:

  1. Create a visualization that groups [Internet Sales Amount] by 'Customer'[Gender] and 'Customer'[Occupation].


Sales Amount grouped by Gender and Occupation

  1. Add a variable called HighSkilledFemaleClients that is computed as follows:
    1. Use the ADDCOLUMNS function to add a calculated column called High Profile Customers to the query. The new column's values are based on the 'Customer' dimension attribute values. The values will be either Highly Skilled or Not Interested, depending on the Customer's Occupation.  
    2. Use the FILTER function to restrict the rows by High Profile Customers and Gender criteria.
    3. Use the CALCULATE function to select the intrinsic measure [Internet Sales Amount Local] from the model, given the FILTER constraints defined in variable "y".

HighSkilledFemaleClients = 

               var x = ADDCOLUMNS('Customer', "High Profile Customers", if('Customer'[Occupation] = "Management" || 'Customer'[Occupation] = "Professional", "Highly Skilled", "Not Interested")) 

               var y = FILTER(x, and([High Profile Customers] = "Highly Skilled", 'Customer'[Gender] = "Female")) 

               return CALCULATE([Internet Sales Amount Local], y)

 

Sales Amount isolated to specific Gender and Occupation, compared to the entire sales share

  1. Substitute the Occupation and Gender with Customer Country.
    Note the change in distribution between Sales Amounts. The calculation functions the same way, irrespective of the columns in the visualization.


Sales as well as Sales isolated to a specific Gender and Occupation, distributed by Customer Country

Aggregation Filtering with CALCULATETABLE

Example Scenario: Create an expression that returns a Product Color and Style contribution to the total of the displayed grouping dimensions. 

This calculation should work with whatever dimensions you include in the visualization, without having to modify the expression. For example, compute the fraction of sales that comes from products of a certain Color and Style that have been advertised as on sale. If you remove Color from the display, the same calculation should show the sales with another dimension in place.

 

Key Functions:

  • CALCULATETABLE
  • SUMMARIZE

Steps:

  1. Create a visualization that groups the Sales Amount by product color and style.


Sales Amount grouped by color and style

  1. Create a calculated measure using the following formula:

PromotionalSales = 

               var tab1 = CALCULATETABLE(SUMMARIZE('CubeMeasures', 'Product'[Style], 'Product'[Color], "ISA", 'CubeMeasures'[Internet Sales Amount Local]),'Product'[Style] in {"Unisex", "Womens"}, 'Product'[Color] <> "Black") return CALCULATE([Internet Sales Amount Local], tab1)

The calculated measure displays the sales contribution only from the products of certain colors and styles that are part of a campaign, for example. 
  1. Add the newly created measure to the report.


Sales amount grouped by color and style, compared to sales from all products

  1. Observe the change in the report once the dimension in the report is changed from Color to Customer Country.


Sales amount grouped by Customer Country versus the sales by country from promotional products only

Non Blank Value Distribution

Example Scenario: Compute the number of products sold by Color and Style, and compare that to the amount of Sales they are contributing.

 

Key Functions:

  • COUNT
  • DISTINCTCOUNT

Steps:

  1. Create a visualization that groups [Internet Sales Amount] by 'Product' [Color] and 'Product'[Style].


Sales Amount grouped by Color and Style

  1. Create a calculated measure that counts the Product Dimension Rows.
Product Quantities = COUNT('Product' [Product])

 

Sales Amount grouped by Color and Style, along with the products offered in those categories

  1. Create a visualization that groups [Reseller Order Count] by 'Reseller Type'[Business Type] and 'Product Categories'[Category].


Reseller Order Count grouped by Product Categories and Reseller Business Type, along with the products offered in those categories

  1. Create a calculated measure that shows the distinct number of products, grouped by the selected dimensions.
Distinct Products = DISTINCTCOUNT('Product'[Product])

 


Comparison between the Reseller Order Count and the distinct number of products grouped by Product Categories and Reseller Business Type, along with the products offered in those categories

TOPN by a Related Dimension

Example Scenario: Compute the top performing product style across various years.

 

Steps:

  1. Create a visualization that groups the Sales Amount by Year.
  2. Create a calculated measure named TopStyleSales that uses the TOPN function as a CALCULATE filter table to identify the top selling product style:
    1. Use the SUMMARIZE function to compute the 'CubeMeasures'[Internet Sales Amount Local] (named ISA) by dimension attribute 'Product'[Style] from the AtScale model.
    2. Use the TOPN function to sort the results in descending order by the ISA column and restrict the result to the top 1 rows. 
    3. Use the CALCULATE function to compute the [Internet Sales Amount Local] intrinsic measure from the model using the TOPN results, referenced by the variable tab1, as a filter table argument.
    4. In the table part of the TOPN function (tab0 in this example), use any DAX expression that returns a table of data from which the TOPN rows can be extracted.

TopStyleSales =

var tab0 = SUMMARIZE('CubeMeasures', 'Product'[Style], "ISA", 'CubeMeasures'[Internet Sales Amount Local]) 

var tab1 = TOPN(1,tab0,[ISA],DESC) 

var sumtab0 = CALCULATE([Internet Sales Amount Local], tab1) return sumtab0

  1. Create a calculated measure named TopSellingStyle that identifies the top performing product style:
    1. Use the SUMMARIZE and TOPN functions as described in the previous step to build up a filter table.
    2. Use the CALCULATE function to retrieve the 'Product'[Style] attribute that corresponds with the top sales amount retrieved in the previous example.

TopSellingStyle = 

var tab0 = SUMMARIZE('CubeMeasures', 'Product'[Style], "ISA", 'CubeMeasures'[Internet Sales Amount Local]) 

var tab1 = TOPN(1,tab0,[ISA],DESC) 

var sumtab0 = CALCULATE('Product'[Style], tab1) return sumtab0

  1. Create a table report with Calendar Year, Internet Sales, TopSellingStyle, and TopStyleProducts.


Sales amount for the Top Selling product Style. Comparing the Internet Sales Amounts in total, as well as those for the highest selling Product Style by calendar year, indicates that the Unisex Style was the top selling style for all years.

 

Note: TOPN is not supported as an iterator function table argument. Using it as such might lead to unexpected results.

Keeping Filter and Grouping Columns from the Filter Context

Example Scenario: Create a generic expression that returns a Country's contribution to the total of the displayed grouping dimensions. In this example, you will add to the user-created filters, rather than override them. 

This calculation should work with whatever dimensions you include in the visualization, without having to modify the expression. For example, when grouping the [Internet Sales Amount Local] measure by Customer Country and Gender, the calculation should show the contribution to the Gender and Customer Country's subtotal. If you remove Customer Country from the display, the calculation should show the sales' contribution to the Gender's subtotal. 

 

Key Functions:

  • CALCULATE
  • KEEPFILTERS

Steps:

  1. Create a visualization that groups the [Internet Sales Amount] by Color.


Sales Amount grouped by Color

  1. Create a calculated field with the following formula:
BlackOrSilverProducts = CALCULATE([Internet Sales Amount Local],KEEPFILTERS('Product'[Color] IN{"Black", "Silver"}))
  1. Drag the newly created measure into the visualization.
    The newly created measure displays values only for certain colors, even though there are no filters applied in the visualization.


Sales Amount and the newly created measure grouped by Color

  1. Add the Product Color to the Filters pane and select Black and Red.
    The newly created measure works in conjunction with the filter from the formula created above to display values for only one color.


Sales Amount and the newly created measure grouped and filtered by a Color

 

Note: When using the KEEPFILTERS function, be aware of the following:

  • When using KEEPFILTERS to refer to two or more conditions, you should wrap them in separate KEEPFILTERS calls:
CALCULATE([Internet Order Count],KEEPFILTERS('Product'[Class]="High"),KEEPFILTERS('Product'[Style]="Unisex"))
  • In AtScale I2023.4.1, the KEEPFILTERS function can only be used with existing cube measures. Using calculations as a first argument within a CALCULATE call, with KEEPFILTERS as a filter modifier, will return unexpected results. 
  • AtScale does not support the use of KEEPFILTERS with iterator functions such as SUMX, MINX, MAXX, etc.

Pre Aggregation Filter Calculation

Example Scenario: Create a generic expression that computes the fraction of sales that are less than $100,000 and come from certain colored and style products. This calculation should work with whatever dimensions you include in the visualization, without having to modify the expression.  

 

Key Functions:

  • SUMMARIZE
  • CALCULATETABLE
  • FILTER

Steps:

  1. Create a table containing [Internet Sales Amount] grouped by Product[Style] and Product[Color].

Sales amount grouped by Style and Color

  1. Create a new measure with the following expression:

Products = var tab0 = SUMMARIZE('Product', 'Product'[Style], 'Product'[Color], "ISA", 'CubeMeasures'[Internet Sales Amount Local])

var tab1 = FILTER(tab0, [ISA] < 100000)

var tab2 = CALCULATETABLE(tab1, 'Product'[Style] in {"Unisex", "Womens"}, 'Product'[Color] <> "Black")

return CALCULATE('CubeMeasures'[Internet Sales Amount Local], tab2)

  1. Filter the table as follows:
    • Color is not equal to Black
    • Style is either Unisex or Womens
    • Internet Sales Amount is less than 100000


Sales Amount grouped by Style and Color, filtered

  1. On the same sheet, add a card visualization and drag the newly created measure to it.
    The value matches the manually filtered figure exactly.

Sales Amount grouped by Style and Color and filtered, along with the newly created measure

Switch on a Discrete Dimension Value

Example Scenario: Create a generic expression that ranks sales by a user-defined custom grouping. The rank could be used for custom sorting and/or augmenting visualization behavior. 

 

Key Functions:

  • SWITCH
  • ADDCOLUMNS
  • SUMMARIZE
  • MAXX

Important: As of AtScale I2023.4.1, this scenario does not work on BigQuery or Redshift data warehouses due to dialect-specific issues with the SWITCH statement. 

 

Steps:

  1. Create a visualization summarizing the Sales Amount by Product Style.


Internet Sales Amount by Product Style

  1. Create a calculated measure with the following formula:

StyleRank = VAR tab0 = ADDCOLUMNS(SUMMARIZE(CubeMeasures, 'Product'[Style]), "StylePriority", SWITCH('Product'[Style], "Womens", 1, "Unisex", 2,  "Not Applicable", 3, 0))

return MAXX(tab0, [StylePriority])

  1. Add the newly created measure to the visualization and sort by rank.


Internet Sales Amount by Product Style, sorted by user-defined rank

Switch on Floating Point Dimension Attribute to Create Text Labels

Example Scenario: Create an expression that groups the dealer price into pre-defined buckets and labels each product accordingly.

 

Important: The dimensional attribute must be selected. If not, the dimension is treated as null.

 

Key Functions: 

  • SWITCH

Steps:

  1. Create a visualization that consists of the Product Categories hierarchy and [Dealer Price].


Dealer Price by Product Categories

  1. Create a calculated measure that separates the dealer price into the desired bands:
DealerBands = SWITCH(TRUE, Product[Dealer Price] >= 50, "50+", Product[Dealer Price] >= 40, "40-49", Product[Dealer Price] >= 30, "30-39", Product[Dealer Price] >= 20, "20-29", "<20")
  1. Filter the Category to Clothing and the Subcategory to Caps, Gloves, Jerseys, or Tights.
  2. Add the measure created above to the visualization.


Dealer Price by Product Categories, filtered and labeled

Replacing Cascading IF statements

Example Scenario: Create an expression that allows you to calculate the discount on each product. 

 

Key Functions:

  • SWITCH

Steps:

  1. Create a table containing [Internet Order Count] and [Reseller Order Count], grouped by Product.
  2. Filter the product Category to Accessories.


Reseller and Internet Order Count, grouped by Product and filtered by Category

  1. Create a calculated field with the following formula:
Discount = SWITCH(TRUE,[Reseller Order Count]>[Internet Order Count],0.15,[Reseller Order Count]=[Internet Order Count],0.1,[Reseller Order Count]<[Internet Order Count],0.08,0)
  1. Add the newly created measure to the visualization.
    The discount is calculated based on the dimension in the visualization.


Reseller and Internet Order Count, grouped by Product and filtered by Category, along with the Discount

 

Note: When using the SWITCH function, be aware of the following:

  • If no value is satisfied and no <else> expression is given, a blank value is returned.
  • Expressions are evaluated for each filter context row.
  • All <result> and <else> values must return the same data type.
  • Expressions are evaluated in order: the first matching value returns its result and no subsequent expressions are evaluated.
  • There is no implied aggregation when evaluating hierarchy levels.

Multiplying Column Values with the PRODUCT Function

Example Scenario: Create a generic expression that returns the product of sales from specific customers. 

 

Key Functions:

  • PRODUCT
  • FILTER
  • && ("and" operator)
  • || ("or" operator)

Steps:

  1. Create a table that groups [Internet Sales Amount] by Customer Geography.


Sales Amount grouped by Customer Geography

  1. Create a calculated measure with the following expression:
Filtered Clients = CALCULATE(PRODUCT(CubeMeasures[Internet Sales Amount Local]),FILTER('Customer',('Customer'[Customer Country]="Germany" && 'Customer'[Customer State-Province] = "Brandenburg" && ('Customer'[CustomerFullName] = "Bruce D. Srini" || 'Customer'[CustomerFullName] ="Frederick . Patel"))))
  1. In the visualization, apply the following filters: 
    • Customer is either Bruce D. Srini or Frederick . Patel
    • Customer Country is Germany
    • Customer State-Province is Brandenburg


Sales Amount grouped by Customer Geography, filtered down to specific customers

  1. Add a card visualization with the newly created metric.
    The calculated measure result exactly matches the product of the two lines in the table on the left.
    Note: Failing to apply all filters will result in an error.

 

Note: Be aware of the following:

  • TOPN is not supported as a table argument within iterator functions. Using it as such might lead to unexpected results.
  • AtScale only supports the use of the PRODUCT function with measure columns. Using it with dimensional attribute columns is not supported and will return unexpected results.

SUMMING a Table Expression with SUMX

Example Scenario: Create a ratio to illustrate the sales amount that could be contributed to the order quantity, on both the individual and the grand total levels.

This example uses pre-filtering to lower the data quantity. The point here is to illustrate the difference between the aggregation of values computed for each row and an aggregated expression computed at the grand total level. 

 

Key Functions:

  • SUMX

Steps:

  1. Create a table that does the following:
    • Groups [Internet Sales Amount] and [Order Quantity Long] by Color, Reporting Day, Order Number, and Item Description. 
    • Filters the June 2007 Reporting Month from the Retail 445 Hierarchy.


Sales amount and Order quantity grouped by Color, Reporting Day, Order Number, and Item Description

  1. Create the following calculated measures:
RatioSUMX = SUMX(CubeMeasures, 'CubeMeasures'[Order Quantity Long] / 'CubeMeasures'[Internet Sales Amount Local])

 

RatioSUM = SUM('CubeMeasures'[Order Quantity Long]) / SUM('CubeMeasures'[Internet Sales Amount Local])
  1. Add the newly created metrics to the table.

Note that despite the seemingly matching values on the row level, the main difference in the aggregation of values comes from using an aggregated expression evaluated on the Grand Total Level (RatioSUM) vs. an aggregation computed for each row (RatioSUMX).


Sales amount and order quantity grouped by Color, Reporting Day, Order Number, and Item Description, along with the aggregation measures calculated for each level vs. the aggregated expression evaluated at the grand total level

 

Note: Be aware of the following:

  • Measures from different fact tables or unrelated dimensions cannot be aggregated together.
  • Calculated measures are not allowed.

Finding the Minimum Table Expression Value with MINX/MAXX

Example Scenario: Create a generic expression that returns the minimum amount of sales by an individual customer for various education levels.

 

Key Functions:

  • MINX
  • MAXX
  • CALCULATE

Steps:

  1. Create a table that groups [Internet Sales Amount] and [Internet Order Count] by Customer Full Name.


Sales amount and Internet Order Count grouped by Customer Full Name

  1. Create the following calculated measure:

MinimumSales = CALCULATE(MINX(

           SUMMARIZE(VALUES('Customer'), 'Customer'[CustomerFullName], 'Customer'[Education])

         ,

         DIVIDE('CubeMeasures'[Internet Sales Amount Local],'CubeMeasures'[Internet Order Count])

))

 

MaxSales = CALCULATE(MAXX(

           SUMMARIZE(VALUES('Customer'), 'Customer'[CustomerFullName], 'Customer'[Education])

         ,

         DIVIDE('CubeMeasures'[Internet Sales Amount Local],'CubeMeasures'[Internet Order Count])

))

  1. Create a table with Education and the newly created measure.


Minimum and maximum sales amount grouped by Education

The newly created measure matches the minimum/maximum sales per education in our initial customer report.


Comparison of the sales amount to the minimum sales amounts by Education

Comparison of the sales amount to the maximum sales amounts by Education

  1. Manually filter both reports to Customer Country Australia.


Comparison of the sales amount to the minimum sales amounts by Education, filtered for Australia

Comparison of the sales amount to the maximum sales amounts by Education, filtered for Australia

Calculating Ranks with RANKX

Example Scenario: Create a report that computes a rank metric for each Product Color by sales efficiency. Sales efficiency is defined as the ratio of Sales Amount per Order Quantity Sold. 

Although such a report could be created by simply using the sort feature in the UI, it is useful to create a Rank Metric because it decouples the calculation from the UI, allowing you to display and sort by other values. 

 

Key Functions:

  • RANKX
  • CALCULATE

Steps:

  1. Create a table that groups [Internet Sales Amount] and [Order Quantity Long] by Product[Color].


Sales and Order Quantity grouped by Product Color

  1. Create the following calculated measure:
COLORRank = RANKX(ALL('Product'[Color],'Product'[Color.SortKey0],'Product'[Color.Key0]),DIVIDE([Internet Sales Amount Local],[Order Quantity Long]),,DESC,Dense)
  1. Add the newly created measure to the visualization.
    It is now possible to see the Rank of Product Color as a metric without manually sorting on the metric in the UI.


Sales and Order quantity grouped by Product Color, ranked

 

Note: It is recommended to always use the DENSE argument to explicitly communicate how AtScale should handle ties.

 

Note: Be aware of the following:

  • In AtScale I2023.4.1, the system throws an exception if you group by attributes that are not included in the RANKX expression.
  • It will also display NULL values for the Grand Total of the calculated measure. 
  • You cannot reference an entire table within the function call — you need to specify the columns of interest.
  • The report writer must include all column keys in the calculated measure.
    For example, the following will not work:
RANKX(ALL(Customer[Customer City]), [Internet Sales Amount Local], , ASC)

A valid syntax within AtScale would be:

RANKX(ALL(Customer[Customer City], Customer[Customer City.Key1],Customer[Customer City.Key0]), [Internet Sales Amount Local], , ASC)

If there is a sort key defined, you must include that, as well.  For an example, see the COLORRank expression in the procedure above.

  • Additional dimensional attributes added to the visualization containing a RANKX calculation, which are not part of it, will fail.

Appendix A: Conventions

Related Tables

You do not need to join-in related tables, as AtScale does this automatically. For example, in a Tabular model, you filter a "Measure Table" by a related dimension table as follows:

FILTER('InternetSales_USD', RELATED('SalesTerritory'[SalesTerritoryCountry])<>"United States")

 

However, in AtScale, you do not need to include the RELATED call:

FILTER('InternetSales_USD', 'SalesTerritory'[SalesTerritoryCountry]<>"United States")

 

Filtering Measures by Two Different Dimensions

In AtScale, you filter measures by two different dimensions using separate, dedicated FILTER function calls. For example:

HighClassProducts2006 =

CALCULATE([Internet Sales Amount Local],FILTER(ALL('Product'),'Product'[Class] = "High"),FILTER(ALL('DateCustom'),'DateCustom'[Year] = "Calendar 2006"))

 

Example Scenario: Create a metric that shows the sales for "High" Class products in Year "2006". The computation of this metric requires combining filters on both the 'Product' and 'DateCustom' dimensions. Because the filters are on different dimensions, separate calls to FILTER must be used — one per dimension.

 

Steps:

  1. Create a visualization that groups the Sales Amount by Class and Year.
  2. Create a calculated measure that isolates the sales for products of class High during Calendar Year 2006.

HighClassProducts2006 =

CALCULATE([Internet Sales Amount Local],FILTER(ALL('Product'),'Product'[Class] = "High"),FILTER(ALL('DateCustom'),'DateCustom'[Year] = "Calendar 2006"))

  1. Add a card visualization and drag the newly created measure to it.



Appendix B: Supported DAX Language Elements

AtScale supports the following DAX language elements. For more information, see the AtScale Documentation.

Functions

Aggregation Functions 

  • AVERAGE
  • COUNT
  • COUNTX
  • DISTINCTCOUNT
  • MAX
  • MAXX
  • MIN
  • MINX
  • PRODUCTX
  • SUM
  • SUMX

Date and Time Functions 

  • DATE
  • DAY
  • HOUR
  • MINUTE
  • MONTH
  • QUARTER
  • SECOND
  • TIME
  • TODAY
  • UTCNOW
  • YEAR

Filter Functions 

  • ALL
  • ALLEXCEPT**
  • BLANK
  • CALCULATE
  • CALCULATETABLE
  • FILTER
  • ISBLANK
  • REMOVEFILTERS
  • KEEPFILTERS*

*KEEPFILTERS does not work with iterator functions.

**ALLEXCEPT only works on secondary attributes.

Logical Functions 

  • AND
  • COALESCE
  • FALSE
  • IF
  • IFERROR
  • ISEMPTY
  • ISSUBTOTAL
  • NOT
  • OR
  • SWITCH
  • TRUE

Math and Trig Functions 

  • ABS
  • ACOS, ACOSH, ACOT, ACOTH, ASIN, ASINH, ATAN, ATANH, COS, COSH, COT, COTH, SIN, SINH, TAN, TANH
  • CONVERT
  • CURRENCY
  • DEGREES, RADIANS
  • INT
  • LOG, LOG10, LN
  • MOD
  • ODD
  • PI, SQRT, DIVIDE
  • POWER, EXP
  • QUOTIENT
  • RAND, RANDBETWEEN
  • ROUND, CEILING, FLOOR, ISO.CEILING
  • SIGN
  • SQRTPI
  • TRUNC

Table Manipulation Functions 

  • ADDCOLUMNS
  • SUMMARIZE
  • TOPN

Text Functions 

  • CONCATENATE
  • EXACT
  • FORMAT
  • LEFT
  • LEN
  • LOWER
  • MID
  • RIGHT
  • SEARCH
  • SUBSTITUTE
  • TRIM
  • UPPER
  • VALUE

Statistical Functions 

  • RANKX

Statements

  • DEFINE
  • EVALUATE
  • MEASURE
  • ORDER BY
  • VAR

Operators

  • Arithmetic: + - / * () ^
  • Logical: IN
  • Comparison: = <> < > <= >=

Was this article helpful?

3 out of 3 found this helpful