Project Botticelli

More MDX Member & Set Functions Purchase this course

25 February 2014 · 11 comments · 3493 views

Getting More Advanced

Chris Webb shows how to set date dimension type properties

MDX has many set and member functions that make it easy to create all kinds of advanced common business calculations. Watch this full-length, 1-hour 40-minute, demo-rich video by Chris Webb to learn how to compute important business reporting metrics using MDX.

The PeriodsToDate() function, and related functions like YTD(), QTD() and MTD(), can be used to create to-date calculations. One example is a year-to-date calculation, which returns the sum of a measure value for all of the days in a year up to the current date. You can use the PeriodsToDate() function to do this, or if you have configured the Type property on your Date dimension you can use the more concise YTD() function.

The ParallelPeriod() function can be used to create same-period-previous-year calculations. It works as follows: you pass it a member, a level and an offset, and it finds the ancestor of the member at the given level, moves back the number of members specified in the offset and finally returns the member in the same relative position underneath the offset member, as the original member was underneath its ancestor.

There are a number of different ways to filter a set in MDX. The Filter() function is a general-purpose function that takes a set and a boolean expression, and returns only the items in the set where the boolean expression evaluates to True. NonEmpty() is a more specialised function that takes two sets, and returns only the items in the first set where there is at least one tuple in the second set that evaluates to a non-null value.

The TopCount() function takes a set, and it returns the top n values from that set by a given numeric expression; it’s useful for creating reports that show your top 10 products or sales people. There are a number of similar functions such as BottomCount(), TopPercent(), BottomPercent(), Head(), Tail(), plus the special Generate() function useful for nested TopCount() calculations.

There is no Order By clause in MDX—the items in a set can be ordered using the Order() function, and it can also be used with the Rank() function to create calculated measures that return ranks.

The StrToX family of functions take a string expression that contains MDX, evaluates the MDX, and returns the result. They must be used when working with parameterised MDX queries. They have an optional second parameter, the Constrained flag, that prevents MDX injection attacks. There is a related family of functions, the XToStr functions, that take MDX objects and return their string representations.

Log in or purchase access below to the premium version of this content.

Purchase This Course or Full Access Subscription

Single Course

$250/once

Access this course for its lifetime*.
Purchase
Subscription Best Value

$480/year

Access all content on this site for 1 year.
Purchase
Group Purchase

from $480/year

For small business & enterprise.
Group Purchase

  • Redeem a prepaid code
  • Payment is instant and you will receive a tax invoice straight away.
  • Your satisfaction is paramount: we offer a no-quibble refund guarantee.

* We guarantee each course to be available for at least 2 years from today, unless marked above with a date as a Course Retiring.

Comments

goranbanjac · 6 February 2015

Hi Chris,
I’m new to mdx and I find your video series simple amazing.
While understanding mdx more with each video I’m still stuck with the task that I have and I can’t write it yet into mdx script.
Tsql script is:

Select count(*) from AdtStorage.dbo.EncounterVisit (nolock)
where OriginID = 66
And (
AdmitDateTime >= '2013-01-01' And AdmitDateTime <= '2013-01-31' )
Or
DischargeDateTime >= '2013-01-01' And DischargeDateTime <= '2013-01-31'
)

What I’m trying to do is for given period of time (month) find count of patient that have been in hospital (OriginId).
I have Patient Cube with Time, Patient, Encounter, Organization (it is patient visit Id that gets changes every visit) dimensions and Patient Fact Table. Any help would be greatly appreciated.

Chris Webb · 11 February 2015

First you'll need to create a measure on your cube that does the count, if you don't have one already. Your query will then be something like this:
select {measures.[mycountmeasure]} on 0
from MyCube
where(
Origin.OriginID.&[66],
{
crossjoin(
{AdmitDateTime.AdmitDateTime.&[20130101]:
AdmitDateTime.AdmitDateTime.&[20130131]},
{DischargeDateTime.DischargeDateTime.[All]})
,
crossjoin(
{AdmitDateTime.AdmitDateTime.[All]},
{DischargeDateTime.DischargeDateTime.&[20130101]:
DischargeDateTime.DischargeDateTime.&[20130131]})
})

This blog post should help understand what's going on here:
https://cwebbbi.wordpress.com/2007/04/04/or-queries-in-mdx/

mgltd · 9 September 2015

Hello chris, Please is it possible to use the exist function on different dimension, I am trying a query like this

with
member
measures.lil
as

count(
Exists
(
{([Transaction].[RPC Count].&[1],[Transaction].[Account ID].[Account ID])}
,
{([Account].[PAYMENTSTATUS].&[0],[Account].[Account ID].[Account ID])}
))

