Measure not aggregating with CASE STATEMENT

Question

I have a measure "Ledger Report View"."Posted Trans Amount" with default aggregate of sum. When place in a column by itself it aggregates as expected.

But, I need to have two columns using this same measure with different criteria, so I can't just put it into the criteria. I need one col to filter by March, 2024, and one col to filter by March, 2025.

Here is my case statement. Once I get it working I will be adding the Month and Fiscal Year into a prompt, so I can't place this in the RPD.

CASE WHEN "Ledger Report View"."Fiscal Month" = 'March'

AND   "Ledger Report View"."Fiscal Year" = '2024'

THEN  "Ledger Report View"."Posted Trans Amount" END

My issue is that this column will not aggregate. I don't know what I'm doing wrong.

Gianni Ceresa · Answer

When looking at the physical query generated with the CASE WHEN formula, you see that it's a very complex query for what should be very simple. The query start doing the right thing, in a very complicated way, then go into analytical functions for summing over partitions and it finally decide to only take the first row for each partition. This means that if the CASE WHEN condition wasn't matching the very first record of the partitions, the right amount is further down, but only the first row of the partition is selected.

I would call this a good example of over thinking: it could be easy, but the BI Server start doing very complicated things, and the number you look for disappear.

This happen because the attributes of your condition aren't in the analysis. If you add them, all good, it will work, because the query become a lot simpler.

Business as usual in the life of a query, if often works, and it can easily get lost :D

Jerry Casey · Answer

I notice that the original CASE statement is missing an ELSE clause.  That can be a problem.

Gianni Ceresa · Answer

The simplest way to find the best syntax, is to use the UI window to make the expression.

It looks like parentheses is what makes the composed condition work: a set of ( ) after USING and there inside you define your expression.

But use the UI to build it and you get a valid expression to start with.