MDX - How to return a member from a set?

Question

Question

Lee Cascio · Jan 13, 2021

#InterSystems IRIS for Health #InterSystems IRIS BI (DeepSee)

How do we return a member from a set? For example if I want a calculated measure to return the NAME of the date with the max transactions, to get the set ordered I might do something like:

ORDER([TransactionDate].[YearMonthDate].[Date].MEMBERS, [Measures].[TransactionCount], DESC)

But how do I access the first member in the set as a member so that I can get to the properties such as the member NAME (what I want to return in this case)?

In some MDX implementations like Microsoft there are kind of backdoor functions for this like:

ORDER([TransactionDate].[YearMonthDate].[Date].MEMBERS, [Measures].[TransactionCount], DESC).item(0).item(0).Properties("NAME")

where the "item(0)" lets you reference individual members within a set. What is the way to achieve this functionality in IRIS?

Product version: IRIS 2020.1

Discussion (4)1

Log in or sign up to continue

Akshat Vora · Feb 3, 2021

Hi Lee,

Does using the HEAD function help? HEAD returns the first n members of a set, with n equal to 1 by default.

https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls...

Akshat

0 0

score 0 · Answer 1 · 2021-02-03T08:10:43-05:00

Hi!

I haven't tested it, but have you tried this function?

https://docs.intersystems.com/latest/csp/docbook/Doc.View.cls?KEY=D2RMDX_Properties

HTH

José

score 0 · Answer 2 · 2021-02-03T16:54:11-05:00

Lee Cascio · Feb 3, 2021

that returns properties of a member. I need to return a specific member from a set.

0 0

score 0 · Answer 3 · 2021-02-03T16:57:57-05:00

HEAD returns a set, even if that set only has one member in it. You will not be able to use the result of HEAD as an argument to a function that takes a member argument. Some MDX implementations use some sort of iterator function that allows selecting the nth member of a set (e.g. Microsoft uses an "item()" function). It sounds like there may not be something like that in IRIS MDX.