We write articles and white papers on Microsoft Business Intelligence technologies on a regular basis. The list below showcases all our content sorted chronologically - you can filter through the content by post type, topic, and author.
    9 DAX puzzles
DAX puzzle –

RELATED in a Query

In DAX, columns have lineage. Knowing when lineage is maintained in a DAX expression and when it is not, is an important skill to write effective code. As a general rule, column references maintain lineage whereas expressions lose it. This…  Read more

DAX puzzle –

Last Date

The LASTDATE function in DAX retrieves the last date in the filter context. In this puzzle, you have to fix a wrong measure where LASTDATE is not working as expected.  Read more

DAX puzzle –

Data Lineage

DAX has an internal tracking of physical columns (lineage) that keeps references to original columns after certain transformations applied in a DAX expression. In this puzzle, you have to predict the result of two similar DAX queries.  Read more

DAX puzzle –

Active Orders

Relationships, filter context, row context, context transition, measures. Any DAX programmer knows that mixing all these concepts together makes the formulas somewhat complex. In this puzzle, we ask you to author a measure that proves to be a bit challenging,…  Read more

DAX puzzle –

Multiple many-to-many relationships

In Power BI Desktop and SQL Server Analysis Services 2016 you can rely on bidirectional filtering to implement many-to-many relationships. Nevertheless, as powerful as bidirectional filtering is, it cannot handle scenarios where it would generate ambiguous paths among your tables.…  Read more

DAX puzzle –

Filtering Columns

In Power BI Desktop and SQL Server Analysis Services 2016 the SELECTCOLUMNS function allows you to retrieve some of the columns out of a table. This enables you to write set operations and queries in a very simple way. But,…  Read more

DAX puzzle –

Unexpected filter behavior in CALCULATE

Filters in CALCULATE override previously existing filters on the column they use. For example, CALCULATE ( [Sales], Product[Color] = “Red” ) returns red sales, no matter what you filter in a pivot table. Nevertheless, this golden rule sometimes looks not…  Read more

DAX puzzle –

Group By Computed Columns

In any version of DAX, you can aggregate data by grouping one or more columns using SUMMARIZE and/or ADDCOLUMNS. In Excel 2016, Power BI Desktop, and Analysis Services 2016, you have a new version of DAX that we identify as…  Read more

DAX puzzle –

USERELATIONSHIP

DAX supports multiple relationships between tables, even if only one out of the many can be active at any time. USERELATIONSHIP, with CALCULATE, lets you activate one relationship, deactivating the other ones. As soon as you mix USERELATIONSHIP, CALCULATE, row…  Read more