Latest puzzles

RELATED in a Query Puzzle

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 puzzle is a single and simple question, but you have to find the exact answer.

» Read complete Puzzle

Last Date Puzzle

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 complete Puzzle

Data Lineage Puzzle

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 complete Puzzle

Active Orders Puzzle

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, due to the interactions of all these topics.

» Read complete Puzzle

Multiple many-to-many relationships Puzzle

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. In this scenario, you face such a data model, where bidirectional filtering is not an option, and you have to solve this complex many-to-many pattern by leveraging some DAX coding.

» Read complete Puzzle

Filtering Columns Puzzle

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, as it often happens with powerful functionalities, it hides some complexity, and this puzzle is aimed to test your understanding of SELECTCOLUMNS.

» Read complete Puzzle

Unexpected filter behavior in CALCULATE Puzzle

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 to work as expected, and it is time for another puzzle!

Hurry up, this puzzle has a limited lifetime, since it depends from a behavior that the team is already fixing, it might be outdated in a few weeks!

» Read complete Puzzle

Group By Computed Columns Puzzle

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 DAX 2015.

The new GROUPBY function in DAX 2015 provides a simple and powerful syntax to aggregate data. However, can you mimic the GROUPBY behavior with the previous version of DAX without using GROUPBY? This challenge shows you a problem that demonstrate why GROUPBY can be very useful for post-processing small result sets obtained by other table functions.

» Read complete Puzzle

USERELATIONSHIP Puzzle

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 context and context transition, the formulas tend to be much more complex than expected. If you feel confident with these topics, then this is the puzzle for you: a simple scenario, an innocent formula that looks easy at the beginning, but – as always – the devil is in the details.

» Read complete Puzzle