select
measures.lil on 0

FROM sax
but the query only return the count of the first set.

Chris Webb · 9 September 2015

You can use the Exists() function across dimensions (by specifying the third parameter which contains the name of a measure group); that's more or less the same as what the NonEmpty() function does, and I prefer to use the latter.

mgltd · 9 September 2015

Thanks for the response chris, I guess I am doing it wrong, I am trying to check the count of acount in a set like below
lets say the count of set below comes out as
with
member
measures.lil
AS
COUNT(([Transaction].[RPC Count].&[1],[Transaction].[Account ID].[Account ID]))

SELECT
measures.lil ON 0
FROM
sax
----------------------------------------------------------
and the next one comes out as 9
with
member
measures.lil
AS
COUNT(([Account].[PAYMENTSTATUS].&[0],[Account].[Account ID].[Account ID]))

SELECT
measures.lil ON 0
FROM
sax
----------------------------
but when i combine the two like below , the query returns 5
with
member
measures.lil
as
count(
Exists
(
{([Transaction].[RPC Count].&[1],[Transaction].[Account ID].[Account ID])}
,
{([Account].[PAYMENTSTATUS].&[0],[Account].[Account ID].[Account ID])}
))
select
measures.lil on 0
sax
------
please what am i doing wrong

aaronlebato · 16 March 2017

Hey Chris,

How do you typically handle getting the Current Year ("this year")? I'm currently using the to string function along with now(). Is there a better way?

Chris Webb · 19 March 2017

I build new attributes on the Date dimension, similar to what I show here: https://blog.crossjoin.co.uk/2013/01/24/building-relative-date-reports-in-powerpivot/

Using the Now() function can be really bad for performance because it prevents the use of the formula cache.

kip.graham · 19 April 2017

Hi Chris,

I have a general question.

I have written queries that feed into two pivot tables that have the same two variables on columns but each pivot table has different filters. Is there a way to create a query that will allow me to combine these two pivot tables into one?

Essentially, I need a way to replicate having two FROM clauses that brings the same variables individually based on the two different filters. I was thinking of making members in the WHERE clause to create members for one of the two filters for each variable.

I'd love to hear your thoughts to see how you would approach this or if it is even possible.

Really enjoyed the videos thus far.

kip.graham · 19 April 2017

I'll post both queries below. As you can see, I'm pulling two variables on COLUMNS and one variable on ROWS, just with differing FROM clauses.

1.

SELECT
NON EMPTY
{
[Measures].[Contract Value USD],
[Measures].[WAvg Loan Rate]
}
ON COLUMNS,
NON EMPTY
[Security].[BB Ticker].MEMBERS
ON ROWS
FROM
(
SELECT
{[Security].[Quotation Geography].&[UNITED STATES]}
ON COLUMNS
FROM
(
SELECT
{[Trade].[Trade Type].&[LND]}
ON COLUMNS
FROM
(
SELECT
{[Classification].[Trading Sub Class].&[Client SBL]}
ON COLUMNS
FROM
(
SELECT
{[Date - Business].[Latest Date Flag].&[Y]}
ON COLUMNS
FROM
SBL_ANALYTICS
)
)
)
)

2.

SELECT
NON EMPTY
{
[Measures].[Contract Value USD],
[Measures].[WAvg Loan Rate]
}
ON COLUMNS,
NON EMPTY
[Security].[BB Ticker].MEMBERS
ON ROWS
FROM
(
SELECT
{[Security].[Quotation Geography].&[UNITED STATES]}
ON COLUMNS
FROM
(
SELECT
{[Trade].[Trade Type].&[BOR]}
ON COLUMNS
FROM
(
SELECT
{[Security].[SBL Asset Class].&[Equity]}
ON COLUMNS
FROM
(
SELECT
{[Trade].[Settled Pending].&[Settled]}
ON COLUMNS
FROM
(
SELECT
{[Counterparty].[Internal_External].&[EXTERNAL]}
ON COLUMNS
FROM
(
SELECT
{[Security].[MoneyBack Ind].&[N]}
ON COLUMNS
FROM
(
SELECT
{[Date - Business].[Latest Date Flag].&[Y]}
ON COLUMNS
FROM
SBL_ANALYTICS
)
)
)
)
)
)
)

Chris Webb · 20 April 2017

You could do this in MDX using calculated members, yes, if you need to use an Excel PivotTable; if you are writing your own query, you would could just use a set of tuples in the WHERE clause (there's no need to use so many subselects as these queries do) and this would perform better. The best approach would be to create a new dimension in the cube with members representing these selections.

kip.graham · 25 April 2017

I seem to have figured out the problem thanks to your input, thank you.

Online Courses