How to Create a measure from a dimension
I have a dimension for my custom Financial Calendar and I want to add a column to my Discovery which is filtered on Year to show the Max Period. When I try and use MAX(Data Point.Period) it returns a number and not the Period Name (which are month names).
How do I achieve this?It is the equivalent of this in SQL
select max(period)
,sum(sales)
from sales a
,financial_calendar b
where a.period = b.period
and b.year = '2023'
2 replies
-
Hi Steve,
Pyramid's Semantic Model is dimensional, so you have to think Sets rather than Tables, Rows and Columns.
So, you need to create a set of Periods, sorted by Sales descending, then pick the first member of that set and instead of getting the value, get its name.
You can use the function TopCount() to order the Periods, using Sales as the Measure, then use GetElement() to pick the first one, then the .caption() function to get its name and place the result into the Measures dimension thus:
Note: GetElement() starts its position at 0, rather than 1; the result is placed into the Measure dimension, using the context panel highlighted above; TopCount is returning just one Member, but you could return as many as you like and use GetElement() to pick any position in that list.
In your Discover, then filter on Year to get whichever Year is needed. You can filter on anything else you want to get the Max Period for that filter value as well as the Year:
Remember that if you place anything on the rows, the calculation will ignore it as it is being calculated independently of the dimension on the rows:
If you want the Max Period to reflect its context in the query, you need to add the "CurrentMember" of the Row dimension, in this case Country, to the DataPoint in the TopCount() function:
Hope that helps.
Ian
-
Hi Ian, I'll take a look at this, thanks for your suggestion.