The totals and the NonEmptyCrossJoin function

Let’s suppose we need a report with the following structure:

Excel is able to generate, but it’s laggy. Let’s start from the query without totals:

Let’s get the total for each client:

Let’s unite two queries:

Boom, it looks like the performance of the query will last forever. Why do the two queries perform way faster separately than together? We should blame Union, also known as the +, function for that. Separately, we throw the empty members away dynamically. When it comes to two big crossjoined sets to be united before the query start it takes time, depending on the number of members, not the query result. Luckly, there is a NonEmptyCrossJoin function which will filter the empty members out dynamically. Thus, the performance is okay once again:

Happy querying. ;)