Hi there,
using AT and FOR options can be used to aggregate a measure based on the value of a given data item. However, on a relational model, is there something that can give me the aggregate of a measure for one specific value of a given data item?
i.e, this is what I have:
[Data item] [Measure] Total [Measure] for [Data item]
A 10 30
A 20 30
B 30 70
B 40 70
But this is what I need:
[Data item] [Measure] Total [Measure] for ([Data item] = 'A')
A 10 30
A 20 30
B 30 30
B 40 30
Is there any way to achieve this on relational other than a join? I'm specifically looking for a way to avoid a query join, as the resulting query is the detail query of a master detail relationship, and I'm trying to troubleshoot performance issues
Quote from: hespora on 21 Jul 2016 09:45:26 AM
Hi there,
using AT and FOR options can be used to aggregate a measure based on the value of a given data item. However, on a relational model, is there something that can give me the aggregate of a measure for one specific value of a given data item?
i.e, this is what I have:
[Data item] [Measure] Total [Measure] for [Data item]
A 10 30
A 20 30
B 30 70
B 40 70
But this is what I need:
[Data item] [Measure] Total [Measure] for ([Data item] = 'A')
A 10 30
A 20 30
B 30 30
B 40 30
Is there any way to achieve this on relational other than a join? I'm specifically looking for a way to avoid a query join, as the resulting query is the detail query of a master detail relationship, and I'm trying to troubleshoot performance issues
How about something like this?
total ( case when [Data item] = 'A' then [Measure] else 0 end for report )
Oh dear.... now I'm gonna spend the rest of the day with an impression of my palm on my forehead.
Thanks Lynn! That was spot-on, and I'm embarassed to not have thought of this. Must have been the heat! ;)
Yes, the heat is diabolical for sure!
The expression seems a simple thing once you see it, but I've seen other people that want to do "case when total (...) end" instead of this way around, so it does come up as a vexing challenge from time to time. I don't think any forehead slapping is required :)
As another handy trick, if circumstances call for a count rather than a total the "else" portion can be set to null which won't get counted.