1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

PowerPivot Tutorial

Discussion in 'The Vault' started by chirayu, Jan 16, 2018.

  1. chirayu

    chirayu Well-Known Member

    Messages:
    890
    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
  2. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    795
    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.
  3. chirayu

    chirayu Well-Known Member

    Messages:
    890
    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
  4. chirayu

    chirayu Well-Known Member

    Messages:
    890
    Last edited: Jan 17, 2018
    Thomas Kuriakose likes this.
  5. chirayu

    chirayu Well-Known Member

    Messages:
    890
    @GraH - Guido

    I've created a simple document summary for PowerPivot. Have a look & let me know if it looks ok

    Attached Files:

    Thomas Kuriakose and Chihiro like this.
  6. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,006
    This part, isn't entirely accurate.
    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.
    Chirag R Raval likes this.
  7. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    795
    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.
  8. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,006
    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.
  9. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    795
    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.
  10. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi Chirayu ,

    You say :
    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 !

    For those who wish to see this in its formatted version , the file is attached.

    Narayan
    Last edited: Jan 27, 2018
  11. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    795
    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

    Attached Files:

  12. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    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
  13. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    795
    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.
    shrivallabha and NARAYANK991 like this.
  14. chirayu

    chirayu Well-Known Member

    Messages:
    890
    - 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

    Attached Files:

  15. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    795
    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!
  16. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,006
    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
    chirayu and GraH - Guido like this.
  17. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
  18. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,006
    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).
    NARAYANK991 likes this.
  19. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    795
    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
    And
    EDIT: I'm with Chihiro...
    NARAYANK991 likes this.
  20. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    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
    GraH - Guido likes this.
  21. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    795
    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...
    NARAYANK991 likes this.
  22. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi ,

    Ha , ha !

    I am sure my questions will be simple ones ; I don't know enough to ask difficult questions !

    Narayan
  23. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,006
    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.
    NARAYANK991 likes this.
  24. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    416
    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.
    NARAYANK991 likes this.
  25. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    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: Mar 4, 2018
    Thomas Kuriakose likes this.

Share This Page