CODEWITHSUNDEEP
ssasmdxolap-cube
Metanormal
Metanormal

Reputation: 27

How to get the Number of Months in filtered view as a Calculated Member with MDX in SSAS

I have to insert a calculated member into an existing OLAP-cube and (sadly) do not really know what i am doing.

The result is to be the number of matching months in the current filtered view.
I get the result of months respecting the selected year, but not the filterlist of months.

I have:

CREATE MEMBER CURRENTCUBE.[MEASURES].[AnzahlMonate] AS 
COUNT(Descendants(
     [Datum].[Datum - J - M - T].CurrentMember, 
     [Datum].[Datum - J - M - T].[Monat]  ),EXCLUDEEMPTY )
,FORMAT_STRING = "0", 
VISIBLE = 1;

I get:

Results in SSMS

I want to get:
[3]

I tried google, etc. but obviously missing the correct keywords.

Help is much appreciated. thanx in advance.

--

I am using:
SQL-Server 2014
SSMS 12.0.2456.0
Windows Server 2012 R2

Upvotes: 0

Views: 722

Answers (3)

Alex Peshik
Alex Peshik

Reputation: 1515

It's not working for me when I used GUI-designe, because filter is out of current context:

with member [Months] AS 
sum(existing [Date].[Year Qtr Month].[Month].MEMBERS
,count(existing [Date].[Year Qtr Month].CurrentMember))

select [Months] on 0
FROM ( SELECT ( 
{ [Date].[Year Qtr Month].[Month].&[2018-03]
, [Date].[Year Qtr Month].[Month].&[2018-04]
, [Date].[Year Qtr Month].[Month].&[2018-05] } ) ON COLUMNS 
FROM [Some_Cube]) WHERE ( [Date].[Year Qtr Month].CurrentMember)

But works when I manually added filter:

with member [Months] AS 
sum(existing [Date].[Year Qtr Month].[Month].MEMBERS
,count(existing [Date].[Year Qtr Month].CurrentMember))

select [Months] on 0
FROM [Some_Cube]
WHERE
{[Date].[Year Qtr Month].[Month].&[2018-03]
,[Date].[Year Qtr Month].[Month].&[2018-04]
,[Date].[Year Qtr Month].[Month].&[2018-05]}

Upvotes: 1

FrankPl
FrankPl

Reputation: 591

You should use

COUNT(EXISTING Descendants(
     [Datum].[Datum - J - M - T].CurrentMember, 
     [Datum].[Datum - J - M - T].[Monat]  ) )

instead of

COUNT(Descendants(
     [Datum].[Datum - J - M - T].CurrentMember, 
     [Datum].[Datum - J - M - T].[Monat]  ),EXCLUDEEMPTY )

If that does not work, you could try

COUNT(EXISTING [Datum].[Monat].[Monat].Members  ) )

And if all else fails, another solution would be to add another measure group based on your date dimension table, and define a measure in it which would be the DistinctCount of the month id column.

Upvotes: 1

Magnus Smith
Magnus Smith

Reputation: 5963

I think your measure is calculating the number of matching months without paying any attention to other dimensions used in the filters/slicer. So you get 12 every time.

You are using the keyword EXCLUDEEMPTY but you are not telling the MDX what would make a month empty. The MDX is looking at the top-level view of your cube. In fact, it is not looking at the numbers in the cube at all, it is just looking at how many members are in the [Datum] dimension.

Edit your answer to tell us the full MDX you are using, and we might be able to help more.

Upvotes: 1

Related Questions