MDX Help SSRS Query Designer MDX Calculated Member & MDX Operator Filter Expression

0
votes

I have 2 questions in the SSRS Query Designer for my SSAS tabular model. I create a calculated Member column that basically says, IF this other column = "Last Invoice Date", put "Last Invoice Date" as the value, otherwise, put a different column's value in, "FiscalMonthLastDay":

IIF([Invoice Date].[Relative Date].CurrentMember.MEMBER_CAPTION ="Last Invoice Date", "Last Invoice Date", [Invoice Date].[FiscalMonthLastDay].CurrentMember.MEMBER_CAPTION)

When i drag that new field in, it works but its creating extra rows in my results. I added before & after screen shot (erased some sensitive data). I can't figure out how to get it to not show the extra rows?

Before: enter image description here

After:

enter image description here

My 2nd question is, i would then like to filter my data set to only where this new calculated member matches another column. So I am doing the MDX operator in the filters section and thought something like this would work, but i can't get it to (its the same formula as above):

enter image description here

If anyone knows where to get some good examples of how this MDX operator is used, that would be great !

Thanks !

1
sql-serverreporting-servicesssrs-2008ssasmdx
I've realized I can probably skip creating a column, then trying to filter by that column if it's possible to do an OR statement. Something like: [Invoice Date].[Relative Date].&[Last Invoice Date] OR [Invoice Date].[FiscalMonthLastDay].[FiscalMonthLastDay].currentmember.MEMBER_CAPTION = [Invoice Date].[Date].[Date].currentmember.MEMBER_CAPTION I am getting the following error though: The Axis0 function expects a tuple set expression for the argument. A string or numeric expression was used. The filter works if i just do 1st part of the expression.Any ideas anyone? – David Squires

1 Answers

0
votes

I don't understand this:

[Invoice Date].[Relative Date].CurrentMember.MEMBER_CAPTION ="Last Invoice Date"

Looking at your screenprints [Relative Date] is a date - so how can MEMBER_CAPTION be equal to "Last Invoice Date" ?