Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Adding date table to mdx query

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 {[Proj...
Need Help
  • mwegener's avatar
    mwegener
    6 years ago

    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])

     

mwegener's avatar
mwegener
Most Valuable Professional
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    mwegener
    Most Valuable Professional
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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])