Search code examples
sqlsql-serverssasmdx

MDX Calculated Member filter using attribute not working

I have this setup:

ID  T   Date
2   T2  2022-11-18
3   T1  2022-11-21

and in the main fact table there are deals with ID 2 and 3.

Date is an attribute and appears, and works correctly, as a slicer in a pivot table in Excel. T is also an attribute, not visible, purely for the calculated members.

I created a Calculated Member and it doesn't work:

([Date].[T].&[T2], [Measures].[Notional_SUM])

However a check/test using ID does:

([Date].[ID].&[2], [Measures].[Notional_SUM])

obviously this works as 2 is actually in the fact table but what have I forgotten such that using T does not work?

I want to be able to use T as there'll always be T1 and T2 dates but I may not always know the ID (auto- generated by the SQL script rolling the dates).

***EDIT - After testing in Excel I realised that the one I thought does not work actually does if Date is removed from the slicer/top setup.

So obviously the top/slicer is a WHERE on just that date meaning my calculated member

([Date].[T].&[T2], [Measures].[Notional_SUM])

does not 'find' any T2 in the data it sees.

So how can I have a Calculated Member that always shows the T2 data?

I'm constructing an SSAS copy of an existing in-memory Java OLAP cube which does this and I have to ensure all dims/measures are the same.

Thanks

Leigh tilleytech.com

Solution

  • So actually as I've not used SSAS before I'm still learning bits and I had not checked out the attribute relationship tab.

    I used the attribute relationship tab to define that T is related to Date and not ID (the ID links to the fact tables).

    Once this was done the behaviour now works and when Date is changed the T2 values, and move/diff calculated members, all work as expected.