Measure not aggregating with CASE STATEMENT

brenda_strong

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

Hi,

Not aggregate meaning that it force the granularity to a detail level and will generate many more rows than expected, or how the screenshot show just empty?

If it's just empty, what does the generated query look like? Is your condition correct? (is the year a string and not a number?)

The query should point you in the right direction of what is going on.

As an alternative to your CASE WHEN, you can also try using the FILTER(… USING …) , it should work well for your case.

brenda_strong

@Gianni Ceresa Thanks for your feedback. I tried FILTER(…USING…) and it works well.

FILTER("Ledger Report View"."Posted Trans Amount" USING "Ledger Report View"."Fiscal Year" = 2024)

However, I can't figure out the syntax to add another filter to the same column. I tried putting AND after the 2024, and I tried using the filter AND another filter, but neither worked.

I need it something like:

FILTER("Ledger Report View"."Posted Trans Amount" USING "Ledger Report View"."Fiscal Year" = 2024 AND

"Ledger Report View"."Fiscal Month" = 9)

Gianni Ceresa

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.

Jerry Casey

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

Gianni Ceresa

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