PowerPivot FAQ “But I still haven’t found what I’m looking for.”


“In answer to your query, they’re written down for me.”

     -Blade Runner Holden

We’re fortunate to already have a number of great sources of PowerPivot information – the official site, some great blogs, and several forums.

We now find ourselves with a familiar problem, though:  given the breadth of the PowerPivot product, finding the answer to a specific question is often difficult unless you’ve been following all of those sources since their inception.  Digging through archives isn’t a lot of fun, even when assisted by a search engine.

So here it is…

Click Here to Visit the Great PowerPivot FAQ

Contributing to the FAQ

Most of the q’s in the FAQ as of today came from a list I’d been maintaining in Excel, and I’ll of course be adding to it over time, but I hope to not be the only one responsible for all of this :)

So if you want to contribute, here are the three ways to do so:

  1. You can email me a question and answer (or post it in comments).  If I agree that it qualifies for the FAQ, I will post it to the FAQ and credit you as the contributor.
  2. You can send me an answer to a currently unanswered question, and again I’ll credit you.  Notice there’s an “Answered?” column in the FAQ, and there are a few “No’s” in there.
  3. I’m also hoping to have a few co-moderators who have edit rights.  I have a few people in mind and will be contacting them directly.  Depending on response, I might open it up to volunteers.

I don’t have enough logins to go around, and certainly don’t want to open anonymous editing, so for now that’s gonna have to be the system.

New Features of the FAQ

Have you ever noticed the View dropdown?  It contains 3 items of note:

View Dropdown_thumb[6]

Latest View

This view shows you the most recently updated FAQ items at the top.  Useful if you just want to see what’s new on a particular day.

Portuguese Translation View

Yes, this is real :)  Thiago Zavaschi has been translating FAQ content from English to Portuguese, to satisfy the hungry PowerPivot audience in Brazil :)

If you see a need for another language translation and would like to volunteer, please let me know.  It’s very easy for me to set up another view.

Unanswered Questions View

I know, hard to believe, but there actually ARE a few questions posted that have no answers yet.

If you see a question here that you know the answer to, please drop me or any of the moderators a note.  We will credit you with the answer :)

Why SharePoint?

I chose to use SharePoint because it gives me a convenient publishing mechanism – I get a hyperlinked table of contents for free, without manually having to keep that up to date in HTML.  It also gives readers the ability to sort and filter, and I can annotate with additional columns as needed.  I can use that site to publish all kinds of other stuff, too – other lists, wikis, whatever.  (If you have suggestions let me know.)

Also, the Data Grid view let me directly copy/paste my existing list of questions and answers from Excel directly into SharePoint.

And all hosted for $9 a month (as long as I’m ok with anonymous access, which I am).  Not bad.

314 Responses to FAQ

  1. Jenny Goddard says:

    Hi. Thanks for the great site, it has been lots of help! I have a few questions and I’m not sure if they are worthy of the FAQ, but I thought I’d give them a try, as I have not yet come up with answers.

    1 – How can you get away from the set display formats (two charts, one chart and a pivot etc)? If you add new charts/pivots into the sheet can they be linked to the existing slicers?

    2 – Can you disassociate a chart from a slicer. For example, 2 charts with 2 slicers and I want the 2nd chart to only change with the 1st slicer (there is some logic to this even if it sounds strange).

    3 – Is there a good DAX solution for variences between data within a table. For example, a table of financial data with actual and budget data can easily produce a pivot table of account type vs Actual/Budget but I have been unable to create a varience as a measure without adding extra calculated columns.

    Thanks, Jenny Goddard (Blueprint, UK)

  2. Jenny Goddard says:

    Really helpful answers, thanks. I had not found the “Slicer Connections” before, so this really solves my problem.

  3. Tony says:

    Hi Rob,

    Congrats on the site and on PowerPivot itself.

    Is DateDiff type functionality available within PowerPivot?

    Some uses I have in mind include calculating elapsed times from dates of birth and from dates of referral to a service.


  4. Trev H says:


    What is the best way to bring in SAP BW data. I heard the best way was to bring it in via a reporting services report. I have two problems with that, the first is that we dont have SQL 2008 RS only SQL 2005. The second is that it seems nuts to have to build a reporting services report everytime we want to bring in new BW data. Are there no drivers that we can use. I suppose we could export the BW data into a text file and then powerpivot that but it would be great to connect directly to the BW query and play with the data. Thanks

    • I don’t personally have the answer for you yet, but I have seen the question quite a bit now. I have posted it as an unanswered question.

      And this also prompted me to post an update letting everyone know about the new Unanswered Questions View.

      • Dave Feldman says:

        I’ve been very successful with this and there are two options. If you have the sap netweaver gateway and a recent version of BW then you can export a query using easy query and make it available as an odata feed. i didn’t have that. So I use a reporting services report with the BW Provider to query BW. Then I export a data feed from the SSRS Report and consume that as odata in PowerPivot or Tabular BISM. I find it works best grab the XML and use it as an inline data services document in my environment, but you may be successful using it as a file as long as your SSAS / PowerPivot server can read that file when you do a data refresh. Btw. it is really powerful to have this integration!

    • Jeremy says:

      If this is possible this could really be a game changer for me! So many possibilities of creating awesome dynamic dashboards without having to run a BW report first and then importing it into excel/powerpivot. Take away that whole step and have it be automatic will give me tons more time to actually create cool stuff instead of always running lots of canned reports everyday.

  5. Jeremy says:

    Tx! I still won’t be able to use this stuff anytime soon but I love learning new stuff especially when it deals with excel. Microsoft is going to have to push these services hard to big corporations as I know my company won’t be upgrading to Sharepoint 2010 anytime soon.

    • Would it be useful/possible to deploy a single SharePoint 2010 server? Because I don’t think we require the whole company to upgrade the SharePoint backone.

      • Jeremy says:

        That I don’t know the answer for. I do not work in our IT dept. I am just a Analyst for a Brand inside our company. I am one of a kind where the other brands don’t have a “me”. I get to fiddle around and create reports based off of SAP BW data. But nothing dynamic like this. My company has been rolling out sharepoint across the company the past year and it still kinda in its infancy for us. I am all for this stuff and I can’t wait to be able to learn more about how to use this! I have it on my home pc but the ability to link it via excel services is where I feel this will shine.. Keep up the good work and keep the blog posts coming along!

        This is the email I got a while back from our Sharepoint developer.. I already told him just the developer needs the Office 2010/PowerPivot. But knowing us it will still be a few years.. :(

        I agree that this could be a powerful tool for enabling users to build some of their own reporting functionality. However, there are a few hurdles to overcome before it can even be seriously considered:
        • PowerPivot/Gemini is an Excel 2010 plugin. Office 2010 is still in Beta, and we (WWW) are barely getting onto 2007 over 3 years after release. I am not aware of a WWW 2010 upgrade plan (but that doesn’t mean there isn’t one).
        • This functionality relies on Excel Services on a SharePoint 2010 platform
        o SharePoint 2010 is currently in Beta and a release date hasn’t even been announced; it is unknown if/when we will upgrade
        o Excel Services is an Enterprise licensed feature, meaning that each user that employs the functionality (or has access to a site that has it enabled) must have an additional client access license – current pricing is about $100 per CAL in SharePoint 2007, but pricing hasn’t been announced for 2010 yet

  6. SAP BI Consultant says:

    Hi Jeremy, hi Rob,

    I had the same idea like Jeremy to test the retrieval of data which is provided by SAP BW. No surprise that I also wanted to check this – I am a SAP BI consultant 😉 Don´t worry Rob, I am not an employee of SAP, I am only an independent, self-employed SAP BI consultant. Due to the currently enormous changes/challenges (and the incredibly long lasting confusion) of SAP in the area of the BI frontend tools (BO Tools, new developments such as the new Pioneer frontend which btw partly looks pretty similar to Power Pivot) I am more and more looking for alternatives – out of frustration with the chaotic product strategy. Therefore I had a look on PowerPivot which looks pretty nice, but I also missed the possibility to access the SAP BW. Which is a bit surprising, since SAP BW is increadibly widespread in many big corporations nowadays.

    Having checked the Connection a bit more in depth there is good news and bad news.

    The good news at first:

    There is indeed already a possibility to connect to a SAP BW query. Just go to the button “From Database”, then choose the entry “Others (OLE DB/ODBC)”. A window will open up and there you can see the button “Create”. Hitting this button, another window will open up and there you will see four different tabs. You will start on the second tab which is named “Connection”. But you need to go to the first tab which is named “Provider”. Doing this you will see a list of many different OLE DB/ODBC drivers. If you scroll down, you will see the entry for “SAP BW OLE DB driver”. This is in general one possibilty to connect to the SAP BW system. Of course it presumes that your SAP BW query has been flagged as SAP OLE DB query – which is not the standard when a new SAP BW query is built (not necessary). It is just a flag in the SAP BEx Query designer. If you want to go for this solution, then I recommend to copy an existing query, go to the change mode of the query designer and set this flag. This is all you need to do and then you can use this query for PowerPivot as a dataprovider.
    So, up to here everything looks pretty good. Once you chose this entry SAP BW OLE DB provider, you need to to go to the second tab (Connection) and then you go to point 3. Since I am using the German version of PowerPoint I will not even try to translate this point 3. I guess in English it will be named differently from what I believe it might be translated correctly. Anyway, here you choose the drop down box and immediately the famous SAP Logon Screen will pop up, where you need to enter your SAP BW user name and password. Having done this it takes a few seconds and the same drop down box will open up again and offer you all those BW queries which are flagged as OLE DB queries.
    From here you should be only a few seconds away from being happy.

    But as always in life, there is not only light, but also some shadow. :-(

    Now the bad news comes:

    Everything works fine up to here. Even if you choose one of the OLE DB capable SAP BW queries, it still looks fine. You confirm then with OK and then PowerPivot will start to work… and work… and work. Then – after some time – the window will freeze and… Shit, Excel will crash. I tried it several times, but it does not work – or let me say: It works all the times very similar – it crashes.

    Summarizing all this, it means: Already today PowerPivot theoretically has built in the ability to access the SAP BW by accessing SAP BW queries. The precondition is that those people who are SAP BW query designers in your company make a copy of the query for you, flag it as OLE DB capable query and transport this to the production system. Then nearly everything you need is provided. The only limitation right now is that Microsoft needs to find out, why the PowerPivot including Excel crashes… 😉 Once they found the bug, the solution should work… in theory at least… 😉

    There is currently no possibility to access the SAP BW cubes directly which is really a very pity, because since Release 7.0 there are quite a few different possibilities also to access the cubes directly with 3rd party tools. One possibility is the XMLA interface which originally was developed by Microsoft by the way… :-) and which works fine. We just tried it with another 3rd party tool.

    Even if I cannot provide you a working solution right now, I hope that I at least could make clear that there is a potential way to directly access SAP BW data, in case that Microsoft finds out why its product currently still crashes when using the SAP BW OLE DB provider.

    Best regards,


  7. David Peers says:

    Thanks, this blog helped me in solving some problems with the latest version, Why do they always leave out vital documentation when they release a new version? It may be trivial to them but not to me. I’m sure we’re not alone either.

  8. Barry says:


    How many rows can powerpivot handle?


  9. Jon Lankford says:


    I am struggling with a couple of expressions in PowerPivot. 1). I am in need of an expression that accumulates the values over a period of multiple years. 2). I am in need of an expression that uses the last accumulated value for a time period.

    1). I am drawing an S Curve for cost over time. The table consists of two columns (Performance Date and Budgeted Cost). Each row contains a date from 1/1/2010 through 6/1/2012 and a cost for each date. I need the expression to accumulate the values from one date to the next for the entire duration.


    1/1/2010 $10.00
    2/1/2010 $20.00
    3/1/2010 $30.00

    Desired Results:

    1/1/2010 $10.00
    2/1/2010 $30.00
    3/1/2010 $60.00

    I am currently using the following expression but it only works for the first two years: =TOTALYTD(SUM(Budgeting[Budgeted Cost]),Budgeting[Performance Date],all(Budgeting)) + CALCULATE(SUM(Budgeting[Budgeted Cost]),PREVIOUSYEAR(Budgeting[Performance Date]),all(Budgeting))

    2). I have accumulated values for a date range (i.e. 2/1/2010 – 12/1/2011) and now need to create an expression to use the accumulated value for the last date to calculate another value.


    1/1/2010 $10.00
    2/1/2010 $20.00
    3/1/2010 $30.00

    Expression: =TOTALYTD(SUM(Budgeting[Budgeted Cost]),Budgeting[Performance Date],all(Budgeting))


    1/1/2010 $10.00
    2/1/2010 $30.00
    3/1/2010 $60.00

    I now need syntax to use the $60.00 value (last accumulated value) in another expression.

    Any help that you can provide will be greatly appreciated!


    Jon Lankford

    • Hi Jon!

      First recommendation: when working with date-related logic in PowerPivot, it is ALWAYS best to start with a Date/Calendar table that is separate from your main table.

      Now for question #1. Assuming a table named Sales as your main table, a table named Dates as your date table, and a pre-created measure named [SalesQty], the following works great in my limited experimentation:


      Loosely translated, that means “take the SalesQty measure and evaluate it over all dates between 1/1/1900 and the Last Date you find that corresponds to the filter context of the current pivot cell.”

      This was a useful excercise for me since at Pivotstream we often cannot use these functions as-is, so it’s easy for me to get a bit rusty with them (plus their behavior has changed since the Beta). Looking at #2 next.


    • #2: Define a new measure, name it something like “Running Sum on Last Date”

      The formula goes something like:

      [Original Running Total Measure From Part 1](LASTDATE(ALL(Dates[Date])))

      First it take the Date column, reverts it to ALL (removes current filter context from the pivot cell).

      Then it finds the last date out of all of those dates.

      Then it evaluates your original measure against that date.

      Now you can reference your NEW measure in other measures.

  10. Agust Eriksson says:

    Hi, I’m having problems connecting PowerPivot with an Oracle database. I’ve installed the latest oracle 64 bit client (my machine is W7 64 bit with Office 64 bit running). The error message I get is “The ‘MSDAORA’ provider is not registered on the local machine.” Have you guys run into this issue?

  11. rwendeln says:

    I’ve been working with PowerPivot for a couple of months now; great tool. So I know how to get data into a PowerPivot table.

    Can you export a data set, however, from PowerPivot back into Excel? I don’t see an export function in PowerPivot. When I do a “copy and paste” of a PowerPivot table back to an Excel worksheet, I get errors in the data set (especially if it is a large table or I have a filter on the table).

    Thanks for the advice.


    • John M says:

      I used PowerPivot to open a 36 million line text file. I filtered this for a certain type of transaction reducing the total lines to 36 thousand. I then copied and pasted this data into Excel and found that every time I copied the data, I ended up with a different total.

      The file I was opening is monthly data showing transactions for the month of December. December is a large month for the business. I was able to open the other (smaller) monthly text files in Access to allow me to copy and paste the values into Excel for an import back into PowerPivot. The December file exceeded the 2Gig limit in Access.

      I’m sure there are workarounds, notably querying the unopened text file using Access instead of opening it in PowerPivot. I, however, still want to know if I’m alone here or if this is a known issue in PowerPivot.


  12. Cory Purkis says:

    Can I add a total to the horizontal axis of a stacked chart? I have the total in my underlying pivot (created by PowerPivot) but do not see a way to expose that as a bar on my chart.

    Horizontal Axis has “Subregion Name” showing % sales attainment. Additionally I want a bar showing how the Region is doing overall.

  13. Scott Sobel says:

    Hi Rob & Jeff,
    I enjoyed your recent webex you guys did on Wednesday. I was the one that asked if a slicer can be prepared that lists the columns in a table, from which you pick, then another slicer would respond to that and then list the individual values in the column you picked from the previous slicer… you responded that yes that can be done… I’d like to follow up – how? Is there a brief example yuo could send me that I could look at as a model? Is there an easy explanation of how this could be done? I’m really interested in this kind of functionality to make my life much easier and productive at work.

    Thank you very much in advance.

    • Hi Scott, thanks for the kind words on the webinar :)

      I’m afraid I may have misunderstood your question though. I know how you can show a completely different set of measures in response to a slicer click, but completely changing the contents of one slicer based on selections made in another is not possible. Slicer contents themselves are very much static.

      There is, however, a way to have the contents of slicer B change sort order, and have certain values “bubble to the top” in response to another slicer click. That’s just the default behavior known as cross filtering though, and if you’ve played with slicers at all, you’ve seen that in action already (but it did NOT show up in my demos on Wednesday).

      Let me know if you need some pointers on that. Searching this blog for “cross-filtering” should turn up a number of past posts.

  14. Jesper Bork says:

    Hi there,
    I can’t seem to get RSS feeds working as a data source in PowerPivot in Excel 2010 – e.g. the following currency exchange rates RSS feed which works fine when pasted into IE8:


    Hope that someone can enlighten me on using RSS feeds as PowerPivot data source.

  15. Smithicus says:

    Can I set up a data refresh that runs at least every 15 minutes every day? (on PowerPivot v1)

    Thanks for your time,


    • No, you cannot. But we have software at Pivotstream that enables that scenario.

      • Smithicus says:

        Thanks for your respons, it seems that frequent refreshes require the Powerpivot workbook to be published to SSAS denali (and then use a sheduled job to ‘process’ the tabular cube).

        Which means upgrading to Denali first. Damn Microsoft, always leaving you wanting more… 😉

        That which you use at Pivotstream, is that a seperate installable package?

  16. Samson says:

    I am really struggling with PowerPivot(PP), and unable to find an answer to my problem. I have searched the net for hours but to no avail.

    I have bought Excel 2010 and installed the PowerPivot. I have an existing excel file in which I want to create a Pivot Table(PT).

    I am getting this error repeatdely” Initiation of the data source failed”.
    I am also unble to refresh my data. If I look at the PP window, the refresh windwon is no available. The refresh button on excel does not refresh the data. If I look at the PP option, and click to see the source of data, I can see the pivit name, but when I click the PT source Button,”The select a table, or range” is Not available. So where do I see my data? In the old version PT, when I click the source data, I can see the range and souce.

    I do not know what I am doing wrong. Please help before I lose my patience and revert to the old PT version 2007.
    What are the correct steps to create a pivot from an existing open file excel?

  17. Willem says:


    Is it possible to build a Slicer with column headers, so if you select a different header your table/chart changes all data. ( example cost/order, sales/order)?


    • Willem says:

      Some more details about my question.

      I have build a report, with all kinds of results by month (Based on a Column that tells me which month, -I hav o actual date column).
      What I do have is 3seperate Year, Month & Week columns.
      My Goal is to build a Slicer, giving the end user the possibility to choose the view of the results by Year, Month or Week.

      Can someone help?!

  18. OtterBA says:

    I want to my end user to type in two dates in order to sum production for a given range. Is there a way thru formula to do so using cubesets and cubevalue?


  19. Sami says:

    Hi There,
    My Question is “Is there any decent way, where i can amend and import data on an existing powerpivot table from multimple excel worksheet?”

    Background: I have started a large project in PowerPivot and got stuck in one silly problem. I believe this could be a FAQ on the section.

    I have a a workbook with say about 36 worksheets [Eg Jan 2008, Feb 2008….Jan 2009, Feb 2009…Jan 2010…Dec 2010] each with same structure [10 Columns] containing 100,000 rows on each sheet.

    Now i am trying to consolidate all of them and trying to import them in a PowerPivot table for yearly analysis but whenever i am trying to link each sheet it is creating a different linked table. How can i accommodate all of them in one single table? You know what i mean to say.

    Yes, i know i can copy each sheet and then use the paste append one after another, but on that case if any of my source data changes then i will not be able to reflect it in the power pivot correctly.

    I can copy all of them in a note pad and can import it but then again i will loose the flexibility of Excel for Changing data.

    So is there any other decent way, where i can amend and import data on an existing power pivot table from multiple excel worksheet?

    I really really look forward for this annoying problem.


  20. Miguel says:


    I just had a recent meeting with the people from Jet Reports and they are basically offering me a “lower version of the Powerpivot”, in other words I seee that their software is pretty much the same as Powerpivot but you need to create your own cube before playing with the data. I’m a data analyst who loves excel and powerpivot but I want to get something similar to the Powerview (mainly because of the relational picture files that you can related to some data), is there any way that we could get that functionality in Powerpivot? or is that only for Powerview and Pivot viewer?

    Can anybody show me a chart with the differeces between Powerview vs Powerpivot vs Pivotviewer?

    Trying to make a business dessicion on wether to get the MS Package or the JetReports thing.

    Thx in advance!

  21. Miguel says:


    1. Use a date table and link your dates in each sheet to that dates table.
    2. Make a Measure for the fields that you want, taking in consideration that the measure will be taking in consideration all the 999999 sheets (tables).

    Once you have that measure and your dates table you can go from there, otherwise, the append would do the job each and every time.


  22. Michael Jack says:


    I have created an Income Statement (P&L) in PowerPivot. We have a dimension for U.S. state and I have added the U.S. State dimension as a slicer. Is there a way to batch the report and have PowerPivot create a pdf document for each state. Once the pdf is created it would have 50 pages. Otherwise the users would have the click the slicer 50 times and save it as 50 different files.

    Michael Jack

  23. Minoo says:

    This site was extremely helpful. I had a question, I was looking to find an answer to. Using ASSL I have created an Offline Cube file. I wish to use this as a source for PowerPivot. Can this be done? I have tried a few things without success. Your help is much appreciated.


  24. bruno pozuelo says:

    Hi there
    thanks for the site and all the work you’ve done around it
    I did not managed to find out how to send an email to you directly so I hope that comment will not be lost in the middle of all others
    the question I have is pretty simple : starting from a list of days (01/07/2010, 02/01/2010, etc…) and using any of the DAX function I would have imagine that it would have been possible to show the same day last year (in that example 01/07/2009, 02/01/2009,etc,…) while tackling the february 29th thing (some years that day happen, some others don’t)….But so far I have not found it yet. Any idea or tip to share about it ?


    Bruno, a great fan of powerpivotpro from Europe

  25. Bruno says:

    Hi there

    Once again I’m calling the 911 of Powerpivot. The more I work on that tool, the more I enjoy it but the more I face sometimes some tricky situations…
    And as you’re the skillest, nicest (and all in all the best) powerpivot site I’ve read so far, let me request your help and knowledge once again.

    This time I’ve tried to draw a pivot chart from powerpivot table. Something simple. As learnt I’ve selected from the powerpivot menu the pivot table report button and then selected “single pivot chart” (hope the translation into english is fine). Doing this, i’m brought back to excel spreadsheet where there is one tab which is added automatically and named as “datas for graph sheet 1″ and where I can see an empty pivot table + the field list from powerpivot, but there is nothing appearing on the tab where the chart should appear…Normally there should have a chart frame appearing with on the right side of the screen the powerpivot fields, but this time : nothing
    I’ve tried this on a laptop 64 bits with of course powerpivot v1 64 bits installed. Once i’ve seen the chart function did not work properly I’ve tested powerpivot on another laptop (32 bits this time) with powerpivot v1 32 bits without any success….
    HAve you ever seen that ? Any idea of what’s going wrong ? the powerpivot version i use is the V1 downloaded a couple of week ago…the fact that the chart
    I do hope you’ll be my powerpivot hero once again

    Thanks in advance and best regards

    • powerpivotpro says:

      I don’t believe I have ever seen this problem myself, Bruno. Sorry, I don’t have any theories :(

      • Bruno says:

        sh**….i keep looking in google but sounds no one has met that trouble before….any advice about who I should ask this ?

        • bruno says:

          anyway i can send you my spreadsheet and see if it works on your laptop ? if it works on yours that will let me think it is a trouble related to system (version of excel or powerpivot)

  26. Nikitas says:

    I’d just like to say that I utterly enjoy working on Powerpivot and it made my life so much easier. As a company we’re going to start using it quite a lot. Thank you so much for the info provided and the hard work you’ve been doing so far.
    I do have a question though. If this is not the place to ask it I do apologise.

    As a company we have two huge accounting databases. One has the actual data and the other the budget data, split in various account codes, divisions etc. They do share however a similar ‘date’ field. Powerpivot doesn’t allow me to link the two tables since that field have duplicates. I can’t create a unique ID field to link them since the only thing different between the two are the amounts stored and the SQL table they come from.

    Is there a way to use one single slicer for the two graphs of the two tables? For the time being I have two slicers, one next to the other, linked separately to the two graphs, but they have the exact same values. It doesn’t look nice.

    Thank you so much in advance.

  27. Zdravko says:


    Just started using PowerPivot and find the product absolutely perfect for BI self-service reporting for our organisation. I would like to know if at all possible to sequence textual descriptors into a pre-defined sequence rather then the default A-Z sequence. This is visually useful for us when graphing events in a business process. For example if my events or dimensions sequence was D, X, A and B. PowerPivot displays the list along the axis in A, B, D, X order.

  28. David says:


    I have shown my Fin Director examples of the Web based Dashboard & Reports that can be achieved with Excel 10 / Powerpivot and Sharepoint 2010. However, as an organisation we have previously invested in Business Objects and also have Xcelsius (BO’s Dashboard tool)available

    I have been asked to compare and contrast the 2 products and am wondering whether anyone has either done, or knows where I might find a comparison or, alternatively, has used both products and can share their experiences.

    Many thanks


    • David Churchward says:

      Hi David

      I would be more than happy to help you out with this. Without knowing your business, it’s hard to be specific about what the key benefits are, but in my experience, speed of turnaround, self service and putting business insight into the hands of the key users (probably Excel pros) is critical to a business. These are all aspects that PowerPivot delivers and where traditional BI often fails.

      Having said that, Business Objects (whilst I haven’t used it extensively) can be a good product. It might be the case that PowerPivot serves to compliment what you already have. Due to the relatively low cost of ownership, this could be a viable model. Overtime, it will become apparent what the best solution is for your business.

      If you want to discuss in more detail (thereby avoiding me waffling in this comment section) please give me a shout at david.churchward@azzu.co.uk.


  29. Sri Meda says:


    How can we implement dynamic secuirty in SSAS Tabular (Server version of PowerPivot) using user group rather than username (using USERNAME()) function?

    Thanks in Advance,

  30. bruno Pozuelo says:

    Hi there
    Thanks for this astonishing website. i’m a great fan of your work, allways helpful

    Today I come to you with an issue
    On powerpivot, I tranfered first datas (like turnover, so obviously numbers) from a cube built on analysis services.
    (despite using the assistant of import of datas, i had access to the MDX script that has been created by Powerpivot :


    Once imported in a powerpivot tab, this info is a text.
    I tried of course to turn that into a number through the data type o the ribbon, but it says
    Failled in changing the type of datas from the column

    on error message detail it is shown :

    L’erreur système suivante s’est produite :  Le type ne correspond pas.
    Erreur de base de données PowerPivot : Échec de la conversion du type de données pour [Table : « Requête », Colonne : «  », Valeur : « 183867510.25867417 »].

    Échec du changement de type de données de la colonne.

    Pile des appels :

    à Microsoft.AnalysisServices.Modeler.Storage.DataModelingSandbox.ExecuteCaptureLogAndProcessResults(OperationType type, Boolean cancellable, Boolean raw)
    à Microsoft.AnalysisServices.Modeler.Storage.DataModelingSandbox.ExecuteAMOCode(OperationType type, OperationCancellability cancellable, AMOCode code)
    à Microsoft.AnalysisServices.Modeler.Storage.DataModelingTable.Update(UpdateOptions updateOptions)
    à Microsoft.AnalysisServices.Modeler.Storage.DataModelingView.ChangeColumnsDataType(IList`1 columns, OleDbType dataType)
    à Microsoft.AnalysisServices.Modeler.Storage.DataModelingView.ChangeColumnsDataType(IList`1 columns, OleDbType dataType)
    à Microsoft.AnalysisServices.Modeler.Controls.TableWidgetGridControl.ChangeColumnsDataType(List`1 columns, BasicDataType dataType)
    à Microsoft.AnalysisServices.Modeler.ClientWindow.DataTypeSelectionChanged(UInt32 newSelectedIndex, String newSelectedString)

    Our internal IT does not know how to fix this

    So question : have you seen that before ? and do you know how to fix this ?

    P.S. : i work with Powerpivot V1 only

    Thanks in advance for your precious help

    Best Regards

  31. Sven says:

    Hi there!

    Im looking to create a diagram in which a certain amount of hours are distributed evently over a span of time. I want to do this for several different projects with several different start and end dates.

    At my disposal I have the project code, which is unique, and the start date and end date of each work phase.

    The only method I have tried that have come close to achieving what Im looking for is pasting in a series of dates manually for each project, something that is obviously not feasble on a larger scale.

    Anyone got a solution for this? Seems to me to be a pretty natural thing to want to do but I havent found much about it on the web so far…

    Another thing, is it possible to to do a Linked Table from a PowerPivot table I am using a Flattened Pivot Table?

    Thanks in advance

    • David Churchward says:

      Hi Sven. Have you seen the recent Gantt Chat posts? This uses start and end dates so it might give you some direction.

      On the linked table to flattened pivot table, I would suggest that you’ve already got the data you need in your model so why would you do this? The simple answer to your question is no. There would be a serious refresh data issue. Happy to help if you want to work through this.


  32. Bruno Pozuelo says:

    Hi the Powerpivotpro team !
    I’m quite obsessed by developping a financial reporting tool (something similar to Hyperion) using Powerpivot for my current job since I discovered (and thanks to you) how powerful powerpivot can be.
    So far, I managed to have some good results with the prototype I developped.

    However I’m trying to improve 2 things :
    – I have trial balances with amounts expressed in several local currencies and must convert them into a third one using a link between 2 tables (trial balance and fx rate). SO far I added up a column in trial balance table with a relatedtable formula that allows to bring back the fx rate to be used and convert local currency into a third one. But I do suspect there is a “measure formula” to implement directly on pivot table that could bring me to the same result without increasing drastically the size of the excel file. Do you have any idea of how to write it ?

    – in my world, profit and losses (P&L) are translated from one currency to another using average rate (monthly average rate actually) while balance sheet is translated at spot rate (rate of the last day of the month). So on a table, I have a trial balance by month (with both P&L and balance sheet accounts). Once created the pivot table with the month as a slicer, it gives me the correct amount for P&L because with a formula I translated the monthly profit and loss accounts for each month with their related monthly fx rate. But it does not work for balance sheet. Indeed, the con of that formula is that it converts balance sheet accounts per month with the spot rate of each month, while my intention is to show only the accounts of the month selected with the slicer and convert that only monthly amounts with their related spor rate Any tip to do that ?

    Thanks a lot in advance for any advice / tricks / tip you might think relevant

    And thanks a lot for existing. You guys rock !!!

    • powerpivotpro says:

      Bruno I have alerted David C about your question. He should be here in 3… 2… 1…

      • pozuelo says:

        i’ve worked on these ideas all day long but still haven’t found a quick fix. But of course, I don’t have your large experience of this product.
        Cheers and have a nice week-end

  33. David Churchward says:

    Hi Bruno

    You’re bang in my back yard. I’m sure we can nail this. I have consolidation and exchange adjustment models so I’m sure we can solve this.

    Are you able to send me your file? If so, please forward to David.churchward@azzu.co.uk. I will gladly take a look and we can progress from there. I think a blog post on this is long overdue.


  34. Hi!
    I’m happy to find needful help here – thanks so far…
    I have a list of entries in a table stating date and time in the same column. I need to group this data by day, week, month etc. I haven’t found any clues on how to achieve this with PowerPivot. Grouping is not done in the same way as in Excel (which handles this well). Have you got any tips…?

    Best regards

    • powerpivotpro says:

      Hi Lars. One way to do this is via calculated columns, using YEAR(), MONTH(), etc., and concatenations of those, then adding those fields to rows rather than the original date.

  35. Plamen Mitov says:

    Hello, powerpivotpro,

    I am new to powerpivot and have an issue with importing data from text files.
    I need to use the first row as header, but if all other rows contain numeric data, powerpivot imports blank cell in the first row. When I try to activate the checkbox “Use first row as column header”, I receive the following message: “More Details:Failed to retrieve data from filename#txt. Reason: The expression contains invalid date constant ‘#txt.NoName’.”

    So, please, if there is a work around, would You be that kind to share it?

    Best regards,

    • Plamen Mitov says:

      An additional TAB symbol was detected at the end of row 1 (header), which somehow makes the difference.

      Thanks anyway for reading my worthless crap!

      And now another issue emerges – in the preview I see values in the first column of every visible row, and when the import finishes, about 50 000 out of 70 000 rows have an empty first cell. The import is from tab delimited .txt file.

      Any ideas?

  36. David Levy says:

    Hope this is the right place to leave a question. The book is very good, I am reading it again with more time now.
    One question about relationships: If you have for example a situation where you have actual figures and budgetd figures (sales, expenses, etc). Actuals will find a budget as they “lookup” the budget table. However, the problem is that budgets exists for rows with no actual. The result being that the total budget number will be understated by the sum of those rows. Any practical way to deal with this in Powerpivot?



  37. Shahzad Hanif says:

    I want to ask about “IF” function, is there any limitation of using IF function in powerpivot window like I m using my account code to have particular value if the condition is true but powerpivot window is not returning any value although the condition is true and partial results are appearing in powerpivot window.


    • powerpivotpro says:

      Hi Shahzad. I’d need more detail on the problem in order to answer. IF() works fine – I use it all the time. But there may be something particular about your situation. Maybe mismatched data types?

  38. wfvandijk says:

    Hi Rob,

    I created a report based on your TopN posts and am wondering if there is a way to “shut-off” a slicer when it comes down to a measure calculation.
    I have a TopN of productID whith slicers allowing for country selection and another one for dealer selection, what I would like to do is have a measure show up in my report which would ignore the fact that I have decided to select a specific country and/or dealer, thus creating a row which would show ranking for a specific product as it refers to Dealer, Country and Global…

    I was thinking about applying ALLEXCEPT, however, due to location of dealer information versus sales information, I keep getting lost.

    Any thoughts on where I should start hunting for a solution?



  39. Amanda says:

    Hi PowerpivotPro team!

    First of all, I MUST thank you for all the brilliant tutorials, I’ve learned soooo much from you guys.

    I have a question regarding slicers and cube function. Is it possible to use a reference to a cell whitin cubvalue function for slicers. For example:
    =CUBEVALUE(“PowerPivot Data”,$D15,E$13,Slicer_Region)
    I would like to use
    =CUBEVALUE(“PowerPivot Data”,$D15,E$13,$A$1)

    where cell A1 contains which slicer I want to use in a formula (in this case Slicer_Region).

    I need that, because I want to make a report with cube functions where you can dinamicly change a slicer you want to use without having to change all formulas in a sheet. Is that possible?

    Thank you very much for all the help and support you guy give us!

  40. Bryan B says:


    I have a sharepoint list that has a people picker and I pulled that list into PowerPivot however when importing into PowerPivot those people picker columns are lost. If I export the list to excel and then use the table it creates will allow for the people picker column to be pulled into PowerPivot. Is there away to get PowerPivot to pull in the list without exporting the list to excel first and keep the people picker columns?

    Thanks for all your help and great website,


  41. Nikolas says:

    Hi PowerpivotPro team,

    Hope you can help me with one problem, I have 2 unrelated tables, CaseID column in both tables, there are CaseIDs which are not in Table1 but are in Table 2 and vice versa, plus some of the case IDs in Table2 there are multiple times. What I need is to have a column in Table1 where I could see if given CaseID in Table1 is also represented somewhere in Table2 CaseID column.

    Thanks, Nikolas

  42. Dave Feldman says:

    PowerPivotPro team.. have a new FAQ for you. As far as I have been able to find, the Excel Data Model (and therefore PowerPivot) is completely non-functional in Excel 2013 unless you have admin rights to your local workstation. I haven’t seen this documented but have recreated it on 20+ users. Any thoughts on getting around this or if this is just a bug in 2013?


  43. Dan says:

    Did you ever get an answer to this? I have the exact same issue and am looking for a way to do this.

  44. Dan says:

    My last post is regarding a solution to using a spot rate for powerpivot balance sheet. Orginal post Aug. 31st 2012 above.

  45. Qui says:

    Hi PowerPivotPro team,
    I work new to PowerPivot and amazed by its capabilities but one of the shortcoming i see is row level or user based security. I understood that username() DAX function does solve this but couldn’t find much details.
    I am using Oracle as backend and sharepoint + PowerPivot as SelfService BI. If i want to implement user level security with this set-up, how to proceed? which version will work? Appreciate your help.

  46. Andrew says:

    We opened a Support Incident with Microsoft and got the answer on the requiring Admin rights in Excel 2013. You need to use NTRights.exe in an Administrator Command Prompt to grant your user privileges required to use PowerPivot (Power View, GeoFlow, etc.):

    ntrights +r SeIncreaseBasePriorityPrivilege -u DOMAIN\youruser

    You should see: Granting SeIncreaseBasePriorityPrivilege to DOMAIN\youruser … successful
    Error: ***Error*** OpenPolicy -1073741790 means you were not running command prompt as Administrator

    Now do a complete logoff and then log back in again. The Excel 2013 Data Features should now work.

  47. Mathieu says:

    I have a file with many calculated fields and some of them don’t calculate.
    Here’s an example:
    I have this formula:
    and the calculation return a blank field. It shouldn’t.
    I do this:
    and the calculation works. Then I remove the +1 and the formula is ok.
    Anyone know why?

  48. Mathieu says:

    I’d rather not.
    That bug is not always present. Not often on my PC. More often on people from my team’s pcs…

  49. Jon Pearce says:

    It looks like this is a big deal. Mr Excel just posted that the retail boxed versions of Excel 2013 (not Office 2013 – just Excel 2013) will include PowerPivot – see http://learnmrexcel.wordpress.com/2013/08/16/how-to-buy-power-pivot-for-excel-2013/

    As I understand it, this is the first opportunity for retail buyers to have access to PowerPivot – otherwise it was only available thru Office 365 Pro Plus or volume purchase. Looks like Rob’s Who Moved my PowerPivot Cheese article got some results.

  50. Brian says:

    What is the difference between the Desktop version and the Server version of PowerPivot?

    • powerpivotpro says:

      Server version lets you publish to the web, people can view and interact with workbooks in their browser (or ipads!). Also you can schedule auto refresh.

      Check out the What is PowerPivot page for a little more info.

  51. Ryan S says:

    Not sure if this is the right place to leave a question, but ill give it a shot. When I use the switch function to put measures in a slicer,it doesnt filter other criteria down to those that have values. For example I am showing Gr Hrs by plant, filtered by year from another slicer. When I use the regular measure, it will only show plants that have hours for the given year, but when i use the switch formula, it shows all plants that have ever had hours, with the value being blank. Any way to only show plants that have data for the criteria I select with using my switch function?

    • Chris Gilbert says:

      One possibility: There are Slicer Setting that can be set to enable/disable the “Hide items with no data”, “Visually indicate items with no data”, and/or “Show items with no data last” In the 2013 version, you can either right-click on the slicer or use the SLICER TOOLS menu to open the dialog that lets you modify these settings.

      Caution: There is a performance hit for having these features enabled, as I learned in Rob’s Advanced PowerPivot on-line class in the “Performance” module.

      Another possibility, a slicer can be “connected” (or not) to any combination of Pivot Tables and Pivot Charts (again, in the 2013 version this is called “Report Connections” on the SLICER TOOLS Options Ribbon.

      Finally, you should make certain that the spelling is EXACT in your SWITCH statement, any variation could result in no filtering.

      Hope this helps.

  52. Jon Pearce says:

    I need to create multiple copies of the same underlying PowerPivot table using different data sources. Is there a way to create a script (or even a list) of the DAX expressions that I can use to bulk-populate the new PowerPivot models? These models change frequently so it would be cumbersome to create a template, since the template would need to be constantly updates. I’d like to be able to take an existing model, script out all of the DAX measures (and ideally the table connections) and apply it to a new PowerPivot model. An ideas? Thanks


  53. Simon says:

    Thank you for an awesome site. I have a question I cannot find an answer to:

    Why are my numbers imported as text?
    I have an excel table of data that contains fields that are definitely numerical values; there is no text in them except the header. Yet every time I create a linked table to PowerPivot is puts the field in as text column with a little warning sign that lets me create a calculated column (using some IFERROR and INT functions) to essentially replicate it as a numerical value or to leave it as text.

    As text, I obviously can’t do any calculations. I don’t really want to create all the calculated columns and am frustrated as I have no idea why it is doing it. Any help appreciated.

  54. I managed to set-up a data connection link in Excel 2010 to a Bex easy query to read BW data. However, ALL records appeared in Excel despite the query restrection that I applied and verified that it is working in BEx! Please advise.

  55. Jon Pearce says:

    I think this answer is NO, but I’ll ask anyway. Is there a way to default a “dimension” to a value other than “All”? In some of our models we want the default value of dimension to a particular value. I believe that this is possible in SSAS multidimensional cubes, but don’t think it’s possible in PowerPivot, is it?

  56. prasad says:

    I have a problem in my model in excel sheet I had used TDIST function with below formula =IFERROR(TDIST(ABS((E8/100))/SQRT(($AU8/$I8)+($AX8/$B8)),($B8+$I8-2),2), 0)
    Syntax of TDIST(-t,df,2) .

    Where as this is not available in DAX while of trying to write use this in calculate measure in DAX can any help me what is the alternate function of TDIST in excel .

    TDIST function is not available in DAX function

    Can any help me on this

    In advance.

  57. Hi there. quick and dumb question (did not find the answer in the FAQ (which is by the way the best FAQ I have ever seen/used). I am running Excel 2010. My company has unfortunately still not decided to go for sharepoint. I have a powerpivot that gets data both via an oracle connection and from a local excel file.
    Every morning I need to refresh only part of my oracle connected tabs. Is there any way I can do that without hitting “refresh” tab by tab ? Basically is there a way to select some of the tabs and refresh only these ?
    The reason why I am asking is that I’d like to start the refresh and go take a coffee, rather than staying in front of my computer to hit refresh every time one of the tab is updated…


  58. TJ says:

    Love, love, LOVE PowerPivot, The Book, and this site! Thanks to you and MS for literally changing my life. One thing I have yet to figure out, however, is how to make multiple “flatfile” copies of different cuts of my workbook. I know, “what’s the point?” right? Well, we don’t have PowerPivot installed to the masses for the time being and Leadership is requesting a non-Pivoted file (to avoid the inability to refresh, etc etc). I am still leveraging my data models to create my reports but now I want to disconnect from PowerPivot and unload the data embedded in the backend. I used to be able to accomplish this by right clicking on the Pivot-> Pivot Options->Data-> Uncheck the ‘Save Source data with File’ in non-PowerPivot files. I have tried a bunch of different things, copy->PasteSpecial…, Deleting the PowerPivot Data connection manually and via VBA, etc. Any tips? Thanks!

  59. Mathieu says:

    Question; I have a database (SQL) that have images (scanned invoices) in it. I want to be able to hyperlink the values I will have in a powerview with the SQL link so that I’ll be able to click the value and get the invoice to pop. Any idea?

  60. Chris Turnbull says:

    Hi Rob

    I have been looking at Chandoo’s Powerpivot course but I am a little confused as to what I need to do. Do I just need to do your course or do I need both?



  61. Marshall says:

    HI Rob, and readers, here’s a problem and scenario that I would love some input on.
    Bottom line is I need a Disconnected Table to use as a slicer for a chart & table, and the values in the Disconnected Table need to reflect changing dates.
    Here’s the details, I have a fact table which includes a list of [Tasks], each of which has a [Due Date].
    I need to display a chart showing how many [Tasks] are due [Next Month].
    I have created a calculated column using Today() and EOMONTH to look at the [Due Date] column and if it occurs next month display the year and month which in this case means it displays ‘Jun-14′ (because today it is May of 2014).
    And I have created a measure to count how many occurrences are in that column, that measure [Due Next Month].
    The problem is I can’t use the measure or the calculated column (effectively without the ghost button) to slice the chart.
    My sense is that a disconnected table with a column called something like [Due Months] and one of the values in that column ‘Next Month’ would be the solution, but how do I get ‘Next Month’ to relate to the real calendar ‘next month’ and have it change when the month turns over???

    Any suggestions are greatly appreciated.

  62. Mike Scarborough says:

    Does anybody ever get an error message in PowerPivot (2013) that says “We were unable to update the linguistic schema”? The crazy thing is that there is nothing on Google or Bing about it.

    • Peter Albert says:

      Hi Mike! I also have the error message all the time. Alongside with very long, repetitive data model refreshs in Excel on any DAX formula change. Haven’t fonud a solution yet… :-(

      • Christofer Broström says:

        Hi! (And thanks Rob for being there)
        I have also got the message, and have no clue what it is. However, it seems everything works just fine despite the error. (Well, “just fine” surely is a matter of definition. I am using lots of csv-files since I have never dug into this SQL business. As a consequence, I suffer.)

        Mike/Peter, what’s your experience? Actual errors apart from wasted time?

        • Peter Albert says:

          It’s also “just fine” for me, i.e. apart from the error message, everything seems to work normal…

          • Nick says:


            I get this “We were unable to update the linguistic schema” message too. I have a feeling it is related to the OLAP PivotTable Extensions. I have noticed that it happens when I Enable Auto Refresh (which might also explain why you get long data refreshs(because you may be using this add in to prevent those refreshes happening each time you go into PowerPivot? E.g. are you using linked tables?) Anyway, I have found no problems after the message as per everyone else. I am finding that extension more trouble than it’s worth though. Sometimes it’s hard to enable auto refresh and I think it’s messing with my macros.



    • I got the error message today and it was resolved after discovering I had misspelled a column name in a DAX formula. Hope this helps someone.

  63. Gavin says:

    I get the “unable to update the linguistic schema” error too. This page appears to be the only search result for this error message. Some of my measure names contain non-latin characters (Δ, Σ, Δᵀ, etc.), I’ve been idly speculating that this might have something to do with it.

  64. Christofer Broström says:

    When I create formulas/measures with text that contains a full stop, they are automatically translated to a comma. Eg the simple formula =IF([Price Origin]=”Man.”;1) will translate to =IF([Price Origin]=”Man,”;1)

    Perhaps this is due to my regional settings, as I use the Swedish decimal comma instead of decimal point. But clearly this should not affect text.

    Is there anyone else out there that has had the same issue and knows how to keep the period sign in my text?

    • Andreas Lindenberg says:

      Hi Christofer,
      I am experiencing the same problems with the German version of PowerPivot.

      Is only happens, when I enter a formula that contains a semicolon. As soon as I write =”Man.” is keeps the point.

      Right now, I am looking for a bugfix, but had no look so far. Any ideas anybody?

      • Thomas says:


        I know that different language versions of PowerPivot can have different syntax (and I am using an us-English version), but what happens when you replace the semi-colon with a comma?

        =IF( [Price Origin] = “Man.” , 1 )

        Just out of curiosity, I wonder if you would get a syntax error (like I get if I used a semi-colon) or if PowerPivot works with the comma or something else happens.

        Maybe worth a try…

        • Andreas Lindenberg says:

          Hi Thomas,
          thanks for coming back to my question!

          Entering a comma instead of semicolon as delimiter for parameters will result in a syntax error in the German language Version.

          It seems that PowerPivot does some conversions when it save the formulas between the local formula and an english formula. During that conversion, it seems that chars in strings are also converted, which is really, really bad!

          I noticed this some time ago and was hoping that there is some hotfix out somewhere, but I couldn’t find any.

  65. Don says:

    I have multiple pivot tables connected to slicers. The tables contain aggregated payment information. When certain slicers are applied one of the tables will be populated entirely with $0.00. I have a filter to suppress $0.00 so none of the rows display. However, the header row with column names remains. Is it possible to suppress the header row from being visible in situations where all of the rows below are not showing. Otherwise it just looks like an orphaned row of column headers.

  66. Ron Barrett says:

    Building an operational and financial dashboard can leave a PowerPivot developer with a very large number of measure definitions. Do you have any suggestions for managing, organizing, and reporting measures? Perhaps something like an auto-generated Measures Definition List?

    • Mat says:

      I’d like to be able to change the names of the measures in power view. I usually use long names for the measures so I’m easily able to remember what they are when I’m looking at the field list. But I’d like to use a shorter name on my powerview reports.

  67. Chad says:

    I’m trying to build a report that allows me to see how much inventory was received, shipped and on hand in a selected workweek. I have one table that has the workweek number, the Start datetime (since my workweek ends 4:00 on Friday), and the end datetime. I then have a table that has a product id, a received date, and shipout date. I’ve been trying to figure this out but have hard time determining how to do the inventory on hand especially as items can sit for multiple weeks.

  68. Sri says:

    I want to find out how to include the slicer values in a measure.

    I am trying to calculate “previous period amount” for revenue.

    I have in slicers the Months. However if someone decides to see more than one month and clicks January 2014,Feb and Mar 2014 the previous month amount (rather previous period amount) should return Oct,Nov Dec 2013 values.

    My formula now returns only Dec 2013 values since that is the previous month of the earliest selected date Jan 2014.

    • Avichal Singh says:

      From your scenario it seems you want to compare the selected period with the previous adjacent period.
      So if user selects Jan 2014, you want to compare that to Dec 2013.
      But if user selects Jan-Mar 2014, you want to compare it to Oct-Dec 2013.
      Correct me if I didn’t understand it correctly.

      Pretty unique scenario, typically I see Year over Year comparison; especially when there is seasonality involved. Example in the team that I am working for December is a dead month, really low in transaction volumes. Thus comparing Jan-Mar against Oct-Dec would not make sense. Instead if user selects Jan-Mar 2014, we would compare that to Jan-Mar 2013 (using the DAX SAMEPERIODLASTYEAR function).

      For your scenario I would suggest the following guideline
      a) Calculate the number of Months user has selected (May be do a COUNT of MONTHS in the Date dimension)
      b) Then shift the Date range for the measure using DATEADD. e.g. DATEADD(‘Date'[DateKey], -3 , month) …Instead of -3 you may use what you calculate in a)

  69. Mathieu says:

    There is a straight formula for this. I did the same thing. Previous month and previous 2 months. I would need to check as I don’t remember the formula. I took it from Rob’s book…

  70. Michael Goodwin says:

    Percentages of Totals
    I am trying to work out some DAX to calculate the percentages of one cell against the total of another column

    In one column I have total number of guests in a hotel for a particular day/week/month.
    In another column, i have the number of people who ate at each restaurant. They cannot be in the same column

    I need to work the percentage of guests who ate at the restaurant. Easy to do manually, but i want it inside power pivot with the slicers. These slicers will cover dates and times of the day.
    Looking for things like percentage of guests who had breakfast/lunch/dinner etc in a week/month/day of week.


  71. Marshall says:

    If you have a column for the ‘Total # of People in Hotel for a particular Day’ then you probably or should have a column for the Date of that day. Then I think you should have measures based on these two columns to give you ‘Total # People in Hotel for Week’ and ‘Total # People in Hotel for Month’ by doing a Calculate(Filter) on the Date and #People/Day columns.
    Are your times of the Day, ‘Breakfast’, ‘Lunch’ and ‘Dinner’? Are these values listed in the cells of a ‘Time of Day’ column?
    Your Day/Week/Month would be your x axis of your chart, and the # of People the Y axis and use the ‘Time of Day’ column as your slicer.
    That’s how I’m reading it. Hope that offers something helpful.

  72. Murray says:

    Not sure if this is the right site for a question – but here goes anyway. I have four identical databases. I would like to do analysis over the combined tables within the databases. The databases are separated four different contracts. Is there anyway Powerpivot can pull like tables from the databases into a single table within powerpivot? My SQL is pretty/very limited is there a query I can write in SQL to import these like tables into powerpivot as one table?

    Any thoughts ideas would be appreciated.

    • powerpivotpro says:

      Power Query. You seek Power Query. Another free download from MS. Give it a shot, it does many things in the data shaping space. And that specific scenario is just a straight-up button on its menu :)

  73. Michael Goodwin says:

    Thanks Marshall
    to give you the picture. I am trying work out the Meal % it shows at materialization of meals

    This is the “no of meals” vs the “guests” in a year within each meal period with in a restaurant
    The “no of meals” and “guests” are from 2 different sources

    Its easy in spreadsheet, but in power pivot i only see all guest total, not able to breakdown in slicers.

    Jan No of Feb No of
    Dept Year Meal Period Guests Meals Meal % Guests Meals Meal %

    Lobby 2013 Breakfast
    Dinner 3,223 60.31% 3,506 64.77%
    2014 Breakfast
    Dinner 5,071 47.94% 3,765 35.82%
    Outlet Total 8,294 51.97% 7,271 45.66%

    Bar 2013 Breakfast 1,223 22.89% 998 18.44%
    2014 Breakfast 2,233 21.11% 2,192 20.85%
    Bar Total 3,456 21.66% 3,190 20.03%

    Room 2013 NON F&B 5,344 5,413
    2014 NON F&B 10,577 10,511

    Rooms Total 15,959 15,924

  74. SV says:

    Dear All,

    I have a question related to PowerPivot.

    I have below table:

    Dates Interest rates Review Date Account number
    01/2009 4.0 01/2009 A
    02/2009 A
    03/2009 A
    04/2009 5.0 03/2009 A
    05/2009 A
    01/2009 3.0 01/2009 B

    I would like to obtain a new column with the following numbers:
    New column Account number
    01/2009 4.0 A
    02/2009 4.0 A
    03/2009 4.0 A
    04/2009 5.0 A
    05/2009 5.0 A
    01/2009 3.0 B

    In Excel I would generate a new colum with if(Interest rates = “” ; select previous value; select interest rate this row)

    However, in powerpivot this is not available.

    I tried:
    =calculate(max([Review Date]);filter(Table;earlier([AccountNumber]);[AccountNumber] && earlier([Date]) > [Date]))

    This formula takes very long to calculate… Also, how can I obtain my new column?

    Many thanks in advance,

  75. cliffrosell says:

    Afternoon, I have what I think should be a simple answer.

    I found an older article (http://www.powerpivotpro.com/2010/06/using-excel-cube-functions-with-powerpivot/) on PPPro to be great and have referenced it often. I am encountering 1 problem and I need help with.

    This is the CUBEVALUE() function to get this value for “Bikes” in Australia for Fiscal 2006 :=CUBEVALUE(“PowerPivot Data”,”[Sum of SalesAmount]“,”[Country].[Australia]“,”[Category].[Bikes]“,”[FiscalYear].[2006]“)

    In taking your example above if I wanted the value of all Fiscal Years I could just remove Fiscal
    Years to this:
    =CUBEVALUE(“PowerPivot Data”,”[Sum of SalesAmount]“,”[Country].[Australia]“,”[Category].[Bikes]“)

    My question is: How does the formula get written if I want to see all years EXCEPT Fiscal Year 2006? Meaning I want to exclude a single year from 20 years of data.

    Thanks all

  76. Paul says:

    Is there a way of adding sub rows to just one of the row labels. I.e. let’s say you have 2 measures for supply in row labels, “OH qty” and “Open Order Qty” (each from a different table), but you only want to show the suppliers (Acme & Road Runner) for “Open Order Qty” – can you do this in Powerpivot? As such:

    26-Oct 2-Nov 9-Nov
    Supply – OH Qty 38 20 …
    Supply – Open PO Qty 20 10 …
    Acme 5 10 …
    Road Runner 15 0 …
    Cum TTL 58 88 …

    I’m finding I do this by showing suppliers on OH Qty as well, which I don’t want.
    26-Oct 2-Nov 9-Nov
    Supply – OH Qty 38 20 …
    Acme 38 20 …
    Road Runner 38 20 …
    Supply – Open PO Qty 20 10 …
    Acme 5 10 …
    Road Runner 15 0 …
    Cum TTL 58 88 …

    Thank you!

  77. Thomas says:

    Paul, Have you tried a single measure pivot table (Open Order Quantity) with a slicer for suppliers?

  78. Mary Ann Prunier says:

    I’m creating my first Power Pivot model and am having an issue importing a Customer table. The table has about 215,000 rows with the unique identifier as the CustomerNum. I start with an Excel file that I then save as .csv file. Then I import the .csv file into the Power Pivot model. However, even though exactly all 215,000 rows import in, some fields that should be populated come in as blanks. (including the CustomerNum field.) And there’s no warning–it states the import was successful. Out of the 215,000 records, about 40,000 come in with the CustomerNum field blank. Same thing happens with a couple other fields too.

    Does anyone know why this is happening and how I can prevent data from importing as blanks when the fields should be fully populated? I chose to import via a .csv file because it is so large and Rob has cautioned that if your file is over 10,000 rows, creating a linked table will cause problems.
    Thank you!

    • Thomas says:

      Hi Mary Ann,

      I had an experience like that when I was new to Power Pivot and remember the grief.

      In my case, it turned out that the original data actually had many blanks in the “unique” identifier column (I do not remember the exact number, but I believe it was also well into the multi-thousands) . Somehow, somewhere in the export and import process those partial records moved up to the top of the Power Pivot table.

      In the source Excel file I eventually did a “Smallest to Largest” sort on the “unique” identifier column, and voila, there were the partial records .Before actually doing a physical sort, I had tried a quick, scrolling scan of the worksheet to look for blank values–but since the partial records were spread out in the original “215,000” it was like looking for needles in a haystack (even though there were thousands of them). It was the physical sort that produced the discovery.

      In my case, the experience went from feeling that “something was wrong somewhere between me and the process” to realizing that data, even when found among sample download files for a book or article, can be dirty and need cleanup before exporting and then importing into Power Pivot.

      The short term fix for me, considering it was sample data, was deleting the partial records after the physical sort, when the dirty data was at the top. If the data in the original Excel file was production data given to me by IT, I would have them look at the physically sorted data and ask what they could do about fixing it. A long term fix would be to look into Power Query, an extract-transform-load tool for Excel.

      Hope this helps.

      Please post your progress.

  79. Chris Gilbert says:

    Mary Ann,

    Another possibility is that the column (fields) coming up with blanks has “mixed type” data in it that confuses Excel/PowerPivot csv/txt import routine.

    There’s some minimal number of rows that Excel looks at to try to automatically determine the type of data (numeric, text, date, etc.) and then it tries to fit all of the data in that column to its selected type.
    So if the first (let’s say it’s) 50 rows are all numeric, but then later on there’s an alphanumeric value (like, 12N7604) then Excel will just replace that with a blank. This is a known issue with the text import algorithm – that as far as I know hasn’t been fixed.
    The easiest fix is would be to prefix all data in that column with a text character string, like =”ZZ”&ColumnReference.

    This of course means that you wouldn’t be able to do math operations on that field… but it was really an alphanumeric field to begin with.

    There’s also a laborious “old school” way of writing up a whole import data “schema” file where you call out the type of every field that Excel will use if it is present, but I found that to be quite confusing. Took me about 6 tries before I was able to get it to work by chasing down documentation on the internet for multiple different products… VB is a little differnet than VBA, which is a little different than VBScript, than Access, than ,,, (You get the idea.)

    Maybe the prefix idea will work for you.
    (or try the PowerQuery route that Thomas suggests.)

  80. Mary Ann Prunier says:

    Thank you Chris and Thomas. Turns out PowerQuery fixed my import blanks! Again, thank you!
    Mary Ann

  81. George says:

    Hi guys, I’m having a problem with some slicers. I have 2 tables in my data model connected through a relationship. The relationship works, because I can get data into one table from the other one using RELATED. The problem is that, when I’m adding in the Excel sheet a pivot table which gets its data from the first table, and I use a slicer based on a column in the second table, the slicer doesn’t work on the data from the first table. Any ideas?

  82. Thomas says:


    Need a little more information.

    1) Is the second table is the “related lookup” table and the first table is the “data table” or the other way around?

    2) Was the slicer created off of the pivot table in question, or does the worksheet have multiple pivot tables on it and the slicer was created for one of the other pivot tables.

    3) which version of Excel are you using (2010 or 2013)?

  83. General Ledger says:

    I am looking to see if any one of four values is in any one of four fields.

    I was using IF statements to test for each value in each of the fields. It looks very big and ugly, and is probably not efficient. Is there something better?

    My latest thought is to concatenate all four fields into one string of text. Then test for the presence of my search values in the concatenated text. If I was using Access, I would use InString but PowerPivot does not have this function. It seems like the FIND function will do.

    Any better ideas how to tackle this?

    • Thomas says:

      General Ledger,

      I am just thinking “out-loud” here, but how about:

      Create two calculated columns, the first one concatenating the four columns


      = [ColA] & [ColB] & [ColC] & [ColD]

      Then hide the concatenated column from client tools.

      Then create a second calculated column, where the expressions Text1, Text2, etc., have the hard coded values that you are looking for in the four columns


      = IF( FIND( “Text1″, [ConcatenatedColumn], 1, 0)
      + FIND( “Text2″, [ConcatenatedColumn], 1, 0)
      + FIND( “Text3″, [ConcatenatedColumn], 1, 0)
      + FIND( “Text4″, [ConcatenatedColumn], 1, 0) = 0, 0, 1)

      I think this would result in fast results for any measure that would use the [SuccessColumn], like:

      CountFinds:=COUNT( [SuccessColumn] )

      If inconsistent upper and lower case spellings are possible, consider SEARCH instead of FIND.

      Your thoughts would be appreciated.

  84. Mathieu says:

    I have a question; is it possible that dateadd don’t work with the function “all”?
    I’m trying this:

    and I’d like to replace the datesbetween by dateadd so it becomes a dynamic formula. I want to see all quarters (so I used the all function) because I want to have my actual quarter and compare with a complete year next year.

    Maybe parallelperiod is better in this case?

  85. Tom says:

    first to say, I really like your web page (thumbs up). Second, I am trying to solve formula problem in PowerPivot and it is not going very well :(. I tried posting to MrExcel and got tip to try asking you (as PowerPivot guru). So, here I am asking for help. What I’m trying to do is to get a calculated column in the DAX data model (Table1); which should give the same result as the yellow F column in the Excel table (interest payable); using only data in columns A to E. Here is my file, https://www.dropbox.com/s/vidl37swey…data.xlsx?dl=0
    Thanks in advance for any help you can give me,

    • Avichal Singh says:

      My gut feel is that you should not be thinking a “Calculated Column” but instead a Calculated Field (Measure). But I would need to learn more about your data and how you are planning to use it to be sure.
      See #1 on this list 5 common mistakes made by self taught DAX students . It is fairly common for folks transitioning from Excel, to keep thinking in Calculated Columns.

  86. Tom says:

    Thanks for your replay, and I have read 5 common mistakes made by self taught DAX students :). Thing is I need to use values that are calculated before current, in same calculated column and I can not do this because I get circular reference no matter how I wrap my formula.

  87. Mary Ann says:

    Hi folks,
    I have a rather simple question. I have a calendar table that contains dates through the end of Oct. 2014. (imported as a csv file.) Now I would like to update my model with sales data through the end of Nov. so I need to import a new calendar table that extends through the end of Nov. So I went ahead and imported the new calendar table and it ended up in my model named “Calendar1″. (My original calendar was simply named “Calendar.”) I thought at this point all I need to do is delete “Calendar” then rename Calendar1 as Calendar. As soon as I tried to delete the Calendar table, my computer froze. What am I doing wrong? I’m using Excel 2013 64-bit.
    Thank you.

    • Thomas says:

      Mary Ann, I think all you needed to do was name the new cvs file exactly the same as your previous csv and save in exactly the same location (overwriting the previous file) and refreshed the calendar table in the PowerPivot window. Assumed you have not changed the connection to the csv in the PowerPivot window and the structure of the new calendar info is exactly the same as the first (same columns, same data types, same column order).

      Deleting the Calendar table outright broke any formulas depending on it and calculated fields on the same sheet tab as the calendar table would have been lost altogether. Since the model resides in memory, you may have also caused some system problems on your machine. Do you have a copy of the original model?

      • Mary Ann says:

        OK I see what I’ve done wrong. I tried to IMPORT the new calendar when all I needed to do was a refresh. It came in fine now that I did a refresh.

        Thanks so much!
        Mary Ann

  88. Mary Ann says:

    Now that I’ve successfully imported my new calendar, I’m experiencing trouble refreshing my sales (data) table. My sales table was originally the result of a simply query run in SQL that selects invoice date, customerID, item number, & extended sales from a datamart table where invoice date ends on 10/31/2014. I created a connection to the database within PowerPivot and clicked through to the Table Import Wizard. From here I chose “Write a query that will specify the data to import.” So I wrote the simple SQL code and my sales table imported just fine.

    Now I want to “refresh” that selection so my data runs through 11/30/2014. But where did that selection statement go? I see that I still have a connection to the database, but I don’t see that selection anymore. I was going to update that code’s invoice date range to end on 11/30/14.
    I then created a brand new selection ending 11/30/14 and it imported into my data model just fine as a table named “query1″. Not quite what I needed since I need this new query to replace my old sales table.

    I’m obviously doing something wrong as this is one of my first PowerPivot models and the first to connect directly to a SQL database.

    Thank you.
    Mary Ann

    • TJ Durkin says:

      Hi MaryAnn,

      From the design tab, while looking at your Sales table, choose “Table Properties”. You may be prompted for your user password. Once the Edit Table Properties window opens, you should see your SELECT. If its greyed out, simply click anywhere in the SELECT statement. Eventually, the field will become editable so that you can update the SELECT (mine has a slight delay between clicking and the field becoming editable).

      Hope this helps!

      • TJ Durkin says:

        In short, you’re essentially doing the same thing that you did when re-Importing your calendar table. Opening through ‘Existing Connection’ creates a new table. Opening Table Properties is where you can adjust properties for the table that is already loaded into your model. When clicking Table Properties, it opens the properties for the currently viewed table – so make sure your Sales table is in view before clicking ‘Table Properties’

  89. Monstro says:

    I have a coworker who’ll just LOVE to hear there’s a portuguese translation.
    Thanks, Rob! Thanks, Kasper! Valeu, Thiago!

  90. Hi everyone,
    I have one of those “I know I’m over-complicating this” questions. In short: I need to calculate the percentage change between two dates that may or may not be contiguous.

    Quick summary (seriously, it’s not a tl;dr)
    I do the “daily” Lumosity brain training and once I’ve finished, I write down my scores in my journal. I put daily in quotes because much like going to the gym, my laziness exceeds my aspirations; this laziness is the cause of the intermittent dates :(

    The Basics
    My model consists of four basic tables:
    (1) tblAbsolute – this contains the absolute scores of my daily brain training
    (2) tblRelative – this contains the percentile I land in; within my age group
    (3) Calendar – basic calendar table that is marked as a date table
    (4) tblMetrics – the lookup table for the metrics

    Here is the structure of how my tables are related:

    ↓ ↓
    ↙[Date] [MetricID ]↘
    Calendar (Datekey) [MetricID] tblMetrics
    ↖ [Date] [MetricID]↗
    ↑ ↑

    The Problem
    In order to calculate the delta change I just need two calculations: the current day’s score and the prior date’s score. I can write a measure that takes care of the current day’s score, no problem. The problem is writing a measure that respects gaps in dates that doesn’t create a false date for the previous day.

    To calculate the current day’s score, I’m just summing the score column in tblAbsolute and then the calendar table is breaking it down into daily increments when dragged into the pivot table


    The best I can do to calculate the previous day’s score is using the DATEADD() function.


    Finally, here’s what the resulting dataset looks like:

    Date AbsScore AbsScorePD
    05/15 – 1,497 – (Blank)
    05/16 – (Blank) – 1,497 <—–this is "fake" data
    05/17 – 1,503 – (Blank) <—–this is where I need the measure to "skip" 05/16 and return 05/15
    05/18 – 1,503 – 1,503

    Technically speaking, the previous day calculation isn't wrong insofar 1,497 is the score for the "previous" day. It's just that I need a mechanism that will reference the previous day score without inserting a "fake" data point; such that the resulting dataset looks like this:

    Date – AbsScore – AbsScorePD – Delta
    05/15 – 1,497 – (Blank) –
    05/17 – 1,503 – 1,497 – 0.04%
    05/18 – 1,503 – 1,503 – 0.00%

    On an administrative note, I've posted the workbook for all to view (hopefully it works).


    Thanks again for the help!

  91. Thomas says:


    I created a calculated column (not calculated field) on tblAbsolute called [Prior Absolute Score]

    =CALCULATE( SUM([AbsoluteScore] ), TOPN(1, FILTER( tblAbsolute, tblAbsolute[Date] < EARLIER(tblAbsolute[Date])), tblAbsolute[Date] ) )

    then I created another calculated column on tblAbsolute called [Improvement]

    = DIVIDE ( ( [AbsoluteScore] – [Prior Absolute Score] ), [Prior Absolute Score] )

    Created new pivot table, added [DateKey] to rows, dropped [AbsoluteScore], [Prior Absolute Score], and [Improvement] in the Values zone. Renamed columns in pivot table as appropriate.

    Removed subtotals and grand totals.

    Interesting challenge.

    • Thomas, you magical person you. This was EXACTLY* what I was looking for!


      I had to make a minor tweak, but the output still contains 99% of your logic.

      The trouble was, your approach gave me the score for the previous day across ALL categories. Said differently, it’d be like me saying: “what is the daily percentage change of the Flexibility category versus ALL THE CATEGORIES”

      Whereas my goal is to calculate: “what is the daily percentage change within the Flexibility category?”

      So I just put another EARLIER() to evaluate the MetricID row context and now it works flawlessly!

      Here’s what the final calculated column looks like:
      . SUM([Absolute Score]),
      . TOPN(1,
      . FILTER(tblAbsoluteScores,
      . tblAbsoluteScores[Metric]=EARLIER(tblAbsoluteScores[Metric])
      . &&
      . tblAbsoluteScores[Date] < EARLIER(tblAbsoluteScores[Date])),
      . tblAbsoluteScores[Date]
      . )
      . )

      Here's the updated workbook: http://1drv.ms/1z9RWQH

      Again, thanks for the help!

  92. Thomas says:


    I thought it was really cool that you were finding ways to get benefits from PowerPivot in your personal activities, not just for “the job”!

    Your examples of how you wanted the data to look helped me most to respond; other details good, but I felt I could really grasp a solution once I saw the picture of what you wanted. Online workbook also a big plus.

    Congratulations on making the response work exactly for what you needed! Many thanks for the kind words.

  93. Mary Ann says:

    In order to share our PowerPivot workbooks within my company, I’m advocating for Power BI Online. But, our SQL database is 2008R2. Just wondering if this older version of SQL will play nicely with Power BI. (For example, will automatic refreshes work?) Any thoughts are appreciated!

    Thank you.
    Mary Ann

  94. Mary Ann says:

    That’s great, thank you! I certainly will keep you all posted. Right now we are trying to figure out the number of licenses we need and what the total cost will be.

  95. MikkelJJ says:

    Hi – Mikkel here

    Great site – really helped me with getting into powerpivot and dax, but I have a problem – I don’t seem to be able to get a hang of making dynamic calculations such as number of items sold pr reciet. =CALCULATE(SUM(RecDB[Sales]),RecDB[RecID]=33145293) => OK but making the count for every receipt fails – ex: =CALCULATE(SUM(RecDB[Sales]),RecDB[RecID]=[RecID])

    How do I get the RecID to load in a dynamically way line for line into calculate? (Do i need to make a 2nd related table to do this?)

    • Thomas says:

      Hello Mikkel,

      I think there is an easy solution for you.

      But I have two questions:

      1) Since you are new to DAX and Power Pivot, what is your comfort level for creating a pivot table from Power Pivot data?

      2) How would you describe/quantify the number of receipts (RecIDs) you are analyzing in a single Excel workbook? is it hundreds, thousands, tens of thousands, etc.)? With receipt numbers like 33145293 I can picture many receipts being analyzed in a single workbook.

      My solution is just to create a measure (calculated field) called

      Number of Items Sold

      and the formula is just

      = SUM( RecDB[Sales] )

      Next, create a Power Pivot pivot table, put “Number of Items Sold” is the Values drop zone and RecIDs in the Row Labels drop zone–and you will have a table that, for each receipt (RecID) you will have the Number of Items Sold.

      • MikkelJJ says:

        Hi Thomas

        Thanks for your reply!

        1) Well – I usually work in pivots – and have made the fundamentals of a basket analysis within one.. for some reason this one measure is just tricky…

        2) Not sure yet about it solving my issue – as I have 2.000.000+ RecID’s (10 million+ Table rows – not all loaded for the test)

        Trying it out – put would prefer to have a calc field in the data for reference in other calc fields. :)

        • MikkelJJ says:

          So – I found the solution, basically I tried this earlier – and I misread the error … [Error was due to RAM – 32 bit life is hard in powerpivot I guess].
          Now on a smaller data set i use: =calculate(sum(RecDB[Sales]);filter(RecDB;RecDB[ID]=EARLIER(RecDB[ID]))) and it works like a charm…

  96. ryanw says:

    I need a fast way to calculate medians. There are many solutions online but all of them use Rankx() or TopN() which slows down my huge data sets.

    I would like to use Power Query to pre-sort my data. Then Power Pivot would not need to use Rankx()… it would only need to retrieve the middle value knowing that this is already the median. Let’s ignore the nuances of ties, even-odds, etc.

    Any thoughts on how to implement this?

  97. Thomas says:


    Check out this site for DAX code for median:


  98. Louise Bown says:

    Power BI and publish on sharepoint

    Hi –
    There seems to be a lot of ambiguity over licencing for PowerBI.
    Can you advise on whether powerBI reports (powerpivot, powerview, powermap etc) can be published so external people can access without having the software themselves. They would not need to change the report – only use the slicers etc. already set up.


  99. Alexis says:

    Hi all,

    Sorry if this is a silly question but I’m struggling to find an answer.

    I have a sales table and a company name lookup table. The relationship columns are on the company name.
    My company name table has names in it that do not appear in the Sales table and vice versa. The reason it’s set up like this is because the company table is a list of target groups each containing individual companies. Some we have supplied, some we haven’t.
    I want to use the company[company name] as the row label and the [total sales] measure from the sales table as the value to get a list of sales to each company with blanks for companies we have not supplied. However, when I add in [total sales] the companies that we have not supplied disappear. How do I overcome this?
    I get the desired result from taking the company list and writing a standard SUMIFS() function that returns 0s for companies not supplied, but I want a measure to use within the pivot table.
    I’m sure I’m missing something fundamental with the way relationships work, but my brain hurts now and I just want it to work!!!
    Thanks in advance for any help, I love the website and PowerPivot (along with the other PowerBI suite) has allowed me to do some awesome stuff that I couldn’t have done without it.


    • Thomas says:

      Hi Alexis,

      I am using Excel 2010, if you are using 2013, it should be similar:

      Click on the pivot table.

      On the PivotTable Tools context menu, select the Options tab (in 2013 it may be renamed “Analyze”), look for the PivotTable group (in 2010 it is all the way to the left on the Options tab). Click on the Options dropdown.

      The PivotTable Options dialog will appear. Select the Display tab. About half-way down is a checkbox for “Show Items with no data on rows” (assuming company names are on pivot table rows, if not, you will want to check the option “Show items with no data on columns”). After the check-mark appears, click on the OK button. You should be good to go

  100. Craig says:


    Is there any way to increase the text size of the PowerPivot field list when you are selecting fields for a powerpivot pivot table? (font size is real small…I think 8 points..I’d like to make it larger
    thanks for any help

    • Chris Gilbert says:

      I found that in Windows 7 Excel 2013, that the Field List font responds to a change in the Windows Default Font Size (but, you can still zoom in/out the Excel & PowerPivot “body” by using the [+/-] zoom slider in the lower right-hand corner of the Status Bar at the bottom of the screen).

      Go to Control Panel | Appearance and Personalization | Display, and up the scaling factor to the default [select 125% or 150%] or select the “Set custom text size (DPI)” option in the left-hand pane. I found that 115% works pretty well for me.

  101. Thomas says:


    If you find out how, other than changing screen resolution or changing monitors, please post.

  102. Craig says:

    Hi anyone,

    Can you pull in data from multiple Excel worksheets into one powerpivot table? (I am using Excel 2010) Or do you have to bring data together from separate tables in Excel into one Table in Excel first and then use the Create Linked table icon to get it into one PowerPivot table?

    thanks very much for any insight!!

    • Thomas says:


      If you are using Excel 2010 professional plus, you could use Power Query to do that for you.

      • Craig says:

        Thank you very much!

        I see I have to download that.

        I will be using Excel 2013 Professional plus soon so i imagine I will have to download that too.

        thanks again!!!

  103. Chris Gilbert says:

    Try this link. I found that I couldn’t just cut & paste their code – something about their single quotes that PowerPivot didn’t like. After pasting the code, simply replace their single quotes by typing in your own… worked great. They also have some code over there for reading in/appending identically formatted .csv files.

    Advice… try it with a few small files first.

    I used these over a year ago. PowerQuery didn’t exist back then, so if PQ works, I’d probably encourage that route.

  104. Mandy says:

    I have a table with several client IDs, some repeated. Each row of the table has a date of service. I am trying to calculate the difference in each date of service in a calculated column, but nothing I try is working. Is there a way to do this?Basically I am trying to get the number of days, so I can find out if it was >90 or <90.

    Client ID Date of Service
    1 1/1/2014
    1 2/5/2014
    1 3/8/2014
    3 7/1/2014
    3 5/5/2014
    2 12/1/2014
    2 1/1/2014

    Any suggestions?

    • Chris Gilbert says:

      I used two calculated columns to pull this off…

      a) PreviousService
      FILTER(Service,[Client ID]=EARLIER([Client ID]) && [Date of Service]<EARLIER([Date of Service])),
      [Date of Service]

      b) DaysSincePrevService
      =if(ISBLANK([PreviousService]),0,1.0*([Date of Service]-[PreviousService]))

      Should get you started… (I didn't fix all the totals/subtotals)

      Client & Date Sum of Days since
      1 66
      01/01/14 0
      02/05/14 35
      03/08/14 31
      2 334
      01/01/14 0
      12/01/14 334
      3 57
      05/05/14 0
      07/01/14 57
      Grand Total 457

    • Chris Gilbert says:

      Oh, and I had a Calendar table with consecutive dates for the year 2014 with a relationship between the service date and the calendar date.

    • Thomas says:

      Hello Mandy,

      Thank you for giving sample data to work with.

      Hopefully this answer will work for you — I am currently using Excel 2013.

      I made a linked table into Power Pivot with your data and named it Service.

      Then I created a calculated column called “Next Serviced On” and I used this formula:

      = CALCULATE (
      FIRSTDATE ( Service[Date of Service] ),
      FILTER ( Service,
      [Client ID] = EARLIER ( [Client ID] )
      && [Date of Service] > EARLIER ( [Date of Service] )

      Then I created another calculated column and called it “Difference Between Service Days” and used this formula:

      = IF ( ISNUMBER ( [Next Serviced On] ),
      SWITCH ( TRUE (),
      1.0 * ( [Next Serviced On] – [Date of Service] ) > 90, ” > 90 “,
      ” 90, “If serviced today, will be more than 90 days”,
      “If serviced today will be 90 or fewer days” )

      As far as presentation in Excel, if you have Excel 2013, you can create a linkback table instead of a pivot table.

      • Thomas says:

        Realized that last formula was not copied correctly, it should have been:

        Difference Between Service Days = IF ( ISNUMBER ( [Next Serviced On] ),
        SWITCH ( TRUE (),
        1.0 * ( [Next Serviced On] – [Date of Service] ) > 90, ” > 90 “, ” 90, “If serviced today, will be more than 90 days”,
        “If serviced today will be 90 or fewer days” )

        • Thomas says:

          After posting again, and this time carefully checking before submitting, the second formula was altered during submittal. This time I replaced > with &gt and < with &lt. Also I noticed that double quotes have also been altered to a begin quote and an end quote.

          Difference Between Service Days = IF ( ISNUMBER ( [Next Serviced On] ),
          SWITCH ( TRUE (),
          1.0 * ( [Next Serviced On] – [Date of Service] ) &gt 90, " &gt 90 ",
          " &lt= 90 " ),
          SWITCH ( TRUE (),
          1.0 * ( TODAY () – [Date of Service] ) &gt 90, "If serviced today, will be more than 90 days",
          "If serviced today will be 90 or fewer days" )

          Sorry for the mess.

  105. Mat says:

    I had a strange problem yesterday. Here it is, with what I did to solve it, if it can help some people.

    I started using Power Query a few months ago, to be able to refresh files on Power BI. But, I used power query only to get data from SQL / Azur. I Added new columns and did my filtering in the power pivot model. It was all working fine until yesterday. I had a strange error when I tried to go into PowerPivot / Design / Table properties. It said that the table was created with Power query so I can’t modified it in power pivot. Then, I went into power query to modify the table (had some filtering to do) and power query sent an error message saying that the table had previously been modified by power pivot so I need to go in powerpivot to modify it again.
    Here is the exact message I had:

    “This table was created with Power Query. To change this table, use Power Query instead.”

    Anyway, after a few trials, we figured out that windows installed a few updates that created that problem. I had 2 options then.
    1- Rebuild my model and do all my calculated columns in power query which could take something like 25 hours
    2- Uninstall/reinstall Office and don’t do the windows update.

    I tried the 2nd solution first and it works. I know I need to do the 1st one at some point…. I just postponed the problem.

  106. Jon Pearce says:

    I’ve been working with PowerPivot in Excel 2013, which shows to be Version 15.0.4693.1002. Recently we installed Excel 2013 on a server for Remote App use, and the version that installed was 15.0.4659.1001, which appears to be an older version although it doesn’t want to update with the Update Options tab on the Account screen. The problem is that the Remote App version says that PowerPivot models created with the newer version are corrupt, and it can’t load them. Is there some incompatibility among Excel 2013 PowerPivot versions, and if so how do I get around it?


  107. Mike Rothschild says:

    When I drill down from a cell in my powerpivot table, it’s not filtering per the calculated field. It’s showing me the rows for all values of [current_rate_cd]. This is the calculated field:
    =COUNTROWS(FILTER(CAEXAMMERGED,[current_rate_cd] = 1))

  108. Thomas says:

    The drilldown will list rows of the table that the calculated field belongs to.

    Would it make sense to move the calculated field and associate it with a different table?

  109. MIke Rothschild says:

    So drilldown only filters rows? I was hoping there was a way to create a calculated field that would enable the desired drilldown.

    • Thomas says:

      Hello Mike,

      What would the desired drilldown look like to you?

      I assumed your experience was double-clicking inside of a calculated field inside of a pivot and rows that would support the calculation in the current cell would appear on a different sheet in the workbook.

      What do you need to happen?

  110. @C1nders says:

    A cell that is the intersection of district (row) and ca_rating_cd =1 in the pivottable should yield just those rows in the result table by double-clicking. The district is correct but it shows me all the ca_rating_cd. I just want the ones.

    • Thomas says:

      After working with data, I realized that I misunderstood Mike’s request. There is a feature called drill-through that I mistook instead of term originally used by Mike: drill down (drill through being an effect created by double-clicking inside of a pivot cell containing a calculated field; drill down being an effect created by clicking on a plus sign next to a pivot row or column label when multiple labels are associated with rows or columns).

      Instead of using a measure, the desired effect can be realized by using a slicer.

      Regarding the calculated field,

      =COUNTROWS(FILTER(CAEXAMMERGED,[current_rate_cd] = 1))

      this had no filter effect in my model. Calculated fields (meant to aggregations) are not meant to be used primarily as filters (unless no rows are returned). This calculated field will probably always return “rows” because the row indicating no rows returned by the filter expression is still one row to be counted by COUNTROWS.

      To @C1nders:

      My guess is that you are someone trying to help out Mike Rothschild because you reference ca_rating_cd, where Mike used current_rate_cd and he requested to see-only-the-ones and wondered why he got “all”. Your kindness in helping out helped me, too.

      • MIke Rothschild says:

        I’m using EXCEL 2013

        • Thomas says:


          Glad to hear that you are using 2013, I’ll switch to my laptop where I have 2013 also.

          In 2013, there is also more than one way to create a slicer.

          How I create a slicer in 2013 is click inside my pivot, and when the PivotTable Tools context tab appears in the ribbon, I choose the Analyze tab, go to the Filter group, and then choose Insert Slicer. I do not use the slicer on the Insert tab of the ribbon.

          Did you try the formulas for calculated columns with slicers?

          If you wanted to email an attachment, I have an address on gmail that before the @gmail.com is


  111. Martin Short says:

    I’ve been working with Power Query and Power Pivot for about 4 months now & think I have a pretty good grasp of the basics plus one or two more advanced techniques. It’s nevertheless true that even at this relatively early stage it has improved my productivity and almost, but not quite, replaced my use of VBA. The reality is that, even in a medium sized company with a reasonably sized IT department, most of the data I receive is ‘dirty’ (i.e. excel spreadsheets from non-excel experts) and has to be cleaned up before I can use it. I therefore find I use Power Query as much, if not more, than Power Pivot. My question is, assuming minimal transformations, does using Power Query to import data into the data model use more resources/memory than importing directly via Power Pivot? My instinct tells me ‘no’ but my IT department – most of whom don’t know much about PQ or PP (& those that do think Power Query is for importing data from the Web) says ’yes’. I ask as my current standard procedure is to import/transform data via PQ, then analyse/report in PP. However, if there is a large impact using PQ I may have to rethink.

  112. Thomas says:

    Hello Martin,

    You should be able to check memory usage on your machine (unless using terminal services or your Power Pivot workbooks are loaded with data on a server).

    I think it is possible that your IT department may be right. A scenario that I can picture is Power Query using available memory to optimize the speed of transformations. Because your source may contain as many or more rows as imported into Power Pivot, memory consumption to extract-transform-load may be equal to or greater than memory required by Power Pivot to analyze the data. Also (because Power Query does its processing while your workbook is open), if Power Query is cleaning up “new” data to refresh the Power Pivot model and the “old” data is not deleted until after the cleaning up process is complete, you may have a “doubling” of memory consumption during that interval.

    Your IT department should also have the ability to measure memory consumption (especially if using terminal services) and show you the actual results of using Power Query.


    Please post-back with any insights on this potential issue. If only the local machine’s memory is being impacted, perhaps no big deal, but otherwise, may be a big deal.

  113. Mike Rothschild says:

    I did try a slicer but it filtered everything in the pivot table. Not too bad, but i twould be great if I could construct the “Total” measures to protect against the slicer. I tried to mofidy one with ALL() but I could never get past the syntax:

    • Thomas says:

      Hello Mike,

      It would help me to know what your model structure is–I do not need to know what the “real” data is, because I can make up my own data–but the structure of the model is important in order for me to give a good answer. I can figure out that you have at least one table: CaMergedExam with at least one column, current_rate_cd. Is @C1nders correct when they say there is a separate table that contains a district column?

      I know that we can figure this one out–most likely with a slicer, but it would be helpful to know a little more about the relevant parts of the model. If there is only one table, let me know, but it sounds like that there is at least two tables.

      It does not seem right that a single slicer would filter out everything in the pivot table with one click, unless there were other filters in effect.

  114. Ivan Perez says:


    Could you help me to know how to import a matrix in power pivot? I hava a table where year and every month is a column, but I need year in one column, all months in other column.


  115. Mike Rothschild says:

    @C1nders was me trying to log into facebook.

    There is only one table – the one you mentioned – CaMergedExam

    This is my pp table:
    (need to send you an attachment)

    These are the calculated fields which are accurate:

    Total Exams Current – =COUNT(CAEXAMMERGED[current_rate_cd])
    Current Ones – =COUNTROWS(FILTER(CAEXAMMERGED,[current_rate_cd] = 1))
    Total Exams Prior – =COUNT(CAEXAMMERGED[prior_rate_cd])
    Prior Ones – =COUNTROWS(FILTER(CAEXAMMERGED,[prior_rate_cd] = 1))
    Pct Ones Curent – [Total ones]/[Total Exams]
    PCt Ones Prior – [Prior ones]/[Total Exams Prior]
    Pct Ones Current vs Prior – [Percent Ones Current]-[Percent ones prior]

    Oddly, the slicer really does screw up the table:

    (need to send you an attachment)

  116. Thomas says:


    Regarding slicers, let me say if you are using Excel 2010, there are at least two ways to create a slicer, one of which does really mess things up, the other way has always worked for me.

    I do have a solution in mind that should work for you just based on the information you have given so far, I just need to test it out. It would add a calculated column (not calculated field) and I am confident it will give you the functionality you are looking for.

    Let me just ask if you are using Excel 2010 or 2013?

    • Thomas says:


      Let me give you what I have and you can try it out–it “checked out” for me.

      But before I give you a formula or two, let me say in Excel 2010, I typically do not create slicers in the Slicers Vertical and Slicers Horizontal drop zones of the field list because to some extent Excel controls the placement of those slicers and I have had times when the slicer was placed right over my pivot, and when I moved the slicer out of the way, back it returned over my pivot.

      What I do instead is click inside my pivot, and when the PivotTable Tools context tab appears in the ribbon, I choose the Options tab, go to the Sort and Filter group, and then choose Insert Slicer.

      So here goes.

      Create a calculated column with the following formula and give it a name like “Current Status”:

      = SWITCH ( TRUE (),
      [current_rate_cd] = 1, “Current Ones”,
      ISNUMBER ( [current_rate_cd] ), “Current Others”,
      “No Current” )

      and then create a slicer for the calculated column as described above. I tried it with different pivot configurations and it always worked well for me to show only the desired rows . If it still somehow messes up your pivot, let me know how and we can go from there.

      If it does work out for you, you may be interested in creating a second slicer based on the following formula for a calculated column and name it something like “Prior Status”:

      = SWITCH ( TRUE (),
      [prior_rate_cd] = 1, “Prior Ones”,
      ISNUMBER ( [prior_rate_cd] ), “Prior Others”,
      “No Prior” )

      One other thing to remember about slicers, is to clear the slicers (click over the funnel in the upper right corner of the slicer) before adding or removing items to/from the rows, columns and filters drop zones of the field list.

      If this does not work for you, let me know what occurred and we will get things solved another way.

  117. Craig says:

    I was wondering if the KPI command was very different in PowerPivot for excel 2013 vs PowerPivot for Excel 2010. I am writing procedures on how to create a KPI using my laptop with Excel 2010 (e.g. basic stuff like click the Create KPI icon, then set your target, define your color thresholds, set your icon type, then click OK. Then edit or delete your KPI (the other 2 icons on the ribbon) but I dont have Excel 2013 yet and I need to submit KPI steps for that version as well.

    thanks for any help!


  118. Mandy says:

    I have 1 and 2 in a calculated column, and I want to change 1 to Yes, and 2 to No. Is there a way to do that. The switch and if statements give me errors.

    • Avichal Singh says:

      Can you post the DAX Formula that is erroring out and the exact error message?

      • Mandy says:

        Calculated Field 1:=if(VALUES([Criteria])=1,”Yes”,”No”))

        • Thomas says:

          Hello Mandy,

          I created a table and named it Table1 with a single column named Criteria. I added a single row that had a value of 1 for Criteria.

          After importing Table1 into PowerPivot, there were two issues with the formula you have:

          1) The last parenthesis needs to be removed.
          2) You need to prefix the column name with the table name.

          In my case, the successful formula looked like this:

          Calculated Field 1:=if(VALUES(Table1[Criteria])=1,”Yes”,”No”)

          Another issue with your formula as it appears is that the quotation marks are not the correct type of quotation marks (they are slanted, not vertical), but I think they may have been changed when you pasted the formula into the comment window.

          • Thomas says:


            For your case, since you will have more than one row in your table, you will want to make a check like this:

            Calculated Field 1:=IF ( HASONEVALUE( Table1[Criteria] ) , IF ( VALUES( Table1[Criteria] ) = 1, “Yes”, “No” ) )

  119. Thomas says:

    Try a calculated column with switch instead.

  120. Thomas says:

    Fascinating video on youtube showing soon-to-come features (custom shapes) in Power Map.


    Has anyone heard of any other soon-to-come features for Power Map?

  121. Mandy Bjork says:

    PATID ServiceDate Previous Service Days Since Previous Days Between Svc Criteria
    A 5/5/2014 0
    A 7/17/2014 5/5/2014 73 73 1
    B 5/12/2014 0
    B 7/3/2014 5/12/2014 52 52 1
    B 7/28/2014 7/3/2014 25 25 1
    B 9/18/2014 7/28/2014 52 52 1
    B 11/17/2014 10/20/2014 28 28 1
    B 12/18/2014 11/17/2014 31 31 1
    C 4/21/2014
    C 5/19/2014 4/21/2014 28 28 1
    C 6/23/2014 5/19/2014 35 35 1
    D 4/7/2014
    D 7/17/2014 4/7/2014 101 101 2
    D 1/5/2015 7/17/2014 172 172 2

    Previous Service
    MAXX(FILTER(‘Prescriber_90_Day_Med_Review’,[PATID]=EARLIER([PATID])&&[ServiceDate]0,[Days Since Previous Svc])

    if([Days Between Service]>=1 && [Days Between Service]90,”2″))

    Calculated Field:
    Criteria Met: if(HASONEVALUE(Prescriber_90_Day_Med_Review[Criteria]),IF(VALUES(Prescriber_90_Day_Med_Review[Criteria])=1,”Yes”,”No”))

    First, thank you so much for your help so far. I also bought the book and plan on attending the 2 day training online.

    1) The measure of data that I am looking to achieve is that I have to get a yes for a distinctcount of a PATID (only count it once) if they had a service date at least 1 time in 90 days, and a no for a distinctcount of a PATID (only count it once) not seen at least once in 90 days.

    2) I then need to get percentages of the overall total to display in a pie chart: of the “Yes” and “No”

    The problem I am having is the blank lines are being included in the percentages, and I am counting the PATID more than once. So the current formulas are not completely accurate.

    Is there a way to make a new calculated column to give me the Yes, No, % by client?

    • Thomas says:


      Just posting here to let you know I am reviewing your comment and will get back to you shortly.

      There are several great books out there by either Rob Collie or Bill Jelen (or collaborating together); which one did you buy?

      • Mandy Bjork says:

        I got the one that is pictured on this site, by Rob Collie

        • Thomas says:

          Hello Mandy,

          Let me first answer your question with a “Yes”, that there is there a way to make a new calculated column to give me the Yes, No, % by client, but before getting into the details, I wanted to give you some other calculated fields to answer your other questions:

          PATIDs:=COUNTROWS( DISTINCT( Prescriber_90_Day_Med_Review[PATID] ) )

          ALL PATIDs:=CALCULATE( [PATIDs], ALL( Prescriber_90_Day_Med_Review ) )

          PATIDs Meeting Criteria:=CALCULATE ( [PATIDs], FILTER( Prescriber_90_Day_Med_Review, [Criteria Met] = “Yes”) )

          PATIDs Not Meeting Criteria:=[ALL PATIDs] – [PATIDs Meeting Criteria]

          % of ALL PATIDs that Met Criteria:=DIVIDE ( [PATIDs Meeting Criteria], [ALL PATIDs] )

          Also, an alternate to you first calculated field for [Criteria Met], let me give you an alternative for a calculated column, that you could call “Criteria Satisfied”:

          =SWITCH( TRUE(), [Criteria] = 1, “Yes”, [Criteria] = 2, “No”, BLANK() )

          Let me know what you think so far, would like to carry conversation further about possible model improvements.

          Let me know if I am “off” target or “on” so far or if I can answer any other questions.

          • Thomas says:

            Hello Mandy,

            I do not know if you had the time yet to check out the calculated fields posted yesterday, but here is one more to complete the set:

            % of ALL PATIDs that Did Not Meet Criteria:=DIVIDE ( [PATIDs Not Meeting Criteria], [ALL PATIDs] )

          • Mandy Bjork says:

            My only question is does the % and yes no per PATID only count Yes, one time when they are seen within 90 days (6 months?) For example, PATID B was seen on 7/3/2014 and 7/28/2014, twice in 90 days, but I only want to count one of them as Yes…

          • Mandy Bjork says:

            Thank you so much for your help.

          • Mandy Bjork says:

            I think it would also help you to know, that I have not learned how to use power query yet. I am hoping to learn how to do that in one of the training classes coming up. At this time, the model is built using MS Query into Excel, then the excel is connected via powerpivot. My hope is to use powerquery instead and maybe that has more flexibility for setting criteria in advance, instead of pulling all of the fields in for the 90 day time frame…

    • Thomas says:

      Hello Mandy,

      To answer your last question:

      Does the % and yes no per PATID only count Yes, one time when they are seen within 90 days (6 months?) For example, PATID B was seen on 7/3/2014 and 7/28/2014, twice in 90 days, but I only want to count one of them as Yes…

      Both “% of ALL PATIDs that Met Criteria” and “% of ALL PATIDs that Did Not Meet Criteria” effectively count a PATID only once, and as the formulas are written, there will never be a situation in the table where one PATID will belong to both “Met Criteria” and “Did not Meet Criteria”..

      The calculated fields [PATIDs] and [ALL PATIDs] are only intermediate measures and exist only to make the other calculated fields easier to maintain (they should be hidden from client tools)..

      Would like to continue comments further about your model design, it can be made more intuitive to work with.

      If you would like to continue the conversation about the data model and how it could be made more intuitive, let me know.

      Otherwise, I look forward to your next comment.

      • Mandy Bjork says:

        All PATIDs returned the same number as the PATIDs Countrows Distinct (first formula you gave above). Then, I am really still wondering if the % and yes no per PATID only count Yes, one time when they are seen within 90 days (6 months?) For example, PATID B was seen on 7/3/2014 and 7/28/2014, twice in 90 days, but I only want to count one of them as Yes…

        For example, I have the following in my data model, if somehow I could just return a yes or a 1 only one time per client and a no or a 2 only one time per client, I think that would help.

        PatID: Criteria Seen in 90 Days Not Seen in 90 Days
        A 0
        A 1 1
        B 0
        B 1 1
        B 1 1
        C 0
        C 1 1
        D 2 2

        Seen in 90 Days: if([Criteria]<=1,[Criteria])

        Not Seen in 90 Days: if([Criteria]=2,[Criteria])

        Do you think I would benefit to change any of these? So that the formulas count one once? I did just see your reply, so I am also trying those formulas as well. I would like to talk more about the model itself.

    • Thomas says:

      Hello Mandy,

      I can create a pie chart in excel using a technique that queries the data model. It will be very useful in creating the pie chart you are looking for. The pie chart will have two wedges, “Met” and “Not Met”. Is that what you expect the pie chart to show you (two wedges)?

      My guess is that your grief is coming from the complex relationships between the calculated fields associated with your single table model…

      Do you have Excel 2010 or 2013. It will make a difference to the formula I use.

      Let me know and we will go from there.

      • Mandy Bjork says:

        That is exactly what I would like to do. I am using excel 2013. Plugged in all the new formulas, and the % seem to be more accurate now. I also added in Powerview, as the hope is to have a dashboard. Although, It is getting an error, “Excel cannot open the file ‘ATPVBAEN.XLAM’ because the file format or file extension is not valid. Verify the file has not been corrupted and that the file extension matches the format of the file.” I am hoping that I have not corrupted my model by adding in the powerview tab. Anyway, I have a back up.

        I did notice that when I created a pivot table of the “provider name” and % of all PATDs that met critera and % of all PATIDs that did not meet criteria, the overall % looks ok, but the Pivot fields are not. When I drill down into the data it shows Yes and No included. So somewhere the formula is off.

        I think the problem is this formula: because it is saying if it is not a Yes it is a No. Therefore, maybe it is including the blank cells also?

        Criteria Met: This is being used in the formulat for meeting criteria, which the % are based off of

        Meeting Criteria: I tried to change it to pick up the calculated column Criteria Satisfied instead, but it gets an error saying too few arguments passed in the filter function

        CALCULATE([Client],FILTER(Prescriber_90_Day_Med_Review[Criteria Met]=”Yes”))

        My overall goal is to rollup data into the following:
        1) Yes, No pie chart
        2) Provder Name, % met and % not met
        3) Program Name % met and % not met

        At this time, the pivot by provider looks like the following:

        % of Clients Seen % of Clients not Seen
        5 % 95 %
        11 % 89 %
        6 % 94 %
        14 % 86 %
        1 % 99 %
        17 % 83 %
        4 % 96 %
        14 % 86 %
        4 % 96 %
        Grand Total 73 % 27 %

        I think that looking at the table I first sent in would help, as I am still seeing that the PATID Service Date is being picked up more than once in the 90 day time frame.

        • Mandy Bjork says:

          So basically it seems like the % for not seen if off… So close with this model, but still not quite there yet.

          • Thomas says:


            Thanks for the update.

            Given the current source table’s “shape” (its a single table that has complex relationships between a couple of the columns) in the data model, a pivot table is not currently the right way to create the pie chart you want.

            Do you know how to add a linked table that has a single row for each PATID and make a relationship to the main data table? If you know how, please do so and let me the name of the new linked table in Power Pivot.

          • Mandy Bjork says:

            Hi Thomas, I couldn’t reply to your comment below, and I agree, I need to have a different table to pull from, because I am not just making one pie chart off of it. I am showing the data in three different ways, by provider, by program, and overall summary. I don’t know how to create a link back table, but I will see if I can figure out how.

            After digging the Service Date is important because I have to know if the client was seen once every 90 days. The query is pulled for 6 months of service dates. The following formula ends up giving me zeros in some of the rows, because there is not an earlier date of service to compare against, that seems to be throwing off the formulas. Will a linked back table help me?

            Here is the formula that is returning some 0: MAXX(FILTER(‘Prescriber_90_Day_Med_Review’,[PATID]=EARLIER([PATID])&&[date_of_service]<EARLIER([date_of_service])),[date_of_service])

        • Thomas says:


          Here is a link for a Word document that contains before/after screen shots of model diagram.


          I will probably set it aside for today and return tomorrow to work on the measures.

      • Thomas says:

        Hello Mandy,

        Great to hear from you.

        The first steps that I will take this morning are:

        1) Rename the table “Test Data” to “Services” and I will use “Activities” as the main data table.
        2) Next, since the values under SvcID repeat, they probably represent clients or accounts (service recipients) of some sort, so I am going to rename that column,, SvcAcctID.
        3) Then I am going to make an entity table (a special type of lookup table) for unique SvcAcctID and name the table SvcAcctIDs. I will add columns to the SvcAcctIDs table for “Svc Start Date” and “Svc End Date”, these will represent the days when the account was first opened, and when a SvcAcctID is no longer active, it will have a date in “Svc End Date” For now, I will leave both “Svc Start Date” and “Svc End Date” blank, but will populate them during testing. .
        4) Then I will add separate “lookup” tables to replace some of the columns with repeating values like “Type”, and “Program”.
        5) To take advantage of built in time intelligence functionality, I will create a calendar table to replace some of the date related columns such as Month #, Svc Month, Year, Quarter Number, and Quarter (but Service Date will stay inside of the Services table).
        6) Then I will revisit the measure for counting Sevice Dates that fall within 90 days (and those that do not.

        As I am working, I will listening for messages that have your questions that you post on powerpivotpro.

        comments messages from your posts that may be asking questions.

        Note: There are currently 3 records for SvcID 677774 on April 9, 2014. I will remove two of them.

      • Thomas says:

        Hello Mandy,

        The issue appears to be that the model is actually setup to answer the question, “When the SVDID was serviced, was it “on time” (had a prior service date within 90 days) or “not on time” (did not have a prior service date within 90 days). From your concern regarding low numbers, this is not the business question you are trying to answer.

        I think the business question you are trying to answer is more like, “which SVCIDs had at least one service date for every 90 days” If this is more like the question your are trying to answer, your model needs to change.

        Noticeably missing in your model is a separate “dates” or “calendar” table (explained in Rob Collie’s book) and something like a “service accounts” table which would consist of at least columns for SvcID, SvcStartDate, LastSvdDate.(one row only for each SVCID). The “service accounts” table would have a relationship with the data table on [SVCID] and the “dates” table would have a relationship with the data table on [Service Date].

        A metric you may be also interested in is, “From today, how many SVCIDs have not been serviced within the last 90 days”.

        • Mandy Bjork says:

          Hi Thomas,

          Thank you for taking a look. Our measure is as you stated, “which SVCIDs had at least one service date for every 90 days”

          My learning curve is as follows:

          How would we extract into a separate table from the data that I have already added to the powerpivot model, the SVCID, First SVC Date, and Last SVC Date?

          Would I still need to make a seperate calendar table?

          Would I link back tables, or use powerquery to query the data that I have already pulled into the report?

          I do have the book by Collie and I will take a look there as well.

          Thanks again for your help,

          • Thomas says:

            A calendar (or dates) table is critical for data models were you want to take advantage of Power Pivot’s built in time intelligence functions.

            Don’t worry about the “linked back” table for now, there is another way to get the data needed into the sample data.

      • Thomas says:

        Hello Mandy,

        There can be several ways to proceed, but if you get your data from a SQL Server (or have access to a SQL Server), version 2005 or later, there may be an easy way to proceed.

        • Mandy Bjork says:

          Hi Thomas,

          Here is what I am currently doing to query the data: MS Query, to excel. connecting to Intersystems Cache. It is not SQL server.

          I am trying to see if I can use powerquery instead. Any other suggestions? I am also signed up for the training on April 6th and 7th.

          • Thomas says:

            Hello Mandy,

            If Power Query can connect to Intersystems Cache, my vote would go to Power Query because it has an option to load directly into the data model and Power Query also has more tools to filter and clean up data before import.

            Also, I sent a test email to you, did you receive it?

      • Thomas says:


        I revisited the excel model and wanted to discuss how we can finish up. Let me know when you are ready.

  122. Mary Ann says:

    Finally, my company agreed to set some of us up with licenses for Power BI for Office 365 although we don’t yet have PowerPivot models completely built yet. However I did upload a “regular” Excel file with “regular” pivot tables and a slicer so that our VP Marketing could access the report via his Mac. I was so disappointed he couldn’t access it! He got error messages stating the file is too big. I thought Power BI was supposed to handle large files. (This was about 17 MB)

    Has anyone else had experience with Macs, Power BI, and large file size?
    Thank you.

    • Mathieu says:

      I upload 150Mb files on PowerBI. If you try to open them with Office365 it doesn’t work. You will need to download them. You need to go on the PowerBi site to open them.

    • Thomas says:

      Hello Mary Ann,

      I will get to a happy ending, but first let me have you take a look at the probable source of grief:

      Here is a link to an article that explains the issue::


      Paraphrasing a little, the author says:

      …in Office 365, you’re simply stuck with [a] 10 MB limit [however,] BI sites allows for data models as large as 250MB.

      In other words, that the data was not in the Power Pivot model, and the file size (separate from the model) exceeded 10 MB, the VP Marketing got an error message

      Now the good news:

      If you can design regular pivot tables to create the reports the VP needs, as the moderator for this FAQ page (and other site visitors who want to help out) I believe we can get your Power Pivot model sufficiently up and running in short order, making a happy marketing VP and a happy you.

      If you are interested in help with your model, I would not need real data, just a “real enough” structure of the tables and relationships you are working with. A few rows of sample data (which of course could be completely fictional) per table would expedite testing.

      I think we have worked together before (I think you have the Power Query add-on, which if true, means you have a professional plus version of Excel. Is it 2010 or 2013?)

      Let me know what you want to do; I clock in at 8 am.


  123. Mary Ann says:

    Thank you Thomas. Yes I have Power Query and Excel 2013. What’s the best way to send over some sample data?

    • Thomas says:

      Hello Mary Ann,

      If you replace the AT with an @, my email is tallanATprodigy.net.

      When I get your information, I will post again here.

  124. Mandy Bjork says:

    I was able to query my data from the powerpivot manage area into the excel report itself, but now I want to create a link back table. I am having difficulty getting it to work. I try to type in the code for the data field I want to link back and I get errors.

    • Thomas says:


      I think the best way for me to help on this one would be if you email (or use something like dropbox) me a copy of your workbook. If you replace the AT with a @ in the following address, your workbook will get to me:


      • Mandy Bjork says:

        Hi there, I am attemping to share my template with you, but I got a failed send. I sent it to tallen@prodigy.net, was sharing link via one drive

        • Thomas says:

          Hello Mandy, before the @, the last two letters should be “an” instead of “en”. Otherwise looks good enough to go through. Look forward to reviewing the template.

  125. Mandy says:

    I have a calculated column that has several row of data with 1, 2, and some Blanks. I am attempting to do a switch function to change the column to say Seen in 90 Days when it is a 1 and Not seen in 90 Days if it is a 2 or a blank. I have a syntax error, but I think I am close. Would appreciate feedback.

    =SWITCH([Criteria],1,”Seen in 90 Days”,2,”Not Seen in 90 Days”)=ISBLANK([Criteria],”Not Seen in 90 Days”))

    • Chris Gilbert says:

      =SWITCH([Criteria],1,”Seen in 90 Days”,2,”Not Seen in 90 Days”,”Not Seen in 90 Days”)

      where, the last “Not Seen in 90 Days” is used as a default for anything not a 1 or 2.

      You were missing some commas, and ended the SWITCH stt with the 1st closing parenthesis, but it still wouldn’t have worked because:
      a) I don’t think you can use a formula as a comparison value
      b) if using a “” for the 3rd comparison, you’d be trying to compare different types (1 & 2 are integers, but “” is a string.

      • Chris Gilbert says:

        Oh, I stand corrected. You can use a formula… see Rob’s March 31, 2015 post on http://www.powerpivotpro.com about the use of =SWITCH([TRUE],…)


        Very timely, Rob!

        • Chris Gilbert says:

          So, you’d be looking at:
          [Code]=1,”Seen in 90 Days”,
          [Code]=2,”Not Seen in 90 Days”,
          [Code]=BLANK(),”Not Seen in 90 Days”)

        • Chris Gilbert says:

          Or, the last line could be
          ISBLANK([Code]),”Not Seen in 90 Days”

          Sorry, I had used [Code] instead of [Criteria] in my test cases.

      • Thomas says:

        Hello Chris,

        Good to see you here!. Hopeftully you remember me, we run across each other over at powerpivotblog also.

        Best wishes,

    • Thomas says:


      This link will take you to a copy of the test data model that I started earlier in the week.

      It works on answering the question, were SvcID’s seen within 90 day periods (quarters). I am not finished testing, but I thought you might like to see what everything looks like.

      I’ll explain some key differences later this morning.


      • Mandy says:

        It looks great! I have also been expiramenting with PowerView, and Got PowerBI turned on now as well. With PowerBI I was hoping to find a 2015 public calendar to import as a table, and I was not able to find one. Did you manually create the calendar table?

        Here is the concept I am working with at the moment, during the initial development…

        Phase one:

        I query MS Query to a intersystems cache database with SQL code. Then, I return that data to excel as a table. I open a new excel workbook, open powerpivot, and make a connection between the excel table and the new powerpivot workbook. As the goal is to be able to click the refresh buttons to refresh the data.

        Phase two: Is to learn how to query the data with PowerQuery instead, and no longer have the extra step of creating a table in another excel spreadsheet.

        What I am wondering, after looking at the model you made, is how I would still be able to auto refresh my data, If I am creating tables and adding them to the model manually then linking them. Would I have one query that I link to in the powerpivot model, that I could update, then the other manually created tables would update as well, because they are linked? What steps did you go through to make the tables? Also, for my NO criteria, I will still need to show the type and program, but it can be a seperate pie and bar chart, so essentially it would show what you have done as a test, plus another section with the same format but % of not met…etc.

        Thank you so much for your help. I am really enjoying learning the functionality available through Excel and Power BI.

        • Thomas says:


          If you are interested in taking a look at Rev 02 of the model, here is the link:


          Externally not much has changed, but internally I replaced calculated columns with measures and hid ID and number columns from client tools to encourage report developers to use “equivalent” text columns (like hiding the integer column “Month #”, because equivalent text columns like “Month” and “Month Short Name” are more intuitive to work with when creating reports).

          To create the tables in the current model, I used SQL Server to import the data and modify the structure. Now I have a template for changing the shape of the data, and will see how far I can go with Power Query.

          If you will work on connecting Power Query to your cache database, I will work on using Power Query to create the tables.

          • Mandy says:

            Hi there,

            Looked at the model again, and the SVCID and TYPE, really would not be seperated, because in reality the SVCID is ClientID, and Type is their name… I had to mock up the data to send to you as an example. At this time, I am wondering how that would change the structure, given that there is a performance table

    • Thomas says:


      In the test data, should SvcIDs always have the same Type and Program, no matter the service date?

      Re: Calendars
      I have code that generates calendars. The following dropbox link for an Excel file contains a 10-year calendar (01/01/2014 to 12/31/2024. The calendar info is in a worksheet, so you should be able to use Power Query to import and filter into other Excel files, and it is also in the Power Pivot data model, so you could use this linked file as a template for other models.

      Let me know if this works for you,


      • Mandy says:

        Wow, Thomas, thank you so much… for the calendar table, and the other model that you have shown me. It is very helpful, for me to look at what others are doing with the data in the creation of the model structure.

        For the SVCID they will not always have the same type and program, there will be times when they are different.

        • Mandy says:

          Hey there Thomas, just wondering if you got the last message from me about the SVCID

        • Thomas says:

          Hello Mandy,

          The following link has information on anticipated changes in the data model.


          • Mandy Bjork says:

            Hi Thomas,

            Thank you for your help in this model. I received the information, but it took me a while to get back to you, due to additional projects I am working on. I have reviewed what you gave me, and I followed some of the same set up with the tables in the underlying model.
            Since we last talked, I was able to resolve part of my issue, by learning how to use power query to connect to the data base that I need the data from. I have also built the data in crystal reports, and use the SQl to paste into powerquery, a couple of times, I have modified the SQL code to Max dates, and group fields. At this time, I have a data model, pretty close to the data I was looking for, and all of the charts done. I still have some confusion on the table structure, and maybe part of that is due to the data that I am attempting to pull not being as normalized as I would like. For example, it is hard for me to determine what really is my data table and what is actually my lookup tables…so I made my best guess and have it set up as tall and narrow. Furthermore, I have used a bit of the “M” code to add custom columns in powerquery, using if statements. Part of the issues I was having was that I was not running 64bit version and I had too many calculated columns.

            You had a couple questions about the model I mocked up, and I wanted to still answer them: Service Date actually means the date of appointment, like a doctors appt. Program is the department. For example, a hospital has an ER, Ear Nose Throat, Cancer Unit etc… think of program as in the area the patient goes to for treatment.

            I have office 365 and loaded the data model (Excel Powerpivot not in Powerview yet) to a sharepoint site to share as view only access… we found a problem in testing it, where if a person clicks on the percent in the chart they can hover over the magnifying glass search button to drill down on the data, and what it does it completely change the chart, and there doesn’t seem to be a way to undo it. We thought it would drill down to underlying data, but it does not. Is there a way to make it drill down to the underlying data, and set the criteria on what field can be drilled down to? If not, can I restrict it even further, so that the magnifying glass is not available for view only…

  126. Deepak says:

    Hello Sir,
    Today I bought the book DAX FORMULA for POWERPIVOT. Now I am unable to download the sample file. please send me the updated link.

  127. Tripp Knightly says:

    Picklist-filtered import? I want to work with a table (on Teradata) containing 50MM+ records. In lieu of sucking the whole thing into Excel (will take a while!) I was hoping PowerPivot might have some smarts to let me constrain it by using a picklist already in Excel (say of 1000 key values).

    Not doable? Thx

    • Ron Barrett says:

      If I understand correctly, there’s a large dataset that you want to access via PowerPivot, create a data model, and expose only a portion to Excel. Is that right? If so, would a filtered pivot table with the aggregated information suit your needs?

      • Tripp Knightly says:

        Thx Ron — it’s not clear how what your propose avoids the enormous suck of data across the LAN. I don’t want to filter at the pivot table alone — I want to filter the all extraneous data from the very beginning.

        • Ron says:

          OIC. And the preview/filter on the data connection window won’t do the trick for you?

          • Tripp Knightly says:

            Source table is millions of rows. I want to bring in data only for let’s say 3,000 key values in my picklist. Am I supposed to deselect all and then manually select each value on the key column in the preview / filter step?

  128. Thomas says:

    Hello Tripp,

    If you have a version of Excel that supports Power Query, should be quite doable.

    With 50 million + plus rows, you would have to import that into Power Pivot (regular Excel is limited to 1,048,576 rows)

    And with 50 million + rows, you would definitely be interested in obtaining a free copy of Power Update, which would allow you to schedule a refresh at a time convenient to you.:


    Keep in mind what you have in those 50 million + rows and the constraints of your system.

    Good to hear from you! Let me know how things progress.

    • Tripp Knightly says:

      Thx. I was thinking Power Pivot might have some native smarts to constrain what it imports, though I’m seeing now where MS has drawn the line in terms of what does what in their BI suite, so I guess Power Query needs to be bolted onto my Excel as well if I want to pre-filter the import.

      • Thomas says:

        Hi Tripp,

        I definitely think this is a “can do” with Power Query, but let me ask a question or two up front to make sure my current plan is good (if not, we can come up with another plan).

        1) What version is your Excel (32 bit, 64 bit, Excel 2010, Excel 2013)?
        2) Have you been able to download and install Power Query?
        3) Am I right in thinking that your pick list is (or will be) a single column Excel table of about 1000 +/- rows?

        My current plan is to use Power Query to create a script to query the Terradata database.. If I were querying SQL Server, my script would create a temporary table which I would populate with the contents of the pick list. Then the script would continue with an inner join on the table with 50 MM rows, thus filtering the output to contain only matching records.

        Do you have permissions on the Terradata server to create a temporary table?

        • Tripp Knightly says:

          1) Excel 2010 (Enterprise Plus)
          2) Yes downloaded / installed successfully, haven’t gotten around to tinkering w/ it YET but it’s a working tab in Excel right next to PowerPivot.
          3) It’s about 3K records. The pick list is 2 columns since uniqueness on the database comes from 2 columns not one. I envision my use case going up to 10K records. The use case is being able to do quick ad hoc queries for specific records.

          I can create tables on the Teradata table. There’s often more than one way to do thing. I have a colleague who has done something along these lines using VB and ADO connection (no PowerQuery installed). In terms of where you’re going, it kind of sounds like “all” that PowerQuery would be doing here is giving me 1-2 less tools to have to use. PowerQuery becomes the thing that uploads the data (load tool avoided) and then runs the SQL (sql client avoided). In the end, I’m uploading data to Teradata doing the work there then hauling back the result set for my picklist.

          As a comparison, seems like if I was using Access, I could do a heterogeneous join. I could have the picklist in Access, then join to the Teradata table(s) and get the results in an Access query. The difference may just be semantic since I don’t really know what Access is doing behind the scenes in that scenario.

          Regards and thanks for giving it all some thought… I’d like to think my use case isn’t too uncommon.

          • Thomas says:

            Hello Tripp,

            Your assessment of what I was thinking and the benefits to you are exactly right.

            Sounds like you and your colleague have a way to work things out. If so, that approach may be best this time around because, when first using Power Query, and the query is advanced, Power Pivot may have a steep learning curve.

            Your use case is not uncommon and applies to queries for relational databases in general.

  129. Ron says:

    Manipulate a PowerPivot pivot table with VBA

    After reading the post, “PowerPivot VBA Macro Mania”, at http://www.powerpivotpro.com/2010/08/powerpivot-vba-macro-mania/, I was successful in adding a cube measure to a pivot table with VBA. The code is:

    Sub AddCubeField()
    ‘adds the variable to the pivot table values
    ‘If you want to turn off the pivot table update while adding the measure value
    ActiveSheet.PivotTables(“PivotTable1″).ManualUpdate = True
    Sheets(“Sheet1″).PivotTables(“PivotTable1″).AddDataField _
    ActiveSheet.PivotTables(“PivotTable1″) _
    Sheets(“Sheet1″).PivotTables(“PivotTable1″).AddDataField _
    ActiveSheet.PivotTables(“PivotTable1″) _

    ‘Turn the pivot table update back on
    ActiveSheet.PivotTables(“PivotTable1″).ManualUpdate = False

    End Sub

    I haven’t been successful in adding a row label to the pivot table. What was in the post didn’t seem to work for me. Kept getting the “”unable to get the pivotfields property of the pivot table class” error message.

    Any ideas what the proper VBA code is to add a row label?


    • Ron says:

      Found the solution to my question, which will hopefully help others.
      The above code, Sub AddCubeField(), adds measures (i.e. calculated fields) from a PowerPivot data model onto a related pivot table.
      The below code, Sub AddRowColumnItems, adds row and column labels to the pivot table:

      Sub AddRowColumnItems()
      With Sheets(“Sheet1″).PivotTables(“PivotTable1″).CubeFields(“[Appointments].[Member Name]”)
      .Orientation = xlRowField
      .Position = 1
      End With
      With Sheets(“Sheet1″).PivotTables(“PivotTable1″).CubeFields(“[Appointments].[Member Provider ID]”)
      .Orientation = xlRowField
      .Position = 2
      End With
      With Sheets(“Sheet1″).PivotTables(“PivotTable1″).CubeFields(“[Appointments].[MMMYY]”)
      .Orientation = xlColumnField
      .Position = 1
      End With
      End Sub

      The code is simple, but it me hours searching around to find and figure it out. Hopefully, that’s time others won’t have to.


  130. Thomas says:

    Hello Ron,

    Having these VBA steps to programmatically enter measures and labels into pivot tables will definitely same me time.

    An excellent addition to “PowerPivot VBA Macro Mania!” that you referenced above.

  131. Ron says:

    “Data could not be retrieved from the external data source. Error message returned by the external data source: The XML for Analysis request timed out before it was completed.”

    The above message is what I experience when attempting to refresh data for a PowerPivot pivot table. There pivot table has about 600 rows and includes some very date intensive measures accessing a PowerPivot data model. The model includes one table with 12 million rows, another with 2 million.

    My understanding from the message is Excel (2010) treats the PowerPivot data model as an external data source. The data connection is managed by a local installation of SQL Server. SQL Server is issuing the error message. The message occurs after exactly 60 minutes.

    Does anyone know if there is a way to change the time out setting for the local SQL Server?

    Thank you!!

    • Thomas says:


      If other users would be affected by a change in this setting, discuss first with the database administrator.

      Otherwise, if changing the setting will only affect your queries (SQL Server is local instance dedicated only to your needs), you could start with this:

      1.In Object Explorer, right-click a server and select Properties.

      2.Click the Connections node.

      3.Under Remote server connections, in the Remote query timeout box, type or select a value from 0 through 2,147,483,647 to set the maximum number seconds for SQL Server to wait before timing out.

      How long did original load require?

    • Thomas says:


      Since your data source is a SQL Server, can you pre-aggregate your data so the load into Power Pivot is smaller?

    • Thomas says:

      Hello Ron,

      Were you able to change the setting for SQL Server connection timeout? or more important, were you able to load your data?

      • Ron says:

        Hi Thomas,

        What I discovered during my efforts to fix the time out issue might be worth a blog post by Rob Collie. Some fascinating stuff.

        First, the time out issue. To connect with our server-based SQL Server database I have a desktop installation of SS, call it a client installation. PowerPivot uses this to pull the data down from the server and to query the data model from an Excel pivot table. There is a hard-coded 60 minute time out on a PowerPivot pivot table refresh. Which brings us to pivot tables.

        Turns out that a pivot table is simply a single data query that results in a range output, say four columns wide and 8 rows deep. The query itself is stored in the upper-left cell of the pivot table. Executing the single query refreshes the pivot table result. Now, that’s important. It’s a single query, which is subject to the 60 minute time out limit. A work-around is to use CUBE functions in replacement of a pivot table.

        A CUBE formula is a single query to the PowerPivot data model resulting in an array (CUBESET) or a single value (CUBEVALUE). Let’s say you have a cell range four columns wide and eight rows deep. Each cell has a CUBE formula. Refreshing data consists of 32 individual queries, rather than one. Each query is subject to the 60 minute time out limit, but because the result is a small data set or a single value the 60 minute limit doesn’t become an issue. Problem solved. Ahhh… but what if your CUBE formula references a slicer connected to a pivot table?

        Say you create a CUBE formula with a slicer filter where the user selects a year. The pivot table to which the slicer is connected times out after 60 minutes as explained above. But, you’re creating a CUBE formula. Refreshing the CUBE formula sends a query to the slicer to filter data. The slicer, because it’s connected to the pivot table, causes the pivot table query to execute, which causes another 60 minute time out. What to do?

        To avoid this situation I took two steps:
        1) Remove from the pivot table the measure columns that were causing the timeout.
        2) Create a CUBERANKEDMEMBER formula in a cell, say C1, that results with the slicer selection.
        3) Reproduce the removed pivot table measures with CUBE formulas that reference cell C1 as a filter.

        The results were impressive. Rather than timing out after 60 minutes the CUBE formulas returned the desired results in about 1 second!

        Now, I’m sure there are lots of mis-statements in what I’ve shared about how things work and there’s no doubt more efficient solutions. But, this is what I understood from the experience and what worked for me.

        I would LOVE to see a blog post from Rob or Avi Singh one this subject!!!



  132. Mary Ann says:

    I have a CSV file that I loaded into a Power Pivot model last month via Power Query. (I “saved and loaded” as a “connection only” and imported into the data model.) Now, I want to refresh the model with another month’s worth of data. What steps do I take? I assume I have to save the CSV file as the exact same name in the exact same place. Then, is there a “refresh” button in Power Query that will update the Power Pivot model?
    Mary Ann

  133. Thomas says:

    Mary Ann,

    Try this: Overwrite the CSV as described, then open Excel, go to the Power Pivot window and on the Home tab, click Refresh. When you return to Excel, you may also need to go to the Data tab on the ribbon and choose “Refresh All”.

    • Mary Ann says:

      Thank you Thomas, that worked fine.

      • Thomas says:

        Hello Mary Ann,

        You are welcome.

        Since last month, I have had a couple ideas how to further improve the report by automating the report headers and the calendar.

        If interested, I will send you a revised copy of the report and you can let me know if that will work for you.

  134. Mary Ann says:

    One big reason why we have turned to Power BI for Office 365 is to share Power Pivot models with our senior executives who all use Macs. While they’ve been able to successfully view the Power Pivot models and interact with the Slicers in Power BI, they have not been able to print the reports. Very frustrating. There doesn’t seem to be a way to resize the report so that all columns print. So we then tried to click on the tiny button located in the bottom right corner that says “View Full-Size Workbook.” The file then opens up in Excel Online. Then when we click on the printer icon, we see the report but the slicer becomes invisible. When printing, all columns do show up however the slicer is invisible.
    Have any Mac users figured out a way around this? Thought there might be a browser issue but it’s happening in both Safari and Firefox.

    Thank you.

  135. Ron says:

    Time-dimensioned Measures, Calculated Column, and Disconnected Slicer

    Creating a time-dimensioned measure is simple with DAX and functions like DATEADD(). It works especially well with pivot tables and slicers. Suppose the requirement is a rolling 13-month line chart of revenue based upon an end-month selected on a slicer? Pretty simple, write 13 measures each incrementally offset from the base measure by -1 month.

    Is there a better way to create relative time dimensions?

    Here’s what I’ve tried and ran into a dead-end:

    The idea was to create a disconnected date slicer the selection of which would affect a calculated column in the primary date table. Let’s call the primary date table, p_Date, and the disconnected date table d_Date.

    d_Date has a single measure: MaxEOMDate:= EOMONTH(MAX(Date),0)

    p_Date has a calculated column, RelativeMonth =

    The column values are either a text of the month of the primary table’s row date value or null, if the date is outside the 13-month period.

    The Transaction table’s transaction date has a relationship to p_Date[Date].

    Here’s the intended use:
    Create a pivot table. A slicer of d_Date’s month_year column provides the user with an end-month to select. The selection sets the MaxEOMDate value, which then identifies the RelativeMonth calculated column values. RelativeMonth provides values for the pivot table’s Column Labels. Whatever transaction measure would then be aggregated by RelativeMonth. Hola! Relative time-dimensioned measures using a disconnect slicer. Just one, small problem. It doesn’t work. :*(

    My best guess of why it doesn’t work lies in the difference between calculated columns and calculated measures. Calculated columns are calculated upon refresh of the table, whereas measures are calculated when used. Given that d_Date is a disconnected table when p_Date is refreshed the RelativeMonth says, “Okay, what is the maximum date in the d_Date table?” The result is then used for the column. What the column’s calculation doesn’t see, because the tables are disconnected, is the change in the maximum date when the user selects a month_year from the slicer. Ooooo, so close!

    So, that’s where the dead-end landed.

    Is there a better way to create dynamic relative time-dimensioned measures for a rolling 13-month period other than writing 13 measures?

    Rob (or others), what do you think?

    Cheers everyone and hope you have a memorable Memorial Day weekend!


  136. Thomas says:


    Most of the conversation is now in forums created by Rob and Avi:


    Why not copy your comments, register for the Power Pivot forum, and post your question there?

    Looking forward to seeing you in the Power Pivot forum,


  137. shweta K says:

    I have task level data for orders completed.It has a start date and end date.I need to calculate the lag time between the end of task 1 and start of task 2. I am having issues calculating because there are multiple parallely triggered tasks.Can you please help with a formula or technique to calculate the lag/wait time accurately

    • Thomas says:

      Hello shweta K,,

      Glad to see you have a good question for others to give feedback. Topics like these are now discussed in forums that you can reach at the following link: :


      Why not copy your comments, register for the Power Pivot forum, and post your question there?

      Looking forward to seeing you in the Power Pivot forum,


  138. kirillperian says:

    Hey, Rob and Avi!

    Thanks for all the content you’ve been putting out – it completely changed how I go about any type of research and data analysis both at work and at home!

    I have a suggestion! I don’t think you’ve covered this one before on the site and there is very limited information out there on the internet. It’s about the streaks! They don’t even have to be sports related (attendance streaks at work, can be used, for instance), but for us sports aficionados, winning and losing streaks is what it’s all about. :)

    Anyhow, that’s all I was able to find that is close to the topic, but this is all in Excel stuff. I was not able to find anything DAX-related or translate the below examples into DAX with any success.

    Maybe it can even be done as an extension of the post that Avi’s helped me with?

    Let me know what you think.

  139. Thomas says:

    Hello kirilperian,

    There are new forums (will soon replace this page) for this website to discuss topics of interest to you::


    Why not copy your comments, register for the Power Pivot forum using the link above, and post your question there?

    Looking forward to seeing you in the Power Pivot forum,


  140. Louise Bown says:

    I am really struggling with getting a calculation based on a start and end date. I have rows that have a start and end date and a daily rate e.g. start date 10/10/2016, end date 12/12/2016 @ £100 (End date does not count towards revenue)
    I want to be able to sum and slice the relevant amount for any particular day, month, quarter etc. For example
    10/10/2016 = £100
    October = 22 days = £2,200
    November = 30 days = £3,000
    Dec = 11 days = £1,100

    Is this possible?



  141. Benjamin Chee says:

    Hi Rob!
    Do you know why PowerPivot does not exist for the Mac Excel? Or will it be published soon?

Leave a Comment or Question