Skip to main content
This guide helps people who are familiar with DAX to understand the concepts of Honeydew.If you are not familiar with DAX, head over to the introduction instead.

​
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.
It is possible to map a Power BI Semantic Model to a Honeydew Workspace. Similarly to Semantic Models, workspaces do not share any logic between them.However, the best practice is to translate a Semantic Model to a Honeydew Domain.Multiple business cases that have fully or partially overlapping business logic can be mapped to multiple domains in the same workspace.

​
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.
Light data transformations done in the Power Query are done in Honeydew using SQL instead.In particular, following transformations are common in SQL
  • Column selection (removing columns)
  • Column type changes
  • Removal of NULL data
  • Applying filters to source data
  • Extracting data from semi-structured formats (e.g., JSON, arrays)
  • Pivoting and unpivoting tables
These transformations can also be applied via ETL beforehand. See dbt Pipeline for an example of an architecture.

​
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.
The default for cross-filtering in Power BI is one-directional, while the default in Honeydew is bi-directional cross-filtering.As Honeydew is rooted in the SQL world, it is more common in SQL that filters on dimensions apply to other dimensions than it is with in DAX.

​
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. 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
Relationships in Honeydew are automatically resolved.

​
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))
Honeydew uses SQL syntax for aggregation statements. Any aggregation in SQL is possible in Honeydew.Common aggregations include:
DAXHoneydew SQL (on Snowflake)
APPROXIMATEDISTINCTCOUNTAPPROX_COUNT_DISTINCT
AVERAGE, AVERAGEXAVG
COUNT, COUNTX, COUNTA, COUNTAX, COUNT, COUNTBLANKCOUNT, COUNT_IF
COUNTROWSCOUNT(*) of an entity
DISTINCTCOUNT, DISTINCTCOUNTNONBLANKCOUNT(DISTINCT ...)
MAX, MAXA, MAXXMAX
MIN, MINA, MINXMIN
SUM, SUMXSUM
STDDEV.S, STDDEVX.SSTDDEV_SAMP
STDDEV.P, STDDEVX.PSTDDEV_POP
VAR.S, VARX.SVAR_SAMP
VAR.P, VARX.PVAR_POP
MEDIAN, MEDIANXMEDIAN
PERCENTILE.INC, PERCENTILEX.INCPERCENTILE_CONT, PERCENTILE_DISC
DAX utility functions have SQL equivalents as well - for example DIV0 is the SQL equivalent function to DIVIDE.

​
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)
Honeydew metric calculations always operate in the row context, with cross-entity joins automatically resolved.

​
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. 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

The filter context is where Semantic Models and Honeydew differ the most.
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.
In a general SQL SELECT statement there are many levels of “filter context”:
  1. The GROUP BY expression sets the group to aggregate within (summarize sales per brand)
  2. The HAVING expression filters whole groups based on aggregations (for brands that had more than 1 sale)
  3. The WHERE expression:
  4. Filters whole groups when it is on a field that is a part of the GROUP BY (the brand name starts with “a”)
  5. Filters rows within each group when it is on a field not part of the GROUP BY (only aggregate the sales in 2019)
  6. Window functions (OVER expression) change the context for a specific aggregation within a group.
All of those are part of the DAX filter context, but are different expressions in SQL and in Honeydew.

​
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" )
)
The KEEPFILTERS qualifier retains the filter context for the product color column.
The equivalent expression to the above with FILTER is:
Red Sales Table Filter :=
CALCULATE (
    [Sales Amount],
    FILTER (
        'Product',
        'Product'[Color] = "Red"
    )
)
This expression adds a filter to the Product table filtered by the current filter context.
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')
In Honeydew, the default behavior is to retain filters, as it follows SQL conventions.That contrasts with DAX that requires an explicit KEEPFILTERS statement and overrides filters by default.
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.
Metrics in Honeydew can be derived one from another.If multiple derived metrics add a FILTER (WHERE ...) qualifier one on top of the other, then all filters in the lineage are applied cumulatively, meaning that each filter is combined with the others in the final calculation.This behavior is identical to creating multiple derived DAX measures and using the KEEPFILTERS qualifier in each.
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"
)
The equivalent expression to the above with FILTER is:
Red Sales :=
CALCULATE (
    [Sales Amount],
    FILTER (
        ALL ( 'Product'[Color] ),
        'Product'[Color] = "Red"
    )
)
Which overrides the filter on product color (with ALL) and then applies a new one instead.
When comparing this example to using KEEPFILTERS:Both expressions (with and without KEEPFILTERS) calculate the sales amount and applies an additional filter to filter out non-red rows.The difference between the two is that CALCULATE , by default, overrides the filter context on Product[Color] . KEEPFILTERS disables that override.
  • If RedSales is sliced by Product[Color], it would return the value of red for every cell (as it overrides the filtering on color).
  • If RedSalesKeepFilters is sliced by Product[Color], it would return the red value for the red cell, and a blank value for other cells.
  • If either are sliced by Product[Brand] they will return the same value (as there is no filter context for color)
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.
For people versed in SQL, the common behavior of RedSales would be the same as ignoring a GROUP BY on product.color when calculating the sales amount aggregation, but applying any other group & filter.To write this in SQL would require a nested two-step query.
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.
Note that relationships in Honeydew are resolved automatically.
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)
Using FILTER(EXCLUDE) is not recommended in most cases.Removing the user SQL filters increases the size of the processed data and goes against the common conventions in SQL for behavior of SQL filters. That can also lead to lower performance as the size of processed data increased.When migrating DAX measures to Honeydew metrics, consider carefully whether the goal of the original metric was to remove group filtering (that is, the column with that had a filter changed in CALCULATE is always part of the given filter context) or to change row filters within a group. The common case is removing group filtering only.If an expression must operate before user row filters then the best practice in SQL is to move those calculations to an ELT process, or to a Honeydew calculated attribute.The FILTER (EXCLUDE ...) syntax to remove user filters is in private preview.
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 GROUP BY (NOT entity.field) metric qualifier in Honeydew removes all grouping on entity.field while keeping all other groups.
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.*)
Removing all fields of an entity using GROUP BY (NOT entity.*) syntax is in private preview.Alternatively, can use GROUP BY (NOT entity.field1, entity.field2, ...).

​
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

Honeydew support for metric operating before user filters is currently in private preview.In particular, the FILTER (EXCLUDE ...) syntax used below to remove user filters is not publicly available.The currently supported way to run calculations before user filters is to create them as calculated attributes - Honeydew’s equivalent for Calculated Columns. All calculated attributes operate before user filters, similar to how Calculated Columns behave in DAX.
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.
Control of relationship parameters within an expression is a future functionality not currently supported.If USERELATIONSHIP is used to create “role-playing dimensions”, can create a copy of the entity in Honeydew to enable additional roles with alternative relationships.

​
Other DAX Functionality

​
Using HASONEFILTER and FILTERS

HASONEFILTER in DAX indicates where a value is directly filtered by the user. It can be used to get the currently filter used by the user, i.e. this returns the currently chosen product key or blank otherwise: 
IF(HASONEFILTER(Sales[ProductKey]),FILTERS(Sales[ProductKey]),BLANK())
The equivalent in Honeydew is implemented using conditional filtering. This returns the filtered value or NULL otherwise: 
GET_FIELD_SELECTION(sales.product_key)

​
Using HASONEVALUE and SELECTEDVALUE

GET_FIELD_SELECTION (see above) is not a direct equivalent for SELECTEDVALUE or HASONEVALUE since it does not look at the filtered data, only at the filters supplied by the user. That said, in most practical applications SELECTEDVALUE(field, default) can be replaced with GET_FIELD_SELECTION(field, default)
⌘I