Stack Overflow
Stack Overflow

Questions tagged [powerpivot]

An in-memory OLAP cube that can for instance be installed and used in Excel. It allows for fast analysis of huge datasets.

PowerPivot is part of the Microsoft Business Intelligence suite and uses SQL Server in the background, but it is geared towards the data-savvy business oriented person. It allows you to easily load data from different sources, put them in a relational database-like format and create relationships between the tables.

When all data is loaded, an OLAP store is created in the background and you can do real time analysis by selecting and filtering to your heart's content.

Also included is a powerful query language called DAX (Data Analysis eXpressions) which use an Excel style syntax to allow the user to perform complex calculations.

Power-Pivot datasets can also be served as part of SharePoint and Microsoft has even put at least one all-in hardware appliance on the market in collaboration with hardware vendors.

The following links provide useful reference information:

Further interesting PowerPivot articles can be found here:

1828 questions
4
votes
2 answers

Use datamodel data for cell formula

I have an excel file that has a table imported from a txt in a sheet (using New Query). From that table I created a pivot table and some formulas like for example MAX(). I was told that for large files it is better to add the info to the data model…
mmtzdesalnas
  • 41
  • 1
  • 2
4
votes
1 answer

Power BI - Find closest location based on Lat/Lng

I am new to Power BI and DAX, so I hope you can help me. I have two tables without any relationship: Table A contains lat/lng and date of tracked positions. Table B contains lat/lng and names of all stadiums. I want to find the closest stadium near…
jonas9495
  • 43
  • 1
  • 4
4
votes
2 answers

Excel 2016- Load power query directly into power pivot

Is there a way to load power query data directly into power pivot without creating an excel table as an intermediary step? All the examples I've found reference Excel 2010 and 2013. Although the instructions are similar, it does not work in 2016.…
machump
  • 1,207
  • 2
  • 20
  • 41
4
votes
1 answer

Unable to capture errors in VBA when refreshing a query from PowerQuery

I have a workbook with several PowerQuery queries to other workbooks. This workbook is updated annually at each FY - meaning the queries need to point to new workbooks every year. This can of course be done manually, but there are a lot of queries…
Jaspos
  • 365
  • 1
  • 4
  • 17
4
votes
1 answer

DAX: Averaging multiple % Columns

I'm new to Power BI and Dax, having some difficulty with the below scenario. test a b c d AVERAGE aa 51.97% 46.61% 49% I have 4 columns, a-d, and I simply want the average of the 4 columns…
ScotCal
  • 65
  • 1
  • 5
4
votes
2 answers

Optimizing Dax & model for "where date between" type queries

I am building a model to allow reporting on two seperate datasets, for this example we'l say a Students dataset & a Staff dataset. The datasets are pretty seperate and the only real link between the two is Date, so from a model perspective, there is…
JD_Sudz
  • 194
  • 1
  • 12
4
votes
2 answers

SUM of dynamic Columns in PIVOT table in SQL Server

I have a Dynamic PIVOT query in which Columns are dynamically generated. My table: ATTENDANCE_MASTER Contains: ID, Stud_id, ATT_DATE, PRESENT which stores data like: ID Stud_id ATT_DATE PRESENT 1 1 2015-08-1 1 2 2 2015-08-1 …
QuaBizIT
  • 67
  • 1
  • 1
  • 8
4
votes
1 answer

DAX Dynamic Total with Power View

I am attempting to graph, via Power View, the sum of all account balances at various points in time, so that I can see how the total is changing. I do not have transaction history (add / subtract), only 'Balance' at a specific point in time for each…
Dave Hughes
  • 41
  • 1
4
votes
1 answer

Application-defined or object-defined error with Excel Slicer

In a KPI dashboard on course attendance, I have a dropdown list with course names. A user picks a course and a macro must select the picked course in a slicer (connected to a cube). When using the macro recorder, I see that the recorder uses a kind…
FredC
  • 93
  • 1
  • 3
  • 8
4
votes
1 answer

Using VBA To Add or change Power Pivot data connection

Even after searching and trying for a long time I am not sure whether it is possible it or not. I have a PP model using SQL source. Let's say command is: select * from FactInternetSales where CustomerKey = 11001 and I have filter, 'CustomerKey'…
Prakash Gautam
  • 502
  • 4
  • 13
4
votes
1 answer

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 [Main_Incidents].[Priority].CurrentMember.Member_Caption …
PeddiePooh
  • 403
  • 8
  • 17
4
votes
1 answer

SSAS Tabular / PowerPivot Date Filters

I am trying to design a SSAS 2014 Tabular Cube that has easy to use custom date filters. For example, I want users (in excel for example) to be able to select a custom date range such as "This Month", "Last Month", "This Year", "Last 12 Months",…
cmn
  • 93
  • 2
  • 8
4
votes
7 answers

Analysis Services with excel as front end - is it possible to get the nicer UI that powerpivot provides

I have been looking into PowerPivot and concluded that for "self service BI" and ahoc buidling of cubes it has its uses. In particular I like the enhanced UI that you get from using PowerPivot rather than just using a PivotTable hooked up to an…
AJM
  • 32,054
  • 48
  • 155
  • 243
4
votes
2 answers

Change connection string in MS Excel 2013 powerpivot

I am working on same solution where i need to update connection strings of multiple files in Bulk.But , Its working for MS Excel 2010 and not for Excel 2013. Exception Result : "Exception from HRESULT: 0x800A03EC" Code Snippet…
Avinash Mishra
  • 41
  • 3
4
votes
1 answer

DAX / PowerPivot query functions to spread aggregated values over time period

I’m trying to work out the DAX expression [for MS PowerPivot in Excel 2010] to evenly distribute the sum of a value across the range it’s applied to, and re-sum that up for a given time span/period. It’s trivial to cross-apply in SQL server, though…
iivel
  • 2,576
  • 22
  • 19
1 2
3
…
99 100