All Questions
Tagged with powerpivot mdx
43 questions
1
vote
0
answers
28
views
Using PowerPivot to compute test coverage
For my current project I am using Excel and Pivot Tables quite often. Now, I have seen the nice feature PowerPivot in Excel and I am thinking to use it in my project.
Especially, I want to use ...
- 6,064
0
votes
0
answers
29
views
Define MDX Update Allocation Method - Excel What-If Calculate Change
I have setup up a multi-dimensional data model in SSAS which is embedded in Microsoft Excel as a Pivot Table. In Excel I want to use the OLAP what-If analysis functionalities.
Screenshort Excel ...
1
vote
1
answer
977
views
Excel Grand Total in PivotChart from Data Model
Goal
Generate a Grand Total column (called All below) in a PivotTable to be displayed in a PivotChart in Excel 2016.
PivotTable Current
Sum of DailySales
Column Labels
Row Labels
North
South
1/2/...
- 333
0
votes
0
answers
655
views
cubeset function with 2 conditions
I am using cubeset function with one condition that is giving me the desired set:
=CUBESET("ThisWorkbookDataModel";"[ORDERS].[CLIENT].&["&B2&"]*[ORDERS].[ORDERSID]....
0
votes
0
answers
161
views
excel cubset function to get 2 columns set
I am trying to use the cubset function to get a set of 2 columns. The data table is something like bellow:
TABLE
+--------+-------+-------+
| CLIENT | PRODA | PRODB |
+--------+-------+-------+
| ...
1
vote
0
answers
41
views
Maintaining Series Format in Chart: VBA Event for MDX Legend Changes
Is there a solution through VBA or by editing my MDX set, or anything else, to apply my Series formatting and make my measure/series colors consistent, even when isolated?
Or in other words, when I ...
- 11
0
votes
0
answers
123
views
How to fix issue with MDX - Power PIVOT returning numbers as text
I am using MDX query designer in PowerPivot to drag the data as well as to create calculated columns.
I want to combine two fields:
one shows one side of accounting account, another opposite side of ...
- 1
0
votes
1
answer
93
views
SUM two rows in PowerPivot Pivot table MDX Balance Sheet Subtotal for Liabilities + Equity
Really new to MDX and have researched for hours but haven´t found the way to make it work.
Objective: Sum two rows (liabilities-pasivos and equity-patrimonio) for a pivot table that represents a ...
- 5,696
1
vote
0
answers
372
views
Export Excel PowerPivot recordset as offline cube
I have a PowerPivot model that has Power Query as a source. Is it possible to create an offline cube from this?
I've tried creating a pivot table out of the PP and then using the pivotttable....
- 165
2
votes
0
answers
245
views
Connection String not valid - MDX query error in Power Pivot
I am trying to execute MDX query in Power Pivot.
While creating connection I have selected Model.odc. The connection string gets validated successfully by Test Connection.
In further step when I ...
- 731
1
vote
1
answer
1k
views
MDX: filtering zero values out
I have a dataset that has a lot of 0 values for the measure so I would like to filter them out in my query. However I don't seem to be able to do it.
It either doesn't work or I crash the query. I ...
1
vote
1
answer
238
views
MDX Expression in Excel Power pivot using = operator
I am new to MDX, trying to create report in excel(Power Pivot OLAP) using
tabular model cube.
I need convert below DAX expression into MDX expression,
DAX Experssion
CALCULATE(
sum('Finance ...
- 11
0
votes
0
answers
163
views
MDX Function (Excel PowerPivot) to Exclude Non Numeric Values from DB
I am quite new to MDX and I am trying hard to write a query that allows me to retrieve only numeric values.
My query at present is:
SELECT NON EMPTY {ISNUMERIC([Measures].[Average Booking Window])} ...
- 135
0
votes
1
answer
79
views
Filter() is too slow on PowerPivot Query
I have a question regarding MDX code, I am trying to filter out values from a measure in my cube that contains several account numbers, however, the performance is too slow and I haven't been able to ...
3
votes
3
answers
13k
views
CUBESET() function in Excel with Multiple criteria
I'm trying to create a CUBESET function in Excel, but I don't know how to filter it using multiple criteria within the same dimension. This is what I have so far working with one criteria.
Example 1:
...
- 788
1
vote
0
answers
609
views
DAX Create a measure which references the previously calculated value of itself (previous period)
I would like to create a measure in PowerPivot which references the previously calculated value of itself in the previous iteration.
For example: *[Planned Backlog] n = [Planned OIT] period n + (...
- 19
1
vote
2
answers
668
views
Get higher-level element with CUBEMEMBER in Excel Powerpivot
I have a Chart of Accounts, loaded in PowerPivot based on a SQL query on a database table; the connection is named "ThisWorkbookDataModel" and the table in PowerPivot is named [Accounts].
The Chart ...
- 23
2
votes
2
answers
877
views
MDX syntax for DAX measures calculated with AVERAGE()
I have the following expression in Excel that works fine.
=CUBESET("ThisWorkbookDataModel",
"TopCount(
[ProductBV].[Product Name].Children,10,
sum(
(
[Calendar].[Week Ending].[All].[1/6/2013]:[...
- 788
1
vote
0
answers
591
views
How to create a ranged frequency table using Excel Cube functions
What I want is to create something like this (see image) in Excel that pulls in the frequency of my chosen category from a table within my PowerPivot data model. The output would also need to be ...
- 788
1
vote
1
answer
784
views
Excel PivotTable result vs executed MDX query result
I am executing an MDX query retrieved from Excel PivotTable connected to an OLAP Cube in SQL Server Management Studio. I have retrieved the MDX query using SQL Profiler while refreshing it in Excel.
...
- 180
0
votes
1
answer
449
views
MDX Case statement
I am trying to use a MDX calculated measure to do a case statement on a column.
I am using this code:
Case [hist].[title] when "Mr" then "Test1 "Else "Test2"end
when I save it it sets everything to ...
- 47
0
votes
0
answers
46
views
How to get Total and Percentage in MDX - Power Pivot
I want to get the total per year and the percentage in MDX from this dataset... any ideas, thanks.
Example
- 141
0
votes
1
answer
65
views
Country-Currency MDX Mapping
I'm quite new to MDX and I'm facing an issue I'm unable to solve, even after reading documentations. I have a database that contains values from all countries in all possible currencies (like e.g. ...
- 695
0
votes
1
answer
1k
views
Cube function in Excel using Powerpivot to display specific row values in worksheet
This is a highly specialized question and I have searched the web to no avail for an answer with no luck. I am working with Powerpivot that is connected to a SQL database to create essentially ...
- 55
0
votes
1
answer
566
views
Filtering a CUBESET using a different variable without hierarchies
I'm trying to produce a cubeset that only returns members that correspond to another field in my Powerpivot dataset. Because my organisation is so behind we're using an old version of Powerpivot (SQL ...
- 223
2
votes
2
answers
2k
views
Power pivot named MDX field
I have a PowerPivot Data Model in Excel 2013. There are several measures that I have grouped into a named set using MDX - something like this:
{[Measures].[Sum of Value1],
[Measures].[Sum of Value2],...
4
votes
1
answer
1k
views
UNION multiple MDX queries in SSAS (powerpivot)
I have some sort of difficulties trying to join 2 MDX queries together. When running them separately they work fine. The script below
WITH
MEMBER [Measures].[ParameterCaption] AS
[...
- 403
0
votes
1
answer
32
views
I'm connecting a PowerPivot query to a MS Analysis server cube. double record
I'm connecting a PowerPivot query to a MS Analysis Server Cube. The query result i get contains double records for a single dimension (personnelnr). This because of a name change during a day in the ...
- 1
0
votes
2
answers
58
views
MDX Query Can't connect Fiscal Month/Quarter to my Measures
I've been building an MDX query using excel's powerpivot. I connect to my cube, drag and drop Measures /Dimensions and my query has been working just fine. Up until I try to pull different dimensions.
...
1
vote
0
answers
358
views
How to have dynamic interval in DAX/Powerpivot?
Dynamic Band in RDBMS
Our challenge is to reproduce the below example (RDBMS) but leveraging Microsoft SSAS Tabular Model.
We have value in a column and we need to group them, so literally we have ...
- 133
0
votes
1
answer
1k
views
MDX Expression : Calculated Member
I am trying to create a Calculated Member on a Microsoft Analysis Services Cube.
There is an existing Dimension [Projects][Project Number - Name] which has members along the line of "12345 - ...
- 930
0
votes
2
answers
709
views
Powerpivot: create a data model from a DAX query
Is it possible to create a data model (on which I can run a DAX query) from the output of a DAX query? (both tables being autoupdated from a db backend, not static - so no copy paste or convert to ...
- 696
0
votes
1
answer
597
views
List Dimension Members if selected date falls between Start Date and End Date in fact records SSAS MDX
I have a fact table that contains invoice line items, and since these line items are subscriptions, there is a Start Date and an End Date involved
LineItem Customer Product OrderDate ...
- 163
0
votes
1
answer
478
views
MDX results to Excel in Power Pivot
I don't know ABCDs of MDX. I have this query that was handed over to me by my predecessor and that it is needed only once in a year! That time of the year happens to be now! The query runs and returns ...
- 61
1
vote
1
answer
518
views
Powerpivot with SSAS cube
We have an SSAS cube for our patient data. Instead of recreating the entire cube in powerpivot with all the measures and calculations, I am looking to use the cube itself and add onto it. I am just ...
0
votes
1
answer
2k
views
MDX - Last 90 Days
I'm trying to put togethr an automatic MDX filter in PowerPivot. I have a dimension:
Essentially I need to write a MDX statement where it will pull all records with the Year Month Day >= 90 days ...
- 287
0
votes
1
answer
137
views
Powerpivot Graph 'Region' Total Sales vs 'Employee' Total sales in a period of time
I want to create a column chart that will show: Total Sales, total revenue for a selected employee vs. total sales, total revenue of his Region. I mean, one employee belongs to one Region ... And I ...
2
votes
2
answers
6k
views
Force mdx query to return column names
When connecting from powerpivot to SSAS, I got the following problem - if for some reason no rows are returned by mdx query, no column names are returned either and powerpivot gives an error. When ...
- 23
1
vote
1
answer
5k
views
Calculating a Ratio using Column A & Column B - in Powerpivot/MDX/DAX, not in SQL
I have a query to pull clickthrough for a funnel, where if a user hit a page it records as "1", else NULL --
SELECT datestamp
,COUNT(visits) as Visits
,count([QE001]) as firstcount
,...
- 86
2
votes
2
answers
2k
views
Filter Dimension values
I have a master Customer dimension (that I don't maintain). My fact table includes customer info. When I compile the cube, everything looks good, by browsing the cube I see my fact rows with only ...
- 342
1
vote
2
answers
3k
views
Powerpivot: Joining historicals for database and forecast from a calculated linked table in a single pivottable
I noticed a few people have tried to do similar things that I am asking below. I may have a suggestion in my answer as to how I fix it so i'll ask the question first.
I have historical data that I ...
1
vote
1
answer
859
views
Problems when using RANKX on PowerPivot
I'm trying to use the RANKX funcion to Rank some sales, my table looks like this
ProductID | ProductTotal |
I want to have a third column called ProductRank that will rank the The products depending ...
- 119
0
votes
1
answer
761
views
MDX -> Query works in CubeBrowser not PowerPivot -> "unable to cast object of type 'system.dbnull' to type 'system.string'"
Pasting query into powerpivot produces the following error but works in CubeBrowser. :
"unable to cast object of type 'system.dbnull' to type 'system.string'"
Here is my query.
SELECT NON EMPTY { [...
- 3,356