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

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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