• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

PowerPivot Tutorial

chirayu

chirayu

Well-Known Member
Hi Guys,

I found this link while searching Google for Free PowerPivot Tutorials.

I've gone through all the sections and its relatively easy to work through and pretty comprehensive (as well as free lol), so I thought I'd share the link here.

Also don't mind the fact that its listed under the SQL Server 2012 help section on the site because they haven't referenced it at all during the tutorial. Its a pure Excel guide with sample file link in the Intro section.

FYI certain sections seem to be dated as certain functionality described doesn't match the UI so you might need to figure out some bits yourself, but that's not too difficult to do.

If anyone knows of any other resources for PowerPivot then feel free to add to thread. Link below

https://technet.microsoft.com/en-us/library/gg413497(v=sql.110).aspx
 
Hi chirayu,

you did use Microsoft and for free in a single post, you noticed:)?

Besides the reference you have given, I personally learned a lot about PowerPivot via the youtube channel "ExcelIsFun" by Mike Girvin. All his tutorials come with a free sample file (start and finish) to follow along. Clearly he has his own style, some will like and others not, but from a didactically point of view he aces it according to me. Clearly beginners level is a targeted audience in many videos.
Each of these tackle a distinct topic which makes learning rather easy. There are about 59 of them on PowerPivot, but he adds new ones. And he often builds in comparisons with other solutions, where possible. On the plus side, the videos are not limited to PowerPivot. He talks about everything that can be accomplished with Excel, except heavy VBA.
Whenever I get the change, I refer people to 3 sources: MrExcel, Chandoo and Mike Girvin.
I hope it is ok to have shared this.
 
Thanks for the resource. The only thing I don't like about PowerPivot is the fact that you can't use Perspectives unless you have SharePoint and Excel Services
 
This part, isn't entirely accurate.
PowerPivot allows you to import, merge, analyze and
visualize large data sets
Click to expand...

PowerPivot is more of data modeling tool that allows you to define data relationships and calculations.

PowerQuery should be used for importing, transforming and merging data.
 
Hi Chihiro, that is true, but you can import directly in PowerPivot if your source data does not need extra transformations. And merging, (not appending), is even not required in PP, since the data model relationship take care of it.

Hello, chirayu, I'm a bit surprised you ask me for a review. Flattering, but I have not showed any expertise about the matter here. I appreciate the vote of confidence though....
I already went quickly trough the presentation you made. I like the setup and that you refer to things that are similar in Excel. Like table references. I did the same in to introduce the topic to colleagues. It really lowers the bar for the audience.
I don't really know what the exact objective and targetted audience is of your presentation. Even for a simple summary I would be tempted to add a few more things.
- If you import from access db, you might simplify the import by auto detecting the related tables.
- "A single table cannot have multiple relationships": I believe this can be misunderstood. Not on the same key, but you can have links to more then 1 lookup table on different columns.
- Best practice tip for DAX: make explicit DAX formulas (not via the standard pivot way, by dropping metrics in the value sections)
- For DAX you could add that some are very similar to know Excel functions: same function types, same arguments. Others are very specific DAX and allow to do "impossible" calculations: filter and time intelligence types.
- Basic evaluation contexts (row/filter) could be explained, but here it can become confusing.
- In sources, you can also add some books.

All of the above is only meant as a kind suggestion. (Language can be in the way sometimes :))
Greets
G.
 
but you can import directly in PowerPivot if your source data does not need extra transformations.
Click to expand...

Yes, but I've seen too many people make mistake of trying to apply Lookup operation, data cleaning etc in PowerPivot to care to count. ;)

Often resulting in data model that's littered with unnecessary calculated columns. Which causes severe performance issues at times.

I always recommend two step procedure. First in PQ, ensure proper data structure (data type, no blank row, get rid of unnecessary data etc). Then load to data model and build relationships using PowerPivot (In PowerBI, this is ensured by default).

But I digress.
 
We agree Chihiro. Now I'm a promoter of both PP and PQ at work and outside. But I never had the opportunity to be in a job where I actually needed those tools. And even now, I'm in a function where I do not need Excel to begin with.
All I know is textbook stuff, missing out on the so important real life experience. And that shows, when I compare my replies to others; be it ninjas or other experienced member.
I'll be the first to admit, and I even mentioned it in my profile, to take my advice with caution. I just like Excel, it has been a trustworthy companion in my career. But I'm getting off topic here.
Back to the topic then. Yes, PQ for all that is required to have clean data on which to build the model and unleash tremendous calculation power with DAX, as Matt Allington would say. Exactly like you explained, backed up by your observations.
 
