I have MDX query:
SELECT NON EMPTY { [Measures].[Revenue] } ON COLUMNS, NON EMPTY {( [DimDates].[YearMonthNum].[YearMonthNum].ALLMEMBERS )}
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [TransactionsData].[FirstYearMonth].&[2020-01] } )
ON COLUMNS FROM ( SELECT ( { [TransactionsDataExtra].[Cluster].[All] } )
ON COLUMNS FROM ( SELECT ( { [TransactionsDataExtra].[Campaign].[All] } )
ON COLUMNS FROM ( SELECT ( { [TransactionsData].[RevenueGenerating].[All] } )
ON COLUMNS FROM ( SELECT ( { [TransactionsData].[Product].[All] } )
ON COLUMNS FROM ( SELECT ( { [TransactionsDataExtra].[Channel].[All] } )
ON COLUMNS FROM (SELECT ( { [TransactionsDataExtra].[Custom4].[All] } )
ON COLUMNS FROM (SELECT ( { [TransactionsDataExtra].[Custom5].[All] } )
ON COLUMNS FROM (SELECT ( { [TransactionsDataExtra].[Custom6].[All] } )
ON COLUMNS FROM (SELECT ( { [TransactionsData].[ClientId].&[2] } )
ON COLUMNS FROM [Model]))))))))))
WHERE ( [TransactionsDataExtra].[Channel].[All],
[TransactionsData].[Product].[All],
[TransactionsData].[RevenueGenerating].[All], [TransactionsDataExtra].[Campaign].[All],
[TransactionsData].[FirstYearMonth] <= ['2020-01'],
[TransactionsDataExtra].[Cluster].[All], [TransactionsData].[ClientId].&[2] )
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING,
FONT_NAME, FONT_SIZE, FONT_FLAGS
I want to get results where e.g. Custom6 is NULL, or Custom5 is NULL or both of them, or any other filter, or Custom5[NULL, Value]
but I've been struggling to find how to do that and where in the query add changes... I tried passing nothing like: [TransactionsDataExtra].[Cluster].&[] but that didn't return anything at all. Could anyone help me out?
I also looked up ISEMPTY but can't figure out how to add it..
Fo anyone struggling in the future like noob me use this:
[DataExtra].[Cluster].& instead [DataExtra].[Cluster].&[]
also, check this amazing Excel extension to view queries: https://olappivottableextensions.github.io/#view-pivottable-mdx