Browse by Tags
All Tags »
Excel 2010 (
RSS)
A bit more information regarding the new CALCULATE function in DAX. I’ve described what this function does in a previous post , and I want to expand on how to treat this function in a PowerPivot report. Consider the following report: We have Order Quantity sales ([Order Quantity Total]) and two calculated measures, which we are going to discuss shortly. We have the promotions dimension in the Y axix and 2 slicers: Product Category Name and Calendar Year. The DAX syntax for the calculated...
I’m getting more and more involved in designing PowerPivot models and want to share a bit of DAX tips in the coming posts. I’ve already touched on DAX in previous posts and in this post I’d like to discuss the new CALCULATE and CALCULATETABLE functions and their uses in a model. DAX is a powerful expression language and one of its main differences from the expression language we know in Excel is the fact that we can reference entire tables rather than cells in a spreadsheet. When we need to look...
In this 4’th instalment of the PowerPivot blog posts series we’ll take a quick look at PowerPivot internals. Firstly, what you need to understand is that a PowerPivot database is similar to an Analysis Services cube, but it is not exactly the same, certainly not in the way it stores data internally within the Excel file. There is an easy way to see what is inside an Excel file (thanks to the crowd at the recent Israeli BI User Group meeting…): simply change the Excel file extension from xlsx to zip...
PowerPivot is a combination of Excel and Analysis Services, enabling users to create complex and high-data-volume reports and enjoy the flexibility and usability of Excel alongside the high-performing SSAS engine. In this post I’ll be taking a closer look at a couple of DAX expressions. The DAX expression language is a special expression language which was added to the PowerPivot the Excel 2010 products, and enables the user to query multiple tables (as opposed to the traditional Excel expression...
Continuing the PowerPivot series, I’ll now attempt at creating my first PowerPivot report. After opening Excel, first thing we need to do is getting ourselves a data set to work with. To do so, I’ll click on the “data” tab and choose “From Other Sources” button and connect to my local SQL Server instance, to the “AdventureWorksDW2008” database. Running through a familiar set of wizard screens (similar to the Excel 2007 wizard), where I’m prompted to choose a database, and get the option to choose...
With so much talk about PowerPivot , “BI for the masses”, collaboration and, of course, world peace, I decided I’ll finally give PowerPivot a test run and see what the hype is all about. Attending the PASS summit a couple of months ago, this was by far the most talked about feature in the upcoming SQL Server 2008 R2 and Office 2010 products and it seems Microsoft is really excited with this new add-in. So, in the coming 2-3 blog posts I’ll try to describe how this tool works, and no better place...