From DAX to Honeydew

​

Introduction

Honeydew is a standalone semantic layer. It has both similarities and differences to a Power BI Semantic Model.

At their core, the differences are due to the fact that Honeydew is designed as a SQL-based semantic layer, while a Semantic Model inherits its architecture from multi-dimensional cubes.

It is possible to map most concepts of a Semantic Model to corresponding concepts in Honeydew.

​

Semantic Model

A Semantic Model in Power BI groups together tables, relationships, calculated columns and measures.

Multiple Semantic Models can operate on the same data, each with its own logic - some of it might be duplicated.

Honeydew has a different approach:

1. A global semantic model defined in a Honeydew workspace covers multiple business use cases at once.
2. A Honeydew Domain is a curated subset of the global model to serve for a specific business domain or use case.

When Honeydew is connected to a BI tool (whether it is Power BI or any other tool), a connection can be created per domain.

​

Tables

A table in a Power BI semantic model corresponds to an Entity in Honeydew.

Entities can be based on a table or a view in the data warehouse, or on a custom SQL query.

​

Relationships

Tables in Power BI have relationships to other tables. Relationships between entities in Honeydew follow a similar structure.

• Join fields: a Semantic Model requires a single field for a join connection. It is common to create calculated composite keys for cases when a connection is done over multiple fields. Honeydew allows to use multiple fields and/or calculated attributes.

• Cardinality:

  • One-to-many and many-to-one relationships operate the same way in Honeydew as they are in a Semantic Model.
  • Many-to-many cardinality connection requires a connecting table.

• Cross-filtering and Referential Integrity:

  • Cross filtering (one-directional and bi-directional) works in Honeydew in a similar way. Honeydew also allows to disable cross-filtering entirely.
  • Referential Integrity is represented in Honeydew by the join type of the relationship (LEFT or INNER). Honeydew also supports other join types (RIGHT and FULL OUTER).
  • See Converting Cross Filtering from Power BI for more details.

• Active Relations: Honeydew does not have a concept of an “active relationship”. All relations are active, and multiple relations between two same entities are not supported. Role-playing dimensions can be created by copying an entity with an alternative relationship.

​

Calculated Tables

Calculated tables in Power BI semantic models are tables that are built with DAX expressions.

In Honeydew, calculated tables are typically built with SQL, and are mapped to entities based on a custom SQL query.

For example, a DAX table such as

Inventory = UNION(UsaInventory, IndInventory)

Would correspond to a Honeydew entity called inventory with custom SQL

SELECT * FROM usa_inventory
UNION ALL
SELECT * FROM ind_inventory

​

DAX Calculated Columns

A calculated column in a DAX table corresponds to a calculated attribute in a Honeydew entity.

A DAX expression such as

Sales[GrossMargin] = Sales[SalesAmount] - Sales[TotalProductCost]

Would correspond in Honeydew to

sales.gross_margin = sales.sales_amount - sales.total_product_cost

​

Order of filtering

Calculated columns in Power BI are calculated during data refresh so they don’t take into account any slicers or filters.

Calculated attributes in Honeydew operate similarly, being calculated prior to the application of any user or query filters.

The only exception is domain source filters which can filter data before any calculated attribute.

​

Related tables in calculated columns

DAX allows the use of related columns in a calculated column expression using the RELATED function:

Sales[Discount] = RELATED ( Product[Unit Price] ) - Sales[Net Price]

The equivalent in Honeydew does not need to explicitly say RELATED:

sales.discount = product.unit_price - sales.net_price

​

Using metrics in calculated columns

Honeydew enables the use of metrics as part of calculated columns:

-- total sales is a metric in sales
customer.total_sales = sales.total_sales

This expression calculates the total sales per customer, and stores that as a calculated attribute in the customer entity.

​

DAX Measures

A measure in a DAX table is a way to aggregate rows over a table. It corresponds to a metric in a Honeydew entity.

​

Basic Measures

A DAX expression such as

Sales[Gross Margin %] := DIVIDE ( SUM ( Sales[GrossMargin] ),  SUM (Sales[SalesAmount] ) )

Would correspond in Honeydew to

sales.gross_margin_percentage = DIV0(SUM(sales.gross_margin), SUM(sales.sales_amount))

​

Row Context

A row context in DAX defines the rows for an iterator to go over.

For example, a SUM in DAX aggregates over the rows of a table:

 Sales[SumQuantity] := SUM ( Sales[Quantity] )

SUM in DAX operates on a single column only. A more complex expression that aggregates over the rows of a table in DAX might look as

Sales[Amount] := SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )

Here SUMX provides a “Row Context” (the table Sales) for the following expression:

• The row context is Sales , meaning iterate over each row of Sales
• The expression can access any column of the row - so it can calculate Sales[Quantity] * Sales[Net Price]
• SUMX then aggregates the result with a sum.

In Honeydew, the entity defined for a metric provides its “row context”. All metric expressions in an entity iterate over all the entity rows. The equivalent in Honeydew of the previous measure is the metric:

 sales.amount = SUM(sales.quantity * sales.net_price)

​

Filtering the row context

When operating in DAX in a row context, it is common to apply filters to remove rows from an aggregation using the DAX FILTER function.

For example, consider the following statement:

Sales[TotalEMEAQuantity] := SUMX( FILTER(Sales, Sales[Region]="EMEA"), Sales[Quantity] )

The equivalent in Honeydew is called a filtered metric:

sales.total_emea_quantity = SUM(sales.quantity) FILTER (WHERE sales.region = 'EMEA')

The Honeydew FILTER metric qualifier reduces rows from the entity context of the metric.

​

Related tables in the row context

Many DAX measures need to reference more than one table.

SUMX (
    FILTER (
        Sales,
        RELATED(Customer[Age]) >= 20
    ),
    IF (
      RELATED(Customer[Age] <= 45),
      Sales[Quantity] * Sales[Net Price],
      Sales[Quantity] * Sales[Unit Price]
)

Relationships in Honeydew are automatic. The equivalent metric would reside in the sales entity and its expression would be:

SUM(
	CASE WHEN customer.age <= 45
		THEN sales.quantity * sales.net_price
		ELSE sales.quantity * sales.unit_price
	END
)
FILTER (WHERE customers.age >= 20)

​

Stacked Row Context

DAX enables to stack row contexts for ad-hoc joins between tables. For example, in the statement below:

1. Outer SUMX defines an iterator per row of customer
2. Inner SUMX iterates per row of customers, per row of sales

SUMX (
    FILTER (
        Customer,
        Customer[Country] = "United States"
    ),
    SUMX (
        FILTER (
            Sales,
            Sales[CustomerKey] = Customer[CustomerKey] &&
            Customer[Age] >= 20
        ),
        IF (
            Customer[Age] <= 45,
            Sales[Quantity] * Sales[Net Price],
            Sales[Quantity] * Sales[Unit Price]
        )
    )
)

Honeydew does not have a direct equivalent for this statement.

To create this statement, define a relationship between Customer and Sales entities and then use it in the right context (as a metric in sales):

SUM(
    CASE WHEN customer.age <= 45
        THEN sales.quantity * sales.net_price
        ELSE sales.quantity * sales.unit_price
    END
)
FILTER (WHERE customer.country = 'United States' and customers.age >= 20)

​

Filter Context

Filter context in DAX sets the currently active filter when evaluating an expression.

For example, the following expression:

Sales Amount := SUMX (Sales, Sales[Quantity] * Sales[Net Price])

by default evaluates over the entire sales table.

However, if a user slices the data in a widget by Product[Brand], the formula computes Sales Amount per each product brand. In other words, Power BI filters only a specific brand in each evaluation.

If a user creates a matrix to pivot by Product[Brand] and Date[Year], then each calculation filters out only a specific year and brand. Every cell in a matrix by year and brand has a different filter context to apply to the evaluation of sales amount: the year and brand relevant to that cell.

The filtering applied to the table on which the measure is calculated in DAX is referred to as the Filter Context.

The CALCULATE function in DAX allows to control the filter context within a measure context.

​

Differences between DAX and SQL

The biggest difference between DAX and SQL/Honeydew is how the filter context behaves.

Filter context in DAX combines both SQL grouping and SQL filtering.

For people versed in SQL, the equivalence of SQL GROUP BY and WHERE to a DAX filter context might be confusing at first.

Consider the following SQL statement:

SELECT SUM(price) as total_price
GROUP BY brand
WHERE year = 2019

The data is filtered to only rows in 2019, then grouped by year, and each cell of total_price is calculated then only for rows of a brand in 2019.

In DAX both those actions (filtering the rows in a group, and filtering all the rows before grouping) are part of the “filter context”, as both filter the data before calculating a total price cell.

​

Adding filters

Some measures require to add filtering on the table used in the aggregation.

In SQL terms, that would mean adding a WHERE condition without changing existing grouping or SQL filtering.

This expression adds a filter on the color of a product in addition to any existing filters

RedSalesKeepFilters := CALCULATE (
    [Sales Amount],
    KEEPFILTERS ( 'Product'[Color] = "Red" )
)

or when aggregating directly instead of using a [Sales Amount] measure:

RedSalesKeepFilters := CALCULATE (
    SUM('Sales'[Amount]),
    KEEPFILTERS ( 'Product'[Color] = "Red" )
)

Red Sales Table Filter :=
CALCULATE (
    [Sales Amount],
    FILTER (
        'Product',
        'Product'[Color] = "Red"
    )
)

The equivalent metric in Honeydew is:

red_sales_keep_filter = sales.sales_amount
	FILTER (WHERE product.color = 'Red')

Or when aggregating directly

red_sales_keep_filter = SUM(sales.amount)
	FILTER (WHERE product.color = 'Red')

The FILTER (WHERE ...) metric qualifier adds a SQL filter to a metric without changing SQL grouping.

In other words FILTER (WHERE ...) filters rows within the current group.

Multiple filters can be applied with an AND in the statement.

This DAX filtering expression

Red USA Sales :=
CALCULATE (
    [Sales Amount],
    KEEPFILTERS ('Product'[Color] = "Red"),
    KEEPFILTERS (Customer[Country] = "United States")
)

Is equivalent to the Honeydew metric:

red_usa_sales = sales.amount
FILTER (WHERE product.color = 'Red' AND customer.country = 'United States')

​

Changing filters

This DAX expression changes the filter on color to be red, ignoring any previous filters on color.

RedSales := CALCULATE (
    [Sales Amount],
     'Product'[Color] = "Red"
)

Red Sales :=
CALCULATE (
    [Sales Amount],
    FILTER (
        ALL ( 'Product'[Color] ),
        'Product'[Color] = "Red"
    )
)

In SQL or in Honeydew the CALCULATE expression can be translated to two possible actions:

1. If there was SQL grouping on color, remove the group when calculating the sales amount aggregation (i.e color is no longer part of the GROUP BY).
2. If there was no SQL grouping on color, remove any row (WHERE) filters on color, and filter color for red instead.

Both can be modeled, but the first one (removing grouping) is the common case.

The translation for the metric above that removes SQL grouping on color, but keeps SQL where filters:

-- Removes SQL group only (recommended, but not a direct equivalent to the DAX expression)
red_sales = sales.sales_amount
  FILTER (WHERE product.color = 'Red')
  GROUP BY (NOT product.color)

The GROUP BY (NOT product.color) qualifier instructs Honeydew to ignore that group when calculating the metric.

The direct equivalent to the DAX expression removes both SQL grouping and SQL where filtering:

-- directly equivalent metric: removes both SQL group and SQL filter
red_sales = sales.sales_amount
  FILTER (WHERE product.color = 'Red' EXCLUDE product.color)
  GROUP BY (NOT product.color)

Multiple filters can be combined using AND in the statement.

The following expression that changes filters on two tables

Red USA Sales :=
CALCULATE (
    [Sales Amount],
    ('Product'[Color] = "Red"),
    (Customer[Country] = "United States")
)

can be translated to the Honeydew metric:

red_usa_sales = sales.amount
FILTER (WHERE product.color = 'Red' AND customer.country = 'United States')
GROUP BY (NOT product.color, customer.country)

​

Filtering by an unrelated table

A common pattern in DAX is to use an unrelated table as a filter.

For example, this expression can take all the values selected in a category lookup table, and apply them as category filters.

VAR _SelectedCategories = ALLSELECTED('CategoryLookup'[Category])
RETURN CALCULATE([Sales Amount], 'Product'[Category] IN _SelectedCategories)

The equivalent in Honeydew uses the lookup filter functionality

sales.amount FILTER (WHERE product.category IN category_lookup.category)

Another example is copying a filter from one product table to another

CALCULATE(
    [Sales Amount],
    TREATAS(
        VALUES(DimProduct1[ProductCategory]),
        DimProduct2[ProductCategory]
    )
)

The equivalent in Honeydew with a lookup filter:

sales.amount FILTER (WHERE dim_product2.category IN dim_product1.category)

​

Using ALLEXCEPT

In DAX, ALLEXCEPT removes all context filters in the table except filters that have been applied to the specified columns.

This is applicable to scenarios such as calculating totals, ignoring user groupings:

YearlySales % :=
DIVIDE (
    [Sales Amount],
    CALCULATE (
        [Sales Amount],
        ALLEXCEPT ('Date', 'Date'[Year] )
    )
)

The equivalent in Honeydew is

yearly_sales = sales.amount /
  (sales.amount
  GROUP BY (date.year))

The GROUP BY (entity.field, ...) metric qualifier in Honeydew sets for the group to operate in - which would be equivalent to a DAX filter context that only applies that group.

​

Using ALLSELECTED(field)

In DAX, ALLSELECTED qualifier removes context filters from columns and rows in the current query, while retaining all other context filters or explicit filters.

Consider the following statement:

Sales % :=
DIVIDE (
    [Sales Amount],
    CALCULATE (
        [Sales Amount],
        ALLSELECTED ('Product'[Brand] )
    )
)

It calculates the ratio of sales to sales across all brands.

The ALLSELECTED qualifier removes all filters on a brand, except user filters on rows. In practice, that means the a per-brand grouping is removed but filters like a slicer applies.

The equivalent in Honeydew is

sales_percentage = sales.amount / sales.amount GROUP BY (NOT product.brand)

The GROUP BY qualifier controls how Honeydew processes the user grouping context. Since metrics in Honeydew are calculated after any WHERE filters supplied in the query, changing grouping works similarly to ALLSELECTED.

The same can also be done across an entire table (entity). This calculates the ratio of sales to sales across all products:

Sales % :=
DIVIDE (
    [Sales Amount],
    CALCULATE (
        [Sales Amount],
        ALLSELECTED ('Product')
    )
)

The equivalent in Honeydew is

sales_percentage = sales.amount / sales.amount GROUP BY (NOT product.*)

​

Using ALLSELECTED()

In DAX, ALLSELECTED() qualifier removes all context filters from columns and rows in the current query, while retaining all other context filters or explicit filters.

Sales % Total :=
DIVIDE (
    [Sales Amount],
    CALCULATE (
        [Sales Amount],
        ALLSELECTED ()
    )
)

The equivalent in Honeydew is

sales_percentage_total = sales.amount / sales.amount GROUP BY ()

​

Using ALL and REMOVEFILTERS

In DAX, ALL and REMOVEFILTERS operate before user filtering.

This expression removes all filters on brands INCLUDING user filters:

Sales % IgnoreUserFilters :=
DIVIDE (
    [Sales Amount],
    CALCULATE (
        [Sales Amount],
        ALL ('Product'[Brand] )
    )
)

The equivalent in Honeydew is

sales_percentage_ignore_user_filters = sales.amount /
  (sales.amount
	 FILTER (EXCLUDE product.brand)
	 GROUP BY (NOT product.brand))

Same would work for a whole entity (ALL('Product')):

The equivalent in Honeydew is

sales_percentage_ignore_user_filters = sales.amount /
  (sales.amount
	 FILTER (EXCLUDE product.*)
	 GROUP BY (NOT product.*))

REMOVEFILTERS removes all filters, whether they are grouping or user filters.

Sales % FromAllData :=
DIVIDE (
    [Sales Amount],
    CALCULATE (
        [Sales Amount],
        REMOVEFILTERS()
    )
)

The equivalent in Honeydew is

sales_percentage_ignore_user_filters = sales.amount /
  (sales.amount
	 FILTER (EXCLUDE)
	 GROUP BY ())

​

Using CROSSFILTER and USERELATIONSHIP

• CROSSFILTER controls the bi-directional filtering in a metric calculation
• USERELATIONSHIP engages a non active (alternative) relation between entities

In Honeydew, bi-directional filtering is part of a relationship definition. So changing cross-filtering is done in the same way as using a relationship.