Adding date table to mdx query

[Anonymous]

Hello

In my last post I asked how to correctly pull data using MDX filtering and got everything working.
Code that helped me filter out and format it just how I needed:

SELECT
NON EMPTY {[Project POS].[Type hierarchy].[WinPOS], [Project POS].[Type hierarchy].[SelfCheckout]} ON COLUMNS,
NON EMPTY {[Project POS].[POS hierarchy].[Project]} ON ROWS
FROM [Property Cube]
WHERE ([Time].[Time].[Calendar Year].&[2020],[Measures].[Count of Receipts])

So now this MDX is nicely pulling the correct data from year 2020. Now I need to add a date column/row so I could add a filter to my report. I need to be able to filter between January, February, March etc. With this code atm I can only show year 2020 data without the option to choose what month,day. 
In this cube I have a Date - Time table which I'd need to add to my previous code somehow. 
When I try to add it as a new line:

SELECT
NON EMPTY {[Project POS].[Type hierarchy].[WinPOS], [Project POS].[Type hierarchy].[SelfCheckout]} ON COLUMNS,
NON EMPTY {[Project POS].[POS hierarchy].[Project]} ON ROWS,
NON EMPTY {[Time].[Time].[Calendar Year].&[2020]} ON COLUMNS
FROM [Property Cube]
WHERE ([Time].[Time].[Calendar Year].&[2020],[Measures].[Count of Receipts])

I get error 

An axis number cannot be repeated in a query.

[mwegener]

Hi @Anonymous ,

take a look at this Excel add-in.

https://archive.codeplex.com/?p=olappivottableextend

If a PivotTable is performing poorly or returning incorrect numbers, it may be necessary for the Analysis Services administrator to troubleshoot the MDX query which the PivotTable is using. The MDX tab of the OLAP PivotTable Extensions dialog shows you this MDX.

[Anonymous]

Hi

The data is not incorrect, I already had this extension installed. I need to figure out a way how to add date to my code. The date and data in the cube are linked, in cube I can see what data was created on a specific date but when I pull the entire 2020 data into PBI the link between date and data will dissapear. I can always create a new date table in PBI but this way I can not see which data was updated on what date so thats why I need to include the date table to my code from cube.

[mwegener]

Hi @Anonymous ,

the idea was that you set up your required query in an Excel pivot table and then copy the resulting query from this extension view.

[Anonymous]

Excel pivot insists to use "Hierarchize" which doesn't work in PBI. And so far I have failed to add it to my original code. Can't yet figure out how to use CrossJoin in my code either since the first row with ON COLUMNS already has 2 values, adding a third doesn't work.

SELECT NON EMPTY CrossJoin(Hierarchize({DrilldownLevel({[Project POS].[Type hierarchy].[All types]},,,INCLUDE_CALC_MEMBERS)}), Hierarchize({DrilldownLevel({[Time].[Time].[All periods]},,,INCLUDE_CALC_MEMBERS)})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS , NON EMPTY Hierarchize({DrilldownLevel({[Project POS].[POS hierarchy].[All POS]},,,INCLUDE_CALC_MEMBERS)}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON ROWS  FROM (SELECT ({[Time].[Time].[Calendar Year].&[2020]}) ON COLUMNS  FROM [Property Cube]) WHERE ([Measures].[Count of Receipts]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

[mwegener · Accepted solution]

Hi @Anonymous ,

try this ...

SELECT
NON EMPTY {({[Project POS].[Type hierarchy].[WinPOS], [Project POS].[Type hierarchy].[SelfCheckout]} * [Time].[Time].[Calendar Year].&[2020])} ON COLUMNS,
NON EMPTY {[Project POS].[POS hierarchy].[Project]} ON ROWS
FROM 
   (SELECT ({[Time].[Time].[Calendar Year].&[2020]}) ON COLUMNS  
    FROM [Property Cube]) 
WHERE ([Measures].[Count of Receipts])

[Anonymous]

Thanks that helped 🙂
Final code that does everything I need:

SELECT
NON EMPTY {({[Project POS].[Type hierarchy].[WinPOS], [Project POS].[Type hierarchy].[SelfCheckout]} * {[Time].[Time].[Month].&[202001], [Time].[Time].[Month].&[202002], [Time].[Time].[Month].&[202003], [Time].[Time].[Month].&[202004], [Time].[Time].[Month].&[202005], [Time].[Time].[Month].&[202006], [Time].[Time].[Month].&[202007], [Time].[Time].[Month].&[202008], [Time].[Time].[Month].&[202009],[Time].[Time].[Month].&[202010], [Time].[Time].[Month].&[202011], [Time].[Time].[Month].&[202012]})} ON COLUMNS, 
NON EMPTY {[Project POS].[POS hierarchy].[Project]} ON ROWS
FROM (SELECT ({[Time].[Time].[Calendar Year].&[2020]}) ON COLUMNS FROM [Property Cube]) WHERE ([Measures].[Count of Receipts])