mdx

Hi Team, I have a Pivot Table report linked up to a data model held in the Power BI Service. OLAP is used for fast querying. I now need to present some of the data in a slightly different way and I'm wondering if MDX (in Excel) might be the solution. Below is small example to try to...

Hello everyone, Below is a simplification of a pivvottable (OLAP!) I've created. I'm having trouble adding a column that shows the difference between the two periods I cannot use a calculated column because the source is OLAP/cube I cannot figure out how to use an MDX measure because there is...

What is the correct way to get MDX into a CUBE function? I would like to display for my users on their worksheet when the last update of various tables in the data model occurred. We could do this with a macro or another query (probably run by a macro) but it seems there should be a way for a...

I am attempting to write an MDX code to pull in data from the current month only. Here is what I have tried: TAILMTD( [«Member»] )

Hi Would appreciate any help with this, I’m struggling with the MDX trying to calculate BMI ( Body Mass Index = Weight / Height Squared ), as a Calculated Member in PivotTable Tools -> Analyze -> OLAP Tools -> MDX Calculated Member. I believe I need an IF, as Height might be 0 in some...

Hi All, I have a Pivot table created with Cube connection. I have a tab where we can go and select the desired selections using slicers/dropdown/etc. Based on the selections, in one particular cell, i will create a MDX query. Is there a way that query is taken and Pivot table refreshes using...

Hi All, I have a file with 5 tabs. In first Tab, i provide the selection criteria (Slicers/Drop downs etc.), 2nd tab for few of the mapping tables. 3rd tab with few pivot tables which are not connected to slicers (1st tab). Now in the 5th tab i have report where the data is populated based on...

I would like to ask some MDX expert to help me with the following: 1) My calculated measure in excel looks like this and returns sales revenue in a given day: SUM([Measures].[Sales],[Time span base].[Time span base].[Days].&[41177]) 2) My second calculated measure in excel looks like this and...

I have an olap database which I am connecting to and I'm wondering if it is possible to pull back a list of all of the cubemembers beneath a level of hierarchy. So for example, I have a pivot table which is connected to the database. It displays team names as rows. I can expand each team...

Here is my issue: I am using 2013 excel and my data connection is to a cube. I use a few MDX codes to access the data in the cube. For some reason, my MDX set is in my pivot table but not in my list for sets connected to the cube. Does anyone know where the MDX data is stored in the xml of...

In Excel 2010 I am using the following cubeset to return a targeted list of customers. =CUBESET("MyCube", "Filter({[Date].[Date].[All].[2014]}* {[Territory].[Territory Name].[All].[California]}*{[Customer].[Customer ID].Children},([Measures].[Targeted])>= 1)","Cube Set") This works fine...

Dear all, I have been trying to create a macro that: 1) opens all my excel files in a folder 2) adds a new connection to each file 3) for each pivot table (of each sheet in each file) that is linked to my data cube, I instruct the macro to replace the existing connection and replace it with...

In excel I make an Analysis Services connection to a data cube and create pivot tables, charts, and whatnot. I would like to be able to show a user how current this data is by showing them when the last cube processing time occurred. Making an analysis services connection to the cube in SQL...

Hello, Not entirely sure if this is possible... I'd like to somehow get a count of how many locations each player has scored 5 or more points in, details below. Basically there is an Olap cube set up which I do not have access to change. As a simplified example the data that I have is set...

Hi all, I'm new to PowerPivot but have got as far as easily using a design interface that writes the necessary MDX query that then pulls my data. My question: My query returns a large dataset with just one "metric" column (ROI Value). A lot of these rows have 0 for ROI Value, and so I don't...

Hi MrExcel Board! I'm hoping you can help me. I have a pivot table set up using an OLAP Query that I need to set up a custom MDX filter for time. I need it to dynamically update to pull the last 7 days worth of data for our subset. Can you help me figure out how to change these static dates...

Hello, I am trying to figure out the best way to get information out of an OLAP database. So the database is structured with these fields: [player], [team], [location], [points], [year]. There are many players which roll up into teams and many teams. There are 2 available years...

Hello, Is it possible to create a named set in MDX with OLAP data that contains a subtotal? So if I have a field now called "ID" and I know that some of the IDs are in group 1 and some are in group 2, I know I can create a named set to just show group 1 - it would involve the visualtotals...

Hello, I'm using Excel 2010 with an OLAP cube and I need some help with named sets via MDX. Is it possible to create a named set which basically groups and subtotals data? So if I have 10 employees and they are equally divided in 2 states is it possible to create a named set which is like...

Hello, So I'm using an Olap connection to create some pivot tables for some reporting. I know how to use the topcount function to create a dynamic set which will show, for example, top 30 salesman by sales volume this year. Topcount(Salesman, 30, SalesVolume TY) Then when I use that set...