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
2
votes
1 answer

AAS tabular model in DirectQuery mode performance benefits

Suppose you have 10 pretty big fact tables (each 50-100 GBs) that should be queried with Power BI. They doesn't fit into Azure Analysis Services RAM (reasonable price). So in order to use tabular model and AAS you have stay with the following…
VB_
  • 45,112
  • 42
  • 145
  • 293
2
votes
0 answers

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 execute the MDX query and click on Validate query. The…
Amruta
  • 701
  • 4
  • 15
  • 38
2
votes
1 answer

What is Proper DAX GROUPBY Syntax In Calculated Column in Power Pivot in Excel in Office 365

The following syntax entered into a calculated column formula for table Visits provided below in powerpivot in excel (office 365 version): =GROUPBY( Visits, [Patient Name], "First_Visit_Date", MINX(CURRENTGROUP(),…
Dary1
  • 23
  • 5
2
votes
1 answer

How to create a many-many relationship in Excel's data model?

We have an issue with power BI where we cannot use a many-many relationship to do calculations. End goal is Column E: Initial Table #1: Initial Table #2: When I use the Average Price per Area of Initial Table #2 on the Initial Table #1[Item] as…
Pherdindy
  • 1,168
  • 7
  • 23
  • 52
2
votes
1 answer

weighted average by category filtered by time period

I've got a headache which I would love some help with So I have the following table: Table 1: Date Hour Volume Value Price 10/09/2018 1 10 400 40.0 10/09/2018 2 80 200 2.5 10/09/2018 3 14 …
squib1996
  • 67
  • 5
2
votes
1 answer

DAX running total + starting value

I am fairly new to the DAX universe, but scrolling around I managed to successfully implement a cumulative (running) total, with a measure defined along this structure: …
Philip
  • 33
  • 5
2
votes
2 answers

PowerPivot DAX Max of two values

I have two columns and I need to extract the maximum value of those two for every row in my table. I have looked at the Max, Maxx and Maxa, but they all have input for just one column. How would I write following expression in a Calculated…
Martin
  • 353
  • 1
  • 6
  • 23
2
votes
2 answers

Deleting sheets and pivot tables in Excel with VBA

I'm trying to integrate the creation of a power pivot and pivot chart in VBA and have worked out a big part. However, I am encountering a problem which I cannot seem to overcome I create new worksheets where i create the Pivot and export it to…
Boolean
  • 99
  • 1
  • 2
  • 5
2
votes
2 answers

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…
Chris
  • 737
  • 3
  • 16
  • 32
2
votes
1 answer

PowerPivot Calculated Field calculating to nearest 10p

I have imported a dataset into PowerPivot. This dataset includes the following columns Month which is formatted as Date January 2018 Name which is a standard text field Height which is formatted as a decimal number Length also formatted as decimal…
MBrann
  • 223
  • 5
  • 23
2
votes
1 answer

Optional FILTER in CALCULATE in DAX

Similarly to the Basket Analysis DAX pattern model, I have 1 fact for Sales, 1 dimension for Product and an extra dimension for Filter Product. I want to use the Filter Product dimension to exclude products chosen by the user. I made it work with…
Victor
  • 1,163
  • 4
  • 25
  • 45
2
votes
1 answer

DAX: Need CALCULATE no to be affected by filters

I have a calculation in a dataset on PowerPivot: SUM:=CALCULATE(SUM([Data]),FILTER(ALL(Table[Month]),[Month]=[CURRENTMONTH])) Where: [Data] is numeric [Month] is a date [CURRENTMONTH] is a calculation that according to today's date indicates current…
Luis Rafael Gonzalez de la Cer
  • 29
  • 2
2
votes
1 answer

Show year to date sales for previous years

I am making a report that counts the amounts of offers, uniquely identified with SALE_ID, containing data from different products starting first of January 2015 ranging up to todays date (18/12/2017 at the time of asking). I am counting the amounts…
Dries Weytjens
  • 113
  • 1
  • 1
  • 6
2
votes
2 answers

Exclamation mark in sql string

I got string in a table like this 'ABC !! DEF' When I do a Select in SSMS I see them. When I try to add them in Powerpivot, they disapear. when I try in SQL to modify my view with a simple Replace(MyString, '!!',' ') the !! still there. I tried…
Marc Proulx
  • 33
  • 1
  • 4
2
votes
2 answers

PowerPivot Multiple OData Services Related

I'm playing around with OData with PowerPivot and I would like to know can I relate multiple OData services together? I don't want to have one huge OData service as I want to roll this out for all of our internal systems. I think one service would…
Mike Mengell
  • 2,310
  • 2
  • 21
  • 35
1 2 3
…
99 100