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
3
votes
2 answers

Getting DAX to return 0 instead of Blank

Using PowerPivot and DAX Countrows along with filter option with a column that has six possible expressions. (Excellent, Good, Acceptable, Not good, Poor and Blank). Post count, I am calculating Satisfaction percentage by Dividing Excellent and Good…
Setu
  • 33
  • 1
  • 5
3
votes
3 answers

Applying Multiple Value Filters in Excel Pivot

Using Excel 2013, I would like to apply a Top 10 Filter to a pivot and also apply a filter to an additional values field. Is this possible? I would like to filter off all 1's from Sum of Individual Claims Column, but still have the top 10 by…
Crow
  • 43
  • 1
  • 1
  • 6
3
votes
2 answers

Sorting in a specfic Order using PowerQuery

I have a table with 5 fields with a column containing the following: Medium High Low If I sort in ascending order the data sorts in alphabetical order: High Low Medium In the advanced editor how do I change the code in PowerQuery so that it sorts…
Dinks123
  • 145
  • 1
  • 14
3
votes
2 answers

Querying single data points from the Excel Data Model / Power Query (Get & Transform Data)

I'm using an up-to-date version of Excel 2016 (via O365 E3 license) and using Power Query / Get & Transform Data. I can successfully create queries and load them to the page. I have also successfully created Power Pivot reports. I would like to…
bkgraham
  • 110
  • 1
  • 11
3
votes
1 answer

Calculating an average of a DISTINCTCOUNT efficiently in Dax?

I'm trying to calculate a business-logic in DAX which has turned out to be quite resource-heavy and complex. I have a very large PowerPivot model (call it "sales") with numerous dimensions and measures. A simplified view of the sales…
RedVII
  • 483
  • 6
  • 11
3
votes
1 answer

How to use wildcard in Cubevalue formula in Excel?

I am trying to get the values with wildcard in Cubevalue formula(below) in excel. I am not finding any solution. =CUBEVALUE("ThisWorkbookDataModel","[Measures].[Sum of Bookings_Net]","[Dashboard_Data].[Level_1].[Karnataka_India]") I am trying to…
Punith GP
  • 690
  • 4
  • 11
  • 33
3
votes
2 answers

first 100 rows with DAX to retrieve Tabular Data

HOw to retrieve FIRST (N) rows while retrieving a Table from Power Pivot with Dax? What came to my mind is only to add an index column with Power Query and then to use FILTER() to enroll the SUMMARIZE() My…
Sergiy Razumov
  • 159
  • 2
  • 9
3
votes
1 answer

How to write a DAX measure based on both current row and slicer context?

Background Info: For each row in an international sales table, I need to retrieve the USD exchange rate at a specific date in the past, so that analysts can determine the impact of changes in exchange rates on sales figures. I will then use the…
Tricia
  • 55
  • 1
  • 1
  • 7
3
votes
1 answer

DAX: distinct count using multiple columns

My table includes bunch of duplicated sales information for individual sellers, and I basically created a column and a measure which flags users whether the view they have includes duplicated sales information. here is a very simplified example of…
user2669043
  • 97
  • 1
  • 6
  • 12
3
votes
1 answer

Why can I not share a Power BI report outside my organization?

Power BI doesn't allow users to share reports outside the organization. This is severely limiting and makes me wonder how I'm supposed to create a Power BI solution for my customers. Why is this? And what is the preferred method for setting up my…
PBeezy
  • 1,222
  • 2
  • 17
  • 26
3
votes
2 answers

Dynamic Parameter in Power Pivot Query

We are using Excel 2013 and Power Pivot to build modules that consist of several Pivot tables that are all pulling data from the same Power Pivot table, which queries our T-SQL data warehouse. In an effort to simplify and fully automate this…
cryocaustik
  • 439
  • 2
  • 8
  • 20
3
votes
1 answer

Converting multiple variables into values with excel pivot tables or power pivot

I have to use excel for this problem. I need to know how to create a pivot table from a very large dataset that's simplified structured looks like this: week 1 week 2 week 3 row 1 row 2 row 3 row 4 I need to use a slicer to use…
Lisle
  • 1,620
  • 2
  • 16
  • 22
3
votes
1 answer

Refreshing Excel Sheets using powershell

I have 10 excel different excel sheet in one folder named test. I want to refresh the data connection and data in the pivot table using windows power shell script. The process is using for each loop open each file and then in the file choose…
Justin
  • 393
  • 1
  • 7
  • 21
3
votes
2 answers

Count of Rows Based on Their Most Recent Value in Excel PowerPivot Using DAX

Is it possible to get a distinct count of rows based on a field's most recent value in a PowerPivot table using DAX? I have a transactional table that tracks professionals with hire and transfer dates. I would like to get a count of the…
dotNetE
  • 616
  • 1
  • 6
  • 27
3
votes
1 answer

Excel 2010 PowerPivot - Using a PivotTable result as the input for a second PowerPivot

Suppose I had the following table in a SQL Server DB I wanted to do an Excel PowerPivot (Excel 2010 version) on: Date: Grp: Value: 1-Jan a 1 1-Jan a 2 1-Jan b 3 1-Jan c 4 2-Jan a 5 2-Jan b 6 2-Jan a …
John Bustos
  • 19,036
  • 17
  • 89
  • 151
1 2 3
…
99 100