Search code examples
sqlsql-serverssasmdxolap

How to set a flag of current year as a computed column in OLAP cube?

I want in MDX:

-- T-SQL

SELECT
IIF(cal.CalendarYear = YEAR(GETDATE()), 1, 0) AS ComputedColumn
FROM dim.Application app
JOIN dim.Calendar cal ON cal.DateID = app.ApplicationDateID

I've tried MDX:

SELECT IIF(ISEMPTY({[Dimension].[Application].[ApplicationDateID]}), 0, 1) ON 1
FROM [DWH OLAP]
WHERE StrToMember("[Calendar].[CalendarYear].&[("+Format(now(),'yyyy')+")]";

which raises a parser error.

Query (3, 76) Parser: The syntax for ';' is incorrect. (SELECT IIF(ISEMPTY({[Dimension].[Application].[ApplicationDateID]}), 0, 1) ON 1 FROM [DWH OLAP] WHERE StrToMember("[Calendar].[CalendarYear].&[("+Format(now(),'yyyy')+")]";

I want to use that column as a filter flag for Power BI report. Help with MDX would be appreciated.

Solution

  • You always need to start an MDX script with the 0 dimension - otherwise it is not valid MDX. This should be valid:

    SELECT 
  IIF(ISEMPTY({[Dimension].[Application].[ApplicationDateID]}), 0, 1) ON 0 //<<<< 0
FROM [DWH OLAP]
WHERE 
   StrToMember("[Calendar].[CalendarYear].&[("+Format(now(),'yyyy')+")]"; //<<experiment with or without the semi-colon it is good practise to include but not mandatory