4

I have a string-type dimension in my SSAS cube having the date values along with some blank values and please find the screenshot for your reference.

enter image description here

I am trying to convert these values into Date using the CDate function in the MDX query. I have used the following MDX query which will return null when the value is either empty or null and returns the date value for valid values.

WITH
MEMBER [Measures].[Company].[OrderDate] AS 'CASE WHEN ISEMPTY([Company].[OrderDate].CurrentMember.MemberValue) OR [Company].[OrderDate].CurrentMember.MemberValue = null THEN null Else CDate([Company].[OrderDate].CurrentMember.MemberValue) END' SELECT{
[Measures].[Company].[OrderDate]} ON COLUMNS ,Subset(NONEMPTY((
(Order((([Company].[OrderDate].[OrderDate].ALLMEMBERS)), CDate([Company].[OrderDate].CurrentMember.Member_Caption),ASC))),{[Measures].DefaultMember}),0,100) ON ROWS FROM [Model] CELL PROPERTIES VALUE, FORMATTED_VALUE, FORMAT_STRING

However, When am trying to convert these string values into Date I am getting the Type mismatch error as shown in the following screenshot:

enter image description here

Can anyone please suggest how to convert the string dimension values to date type when blank values are present in the dimension?

Yahfoufi
  • 2,220
  • 1
  • 22
  • 41
TAMILARASAN R
  • 225
  • 1
  • 6
  • Not an expert, but a bit concerning that there are multiple rows with what appear to be the same value in the dimension table. Can you eliminate these (blank) values from the dimension? If you cannot do this, what DATE value would you like to use instead of the blank that is present? – v0rl0n Mar 14 '22 at 14:11

0 Answers0