Forum Discussion
Importing from cube with filters loosing data.
You can use import method by
Also instead of filtering you should write Filtered MDX Query
One of the easiest way is to go to SSMS and drag all the fields / measures that are required and then "Design Mode" to get the MDX query of your data and then write query in below segment.
Thanks !
I always believe Dimension must be come it Rows and then you can use Matrix Visualization to swap Rows in columns
You may want to add those 2 in filters
SELECT NON EMPTY { [Measures].[Count of Receipts] } ON COLUMNS, NON EMPTY { ([Project POS].[Type hierarchy].ALLMEMBERS * [Project POS].[POS hierarchy].ALLMEMBERS ) } ON ROWS FROM ( SELECT ( { [Time].[Time].[Calendar Year].&[2020] } ) ON COLUMNS FROM ( SELECT ( { [Project POS].[Type hierarchy].&[WinSCO] ,[Project POS].[Type hierarchy].&[WinPOS]} ) ON COLUMNS FROM [Property Cube]) WHERE ( [Time].[Time].[Calendar Year].&[2020] ))If I haven't write correct hierarchies you may want to drag and drop each member of type in SSMS and then put in the filter statement
Hopefully this will helps too..
Community ChampionYou can use import method by
Also instead of filtering you should write Filtered MDX Query
One of the easiest way is to go to SSMS and drag all the fields / measures that are required and then "Design Mode" to get the MDX query of your data and then write query in below segment.
Well I got as far so far:
SELECT NON EMPTY Hierarchize({DrilldownLevel({[Project POS].[Type hierarchy].[All types]},,,INCLUDE_CALC_MEMBERS)}) ON COLUMNS , NON EMPTY Hierarchize({DrilldownLevel({[Project POS].[POS hierarchy].[All POS]},,,INCLUDE_CALC_MEMBERS)}) ON ROWS FROM [Property Cube] WHERE ([Time].[Time].[Calendar Year].&[2020],[Measures].[Count of Receipts])
This MDX works very well in SSMS but in PBI it doesnt.
Instead where I should have Project names I get [Project POS].[POS hierarchy].[All POS] for every value. I read earlyer about PBI having problems with hierarchys but don't know how to bypass it now in my code.
PS. I know this topic has been answered already but I hope I can get some ideas how to fix it without making a new topic.
- FarhanAhmed
Altought I am not an expert of MDX but your Try something like this
SELECT NON EMPTY { [Measures].[Count of Receipts] } ON COLUMNS, NON EMPTY { ([Project POS].[Type hierarchy].ALLMEMBERS * [Project POS].[POS hierarchy].ALLMEMBERS ) } ON ROWS FROM ( SELECT ( { [Time].[Time].[Calendar Year].&[2020] } ) ON COLUMNS FROM [Property Cube]) WHERE ( [Time].[Time].[Calendar Year].&[2020] )If this helps kindly mark "Kudos" to this post
Thank you, getting closer 🙂
In your code the type hierarchy is in ROW which means all types are mixed up in rows but I need types to be in a column. IE I have 2 different types WinPOS and WinSCO, need those 2 types in columns not in rows. If you have any suggetions I'd be happy to hear but if not then thank you still, your code got me closer to the final code 🙂
- FarhanAhmed
Thanks !
I always believe Dimension must be come it Rows and then you can use Matrix Visualization to swap Rows in columns
You may want to add those 2 in filters
SELECT NON EMPTY { [Measures].[Count of Receipts] } ON COLUMNS, NON EMPTY { ([Project POS].[Type hierarchy].ALLMEMBERS * [Project POS].[POS hierarchy].ALLMEMBERS ) } ON ROWS FROM ( SELECT ( { [Time].[Time].[Calendar Year].&[2020] } ) ON COLUMNS FROM ( SELECT ( { [Project POS].[Type hierarchy].&[WinSCO] ,[Project POS].[Type hierarchy].&[WinPOS]} ) ON COLUMNS FROM [Property Cube]) WHERE ( [Time].[Time].[Calendar Year].&[2020] ))If I haven't write correct hierarchies you may want to drag and drop each member of type in SSMS and then put in the filter statement
Hopefully this will helps too..