Hi Chirayu ,

You say :
DAX formulas are essentially the same thing
Click to expand...

Do you seriously believe that ?

DAX formulas are absolutely different from any Excel formula.

Please make this absolutely clear to anyone who wishes to learn Powerpivot.

I am giving the following DAX formula , and I would appreciate anyone who can understand it and then give the equivalent Excel formula !

//this DAX statement was formatted using daxformatter.com

CI =
VAR thefirstdate = FIRSTDATE ( ALLSELECTED ( 'Calendar'[Datevalue] ) )
VAR thelastdate = LASTDATE ( ALLSELECTED ( 'Calendar'[Datevalue] ) )
VAR thestopdate = IF (
MAX ( 'Calendar'[Datevalue] ) > thelastdate;
thelastdate;
MAX ( 'Calendar'[Datevalue] )
)
RETURN

CALCULATE (
SUMX (
ADDCOLUMNS (
GENERATE (
FILTER ( VALUES ( 'Calendar'[Datevalue] ); 'Calendar'[Datevalue] = thestopdate );
FILTER ( ALL ( 'Investment' ); 'Investment'[Inv_Date] >= thefirstdate && 'Investment'[Inv_Date] <= thestopdate ) ); "Inv_Compound";

VAR theInvDate = [Inv_Date]
VAR theInv = [Investment]
RETURN theInv * PRODUCTX ( FILTER ( ALL ( 'InterestRates' ); 'InterestRates'[DateValue] >= DATE ( YEAR ( theInvDate ); MONTH ( theInvDate ); 1 ) && 'InterestRates'[DateValue] <= thestopdate ); IF ( DATEDIFF ( DATE ( YEAR ( theInvDate ); MONTH ( theInvDate ); 1 ); 'InterestRates'[DateValue]; MONTH ) = 0; 1; 1 + 'InterestRates'[InterestRate] ) )
Click to expand...
For those who wish to see this in its formatted version , the file is attached.

Narayan
 

Attachments

  • DAX Use TableIterators and EARLIER to compute the compound interest.pdf
    111 KB · Views: 13
Last edited:
Hi Naranyan,
Obviously DAX are very different from excel functions: the technology is incomparable. I understand from Chirayu he is making a simple document to introduce the topic. And some do feel familiar: SUM, AVERAGE, COUNT, IF,... And you can use math operators and comparison operators much like in Excel. This can be leverage for people towards using DAX without being "afraid"...
The formula you display is far away from introductory level: including variable definitions, which to my knowledge, has only been introduced rather recently in DAX for PP.
Hence my earlier suggestion to Chirayu to explain the basic concepts on how DAX works with context. Simple at first, but rapidly it messes around with your head if you are not careful.
I used these references in an introduction workshop about PP at work, Chirayu.
- DAX tip card from Rob Collie's site (attached, it is a free download)
- +250 DAX examples: http://microsoftproducttraining.com/secured/Books/PracticalPowerPivotDAXFormula2010.pdf
- Extract from Definitive guide to DAX: https://ptgmedia.pearsoncmg.com/images/9780735698352/samplepages/9780735698352.pdf
 

Attachments

  • DaxFormula_TipCard.pdf
    330.8 KB · Views: 6
Hi ,

My point was that people who get into Powerpivot should know what they are letting themselves in for.

Getting to know just the basic functions such as SUM , AVERAGE , COUNT , IF in Powerpivot is really not going to get that person anywhere ; he / she would be much better off learning more Excel so that they can leverage the power inherent in Excel.

Making things simpler than they are is doing a disservice to the learner , since at some stage they will feel that they have not really used the power of Powerpivot , and at the same time they will feel that the next step is too high to climb.

Can anyone explain the fourth dimension by saying that it is just one more than 3 ?

The real power of Powerpivot comes when you are dealing with complex and voluminous data , which you cannot understand with the power of Excel. To handle this power usefully , you need to come to grips with row context , filter context and the more intricate DAX functions.

If you take a look at the forums , you will find there are probably a hundred good Excel forums ; if you look at Powerpivot forums , there are probably a dozen.

A majority of the Powerpivot books are by Marco Russo and Alberto Ferrari ; even those that are by other authors , make extensive use of either the books of the above two authors or the web pages authored by them.

If you go through some of the forums by other experts , you will see that even the experts are in tiers ; at the top there are only Russo and Ferrari !
Below that layer is a handful of other experts such as Rob Collie.

Every other expert is below these two layers.

When this is the situation in which experts find themselves , how is a common man going to come to grips with this vastly complex tool ?

It is a difficult task , and the sooner this is made clear to a learner , the better.

Narayan
 
Hi Naranyan,

I mostly and almost completely agree with you. But really? Didn't you learn in primary school that 1+1=2? To later move on to understand abstract algebra? Finally perhaps even master statistical theories? If a teacher early on would have told you about the complexity of the latter, would that have motivated you? I honestly doubt that. And I would not call it a disservice. That's why I support Chirayu's initiative.

Even the Italians would agree, that mastering DAX is a process that takes time, perhaps even years. Roberto Ferrari starts his seminars on DAX with =SUM(fSales[SalesAmount]), followed with the question: Is this total sum of Sales? Obviously he builds up towards the finesses of DAX where the real power lays: filter (context), time intelligence functions and the lady calculate. Which no man will ever completely understand. And those are his words.

But by no means, and I apologize for not being clearer, I was suggesting that the simplest of DAX functions are the ones to know. It is a gently way of getting acquainted with the syntax and basic concept, though. And yes, professional learners must be informed that with DAX comes great power, but equal responsibility. You are absolutely right about that. Indeed, if you fail to understand evaluation context, then stay away from PP and DAX.

Excel has been around since the 80's and PP only since 2010. So it does make sense that there are less knowledgeable (wo)men out there. The more people get to know PP, the more chances new inspiring experts may rise to the occasion. At least that is my naive hope.
 
GraH - Guido said:
- If you import from access db, you might simplify the import by auto detecting the related tables.
- "A single table cannot have multiple relationships": I believe this can be misunderstood. Not on the same key, but you can have links to more then 1 lookup table on different columns.
- Best practice tip for DAX: make explicit DAX formulas (not via the standard pivot way, by dropping metrics in the value sections)
- For DAX you could add that some are very similar to know Excel functions: same function types, same arguments. Others are very specific DAX and allow to do "impossible" calculations: filter and time intelligence types.
- Basic evaluation contexts (row/filter) could be explained, but here it can become confusing.
- In sources, you can also add some books
Click to expand...

- I thought that was an inherent algorithm so I didn't need to explain it but I see what you mean :p
- Updated that to say "Unless on different fields"
- The pivottable thing was just a reference to explain what DAX calulcation are :p (hopefully no one tries to do it that way lol)
- Yup that's why left out
- Added the links u gave me

Also audience is basically anyone new to PowerPivot and has no clue how to use it or what its for.

To be honest I created the document because yes there are books & links & tutorials out there but I didn't find them to be simple enough. I myself just started learning PP/ PV. Haven't moved to PQ yet. So thought I'd draw a doc up that I would have liked to have when I first started learning it.

There's one thing I would like to learn though - the upload to sharepoint & export to dynamic PPT.

FYI updated file
 

Attachments

  • PowerPivot for Excel 2016.pdf
    432.2 KB · Views: 10
Yes, nice initiative, I thought that would be the audience. I did something similar (on request of colleagues, but only 2 or 3 started using it out of 12 I think. Because of DAX... But I insisted on understanding context. I also think that is vital.)

To make people understand PP is way different from Excel, I used the image (based on what Rob says in one of his books) that Excel is like an F16-plane and PP is more like that spaceship from Star Trek (real pictures used btw).
- Engine = vertipaq (3s-rule in mind, it calculates at lightning speed)
- high capacity = in tabular + data model + designed for big data
- heavy armory = DAX to do "impossible" calc, even time travelling
=> Boldly go where no man has gone before... ;-)

Uploading to SP is not (yet?) possible at work, so I don't have a clue about that. The SP site must be set-up for it?

Export to PPT. Hmm, ... again, clueless. Never investigated it...

Good luck with the knowledge sharing!
 
Upload to SharePoint from PowerPivot data model is bit annoying to set up.

Often will require your SharePoint Admin involvement to set up permission levels and for you to set up your workbook ahead of process.

Frankly, I don't like working with SharePoint as development tool and only use it as convenient file storage for remote access to data set via PowerQuery (much the same way as OneDrive). Or as quick survey tool.

Have a read of link.
https://support.office.com/en-us/ar...er-pivot-b6829d84-12fe-43c7-9e21-bd657a36679e
 
Can't say I have. I've learned DAX mostly by trial and error and still learning.
I'm sure if you post your question someone will be able to answer. If not here, then at PowerBI community (Desktop section).
 
Hi Naranyan, I've read it. But... I cannot tell if I understood it well enough to answer your questions. :)
Recently I came across this post by the Italians via LinkedIn.
https://www.sqlbi.com/articles/the-definitive-guide-to-allselected/?platform=hootsuite
ALLSELECTED is a powerful function that can hide several traps. This article is an in-depth analysis of the behavior of ALLSELECTED, explaining shadow filter contexts, what they are and how they are used by ALLSELECTED
Click to expand...
And
If you have read the first edition of the book, “The Definitive Guide to DAX”, consider this article an erratum of the book. Indeed, we are able to be a lot more precise and clear here, than when writing the book. We apologize for that.
Click to expand...

EDIT: I'm with Chihiro...
 
Chihiro said:
Can't say I have. I've learned DAX mostly by trial and error and still learning.
I'm sure if you post your question someone will be able to answer. If not here, then at PowerBI community (Desktop section).
Click to expand...
Hi Chihiro ,

I also believe in learning by trial and error ; the problem with Power Pivot is that when you go wrong and your formula does not give you the result that you are looking for , you have no idea why it is wrong ; by trial and error you can possibly find out the correct formula , or at least what works , but finding out why something did not work is difficult , at least partly because there is no formula evaluator in DAX , using which you can step through the entire calculation process.

I don't wish to ask in Power Pivot forums , because the questions I have are ones of concept. If I had a problem I wanted solved , I would probably post it in a PP forum , but these are questions which need explanation , and a lot of going back and forth , and I doubt that such questions will be entertained in other forums.

If there are other learners who have also gone through a similar process of learning , it will make it easier for me to understand their explanations.

Anyway , I will post my questions as and when I have them , and let us see how it goes.

Narayan
 
but finding out why something did not work is difficult , at least partly because there is no formula evaluator in DAX , using which you can step through the entire calculation process
Click to expand...
That would be nice, wouldn't it!
You could ask the Italians... I've seen them replying from the theoretical perspective.
I'm afraid to ask you what your questions are...
 
Yes, there is no easy way to evaluate DAX.

You could try the DaxStudio tool. It takes some getting used to, but offers ability to trace query plan, extract intermediate tables etc. Although it's more useful as performance optimization tool, using query plan trace.

Marco Russo, one of the author of the book in your link is contributor to the project.
 
Do you seriously believe that ?
DAX formulas are absolutely different from any Excel formula.
Click to expand...

Firstly the confession. I am an absolute newbie as far as PP is concerned; I even failed to find 'Create Linked Table' by opening the PowerPivot Window and looking there for import from Excel Table :-(. Hence I am here to learn.

On the other hand, looking at the snippet of DAX, it was vaguely readable. I wonder whether the difference from Excel formulas is exaggerated by the way in which end users develop solutions in Excel? For example, if one starts an Excel solution with potentially overlapping ranges 'dateRange' (the calendar) and 'datesSelected', then one could define further names 'theFirstDate' by
= MIN(dateSelected)
and 'theLastDate' by
= MAX(dateSelected)

The name 'theStopDate' would then apply to
= IF( MAX(dateRange) > theLastDate, theLastDate, MAX(dateRange) )

I may well have got the wrong end of the stick but my feeling is that it is the standard use of Excel and not the functionality of Excel itself that makes DAX look quite so alien.
 
Hi ,

I think DAX is actually an alien beast.

In Excel , any formula , however complex , will return a single result ; even if you have a multi-cell array formula , you can predict that the formula will return a certain set of results.

In DAX that is not the case.

Every DAX formula can return multiple results , depending on the row context and the filter context. The latter can be the result of page filters , other pivot table filters or slicer selections.

Thus a DAX formula which needs to work correctly and reliably , will need to take all of these possibilities into account.

Secondly , the simplest of the complex DAX functions , such as CALCULATE , is way more complex than the most complex of Excel functions , say AGGREGATE.

Lastly , I think since Excel formulae work on worksheet cells , it is easy to visualize the working of an Excel formula ; a DAX formula works on data , and unless you can visualize the way your data is structured over multiple tables , unless you can visualize the relationships between these tables along with their direction , and unless you can visualize how the evaluation contexts will modify the virtual tables that the DAX functions will work on , it is practically impossible to understand how a DAX formula will work , leave alone develop a correct one.

Narayan
 
Last edited:
You must log in or register to reply here.
Back
Top