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:- A global semantic model defined in a Honeydew workspace covers multiple business use cases at once.
- A Honeydew Domain is a curated subset of the global model to serve for a specific business domain or use case.
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
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 (
LEFTorINNER). Honeydew also supports other join types (RIGHTandFULL 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 asinventory with custom SQL
DAX Calculated Columns
A calculated column in a DAX table corresponds to a calculated attribute in a Honeydew entity. A DAX expression such asOrder 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 theRELATED function:
RELATED:
Relationships in Honeydew are automatically resolved.
Using metrics in calculated columns
Honeydew enables the use of metrics as part of calculated columns: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 asHoneydew uses SQL syntax for aggregation statements. Any aggregation in SQL is possible in Honeydew.Common aggregations include:
DAX utility functions have SQL equivalents as well - for example
| DAX | Honeydew SQL (on Snowflake) |
|---|---|
APPROXIMATEDISTINCTCOUNT | APPROX_COUNT_DISTINCT |
AVERAGE, AVERAGEX | AVG |
COUNT, COUNTX, COUNTA, COUNTAX, COUNT, COUNTBLANK | COUNT, COUNT_IF |
COUNTROWS | COUNT(*) of an entity |
DISTINCTCOUNT, DISTINCTCOUNTNONBLANK | COUNT(DISTINCT ...) |
MAX, MAXA, MAXX | MAX |
MIN, MINA, MINX | MIN |
SUM, SUMX | SUM |
STDDEV.S, STDDEVX.S | STDDEV_SAMP |
STDDEV.P, STDDEVX.P | STDDEV_POP |
VAR.S, VARX.S | VAR_SAMP |
VAR.P, VARX.P | VAR_POP |
MEDIAN, MEDIANX | MEDIAN |
PERCENTILE.INC, PERCENTILEX.INC | PERCENTILE_CONT, PERCENTILE_DISC |
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, aSUM in DAX aggregates over the rows of a table:
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
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] SUMXthen aggregates the result with a sum.
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 DAXFILTER function.
For example, consider the following statement:
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.Stacked Row Context
DAX enables to stack row contexts for ad-hoc joins between tables. For example, in the statement below:- Outer
SUMXdefines an iterator per row of customer - Inner
SUMXiterates per row of customers, per row of sales
sales):
Filter Context
The filter context is where Semantic Models and Honeydew differ the most.
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 SQLGROUP BY and WHERE to a DAX filter context might be confusing at first.
Consider the following SQL statement:
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”:- The
GROUP BYexpression sets the group to aggregate within (summarize sales per brand) - The
HAVINGexpression filters whole groups based on aggregations (for brands that had more than 1 sale) - The
WHEREexpression: - Filters whole groups when it is on a field that is a part of the
GROUP BY(the brand name starts with “a”) - Filters rows within each group when it is on a field not part of the
GROUP BY(only aggregate the sales in 2019) - Window functions (
OVERexpression) change the context for a specific aggregation within a group.
Adding filters
Some measures require to add filtering on the table used in the aggregation. In SQL terms, that would mean adding aWHERE 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
[Sales Amount] measure:
The
KEEPFILTERS qualifier retains the filter context for the product color column.Alternative DAX expression using FILTER
Alternative DAX expression using FILTER
The equivalent expression to the above with This expression adds a filter to the
FILTER is:Product table filtered by the current filter context.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.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.AND in the statement.
This DAX filtering expression
Changing filters
This DAX expression changes the filter on color to be red, ignoring any previous filters on color.Alternative DAX expression using FILTER
Alternative DAX expression using FILTER
The equivalent expression to the above with Which overrides the filter on product color (with
FILTER is: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
RedSalesis sliced byProduct[Color], it would return the value of red for every cell (as it overrides the filtering on color). - If
RedSalesKeepFiltersis sliced byProduct[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)
- 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). - If there was no SQL grouping on color, remove any row (
WHERE) filters on color, and filter color for red instead.
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.GROUP BY (NOT product.color) qualifier instructs Honeydew to ignore that group when calculating the metric.
Note that relationships in Honeydew are resolved automatically.
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.AND in the statement.
The following expression that changes filters on two tables
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.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:
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:
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
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.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.
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.ALL and REMOVEFILTERS operate before user filtering.
This expression removes all filters on brands INCLUDING user filters:
ALL('Product')):
The equivalent in Honeydew is
REMOVEFILTERS removes all filters, whether they are grouping or user filters.
Using CROSSFILTER and USERELATIONSHIP
CROSSFILTERcontrols the bi-directional filtering in a metric calculationUSERELATIONSHIPengages a non active (alternative) relation between entities
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:
NULL otherwise:
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)