Ask your questions here

When you have general PowerPivot question you can ask them here. I’ll try and help you asap :).

I have started archiving old questions, everything before 2014 can be found here:

219 Responses to Ask your questions here


    Hi Kasper,

    No sales no budget. I’m using the below formula to calculate budgeted values, however when there are no sales no budget is calculated neither (as you can imagine from the DAX). How can I always get budget figures even with no sales?

    SUM( Act1213Bud[Act2012] );
    FILTER( ALL( Act1213Bud[YearMonth]);
    CONTAINS( VALUES( DatesTable); DatesTable[YearMonth]; Act1213Bud[YearMonth] ) );

    FILTER( ALL( Act1213Bud[Country]);
    CONTAINS( VALUES(Sales[Country]); Sales[Country]; Act1213Bud[Country]) ))

    Thank you for your help!

    • Hi Mikko,

      I am not sure I understand what you are trying to do here? does Act1213Bud[Act2012] contain the budget value? And why cant you use the relationships between the tables? What does your schema look like?

      PS your formula as is can be simplified using:
      SUM ( Act1213Bud[Act2012] );
      ALLNOBLANKROW (DatesTable[YearMonth]);
      ALLNOBLANKROW (Sales[Country]);


    Dear Kasper,

    Thank you for your answer! The schema is very simple with the date table, budget/actual2012 table which contain budget for 2013 and actual result from 2012 set for the first day of month and finally real sales from DB from Sales table. The tables are linked with the date table the usual way. I can compare real sales towards budget and actual but if some country is missing sales for the month then budget and actual values are also missing.


    PS the simplified formula produces cartesian product for 2012 actual result.

    • Hi Mikko do all these tables have relationships? Also would it be possible to send me a simplified workbook? Just using Excel tables but the same schema and that shows the issue. I’ll answer by doing a blog post on it..


    Dear Kasper,

    I have now made the simplified example. Can you kindly email me address to send it to you.

    • Hi Mikko,

      please use kadejoatmicrosoftdotcom



    Hello Kasper,
    I’d like to get your opinion on whether the set up I have is most efficient or if there are any other recommendations.

    I currently have 3 tables.:

    1st table is named “stock” and has 1Million rows
    2nd table is named “optimization” and has 400K rows
    3rd table is named “fill rate” and has 100K rows

    The “stock” table is downloaded via sql from our MRP system
    The “optimization” table is also downloaded via sql from our MRP system
    The “fill rate” table is downloaded from SAP via business warehouse.

    Once all three are downloaded, I import all three via text files into Microsoft Access.
    I then create a query to join the three tables and bring in the necessary columns and I also create calculated columns .I run the query and make it a new table in Access and I name the table ‘analysis’ in Access

    After those steps I connect powerpivot to the table in Access named ‘anaylsis’ and begin to perform my analysis in Powerpivot.
    Is this the most efficient method to use? If I were to use SQL Server Express instead of MS Access would it make this process easier and quicker to run? What are your thoughts?

    • Hi,

      What is the reason that you use Access in this scenario?

      I would try to see if you can import as much as possible directly into PowerPivot through the SQL connection or text files and keep the 3 tables as they are, create the relationship in PowerPivot and skip access.

      You also might want to investigate Power Query as intermediate step to do some light weight ETL.

      Hope that helps.


    Hello Kasper, Thank you for your reply. I utlize Access to create the joins between the data as I am unable create this relationship without modifications in powerpivot alone. I also create calculations in access as well prior to uploading into powerpivot. I guess I could create another column in the power pivot sheet and concatenate two columns to form the join and relationship that way. I will also give power query a try as well. thanks


    Hello Kaspter,

    Im using a moving average formula and for some reason whenever i show the dates as a row or column i have a blank space with a balance.

    The ending balance formula is the following:

    The moving avg formula is the following:

    Any help would be appreciate.



    Hi Kasper,

    First all of thanks for your efforts in this blog! Do you know how to calculate discounts with DAX. Lets say you have a table (representing a discount schema for individual customer of group). For example you sale bicicles, you give a 10 dollar discounts on all bicycles, 25 dollars when is a trycicle, 45 dollars to italian bycles and for specifics model you give different rebates. You can see the structure (bicicles, bicycle type, country of origin, product number.
    How to create a formula that recognizes these levels? how to deal with overlapping contracts, etc… really have no clue….
    Any tips??


    Hi Kasper,
    Can you give me a simple calculation for removing the phone number from a customer name field (e.g. Phil Brown (561) 398-1234)? I managed doing it by using three calculated columns but I’m sure there is an easier way to do it.


    Hi Kasper,
    I saw your blog post on Sparklines and how you have used Slicers on them. Is there a way you can apply the same slicer on a Chart as well as a Sparkline which would be based on the same PivotTable? Do you have a sample like that?


    Hi Kasper,
    I originally created a PowerPivot in Excel 2010. I eventually upgraded to Excel 2013 because I was attempting to pull millions of lines of data and Excel 2010 couldn’t handle the volume. However, my company uses SharePoint 2010 / SQL2008R2 so I’m unable to schedule a manual refresh or use the slivers. I (now!) know there are some incompatibility between Excel 2013 and our current versions of Sharepoint and SQL. Do you have any thoughts in regard to how to work around this? Again, because Excel 2010 can’t handle the volume we need it to, rebuilding the PowerPivot in 2010 isn’t really a solution (and that is the only recommendation from our Sharepoint team). Any suggestions are MUCH appreciated!
    Best Regards,


    Hi Kasper,

    I am having a similar issue to Jess. My newly formed team has to deal with over 4 million lines of data. It seems like 64 bit version of Excel will be mandatory for us, correct?

    My question is – if my team has 64 bit, will the readers of our reports ALSO need 64 bit or can they read our reports/analysis with 32 bit?


  12. Hi Jess and Ellia, there is no difference between Excel 2010 and Excel 2013 on number of rows. I would recommend installing the 64 bits version of Excel, that will allow more data to be loaded in memory on both 2013 and 2010.

    Its not necessary that you need to install the 64 bits version, it really depends on the data and if it can be compressed enough to fit in memory. There are some tricks see this help article:

    The readers of the report only need 64 bits if you want to share the report with them directly. If you use sharepoint they dont need Excel only a web browser.

    Hope that helps


    Hi Kasper,
    Thanks for your quick response and suggestions. It sounds like Excel 2010 64 bit is the way to go for us. Unfortunately, we had a local consultant come in and tell us we absolutely needed Excel 2013 to handle all the data which is why we upgraded – I should have come here first!
    Thanks again,


    Do you ever connect powerpivot to SQL express? If so do you have any problems establishing the connection? The server name doesn’t appear in the dropbox in powerpivot.


    • Hi, I would expect this to work. What happens if you just type in the name of the database and continue?



    Hi Kasper,
    I’m using powerpivot to review sales and website traffic information. I connect to my data source using a flat file (.txt). After setting up the pivot table, the sales information sums individual product sales. Each of the individual products have a 10-digit alphanumeric identifier.
    I noticed when I sum using powerpivot that the total is different than the total when using a normal pivot table in excel. After performing an audit it appears that powerpivot is omitting rows of data when summing. Powerpivot recognizes the row for the product identifier, but the sales data numerical value is blank, thus leading to the discrepancy in the total amount.
    Any ideas on what I’m doing wrong?

    • Hi Sean,

      Are there relationships between the tables? Are you sure that the values on both side are the same?

      Is the column that you use the aggregate from the same type? can you see the values that are omitted in the sales table?




    I am new to Power Pivot. I am trying to create variance measure for Budget vs actual and YTD calculations. Is there simple formulas I can follow for both?




    I am looking to create a DAX expression which provides a countdistinct on a specific variable where some other variable matches something else across a full table.

    Can anyone help?!

    • Hi Matthew,
      Maybe something like this:
      Calcualte(distinctCount(table[col]), Table[color]= “white”)

      Would that work?



    Thanks for your note. Want I envisaged doing was creating this count for each category without defining the category in advance. So let’s say the categories are:


    I wanted a way to get a count distinct for each category.


    I have another question. I am looking to create a box chart and so I need min, max, quartile 1, quartile 3. Is there a way to calculate all of these across a table of data. Again, I want it calculated in way that I can make the section on a filter on a pivot.

    Let’s say we are calculating salary data. I want to create the expression in the data structure and then make the selection in the pivot.


    OK I now understand what I need to do here. Simply =distinctcount across the column and add as a measure NOT a calculated field.

    My question on how how to create quartile 2 and 3 values as a measure is the tough one for me!


    Hi Kasper –

    All the references to working with time I have seen are actually working with dates.

    I am analysing a number of daily processes. I have log data in PowerPivot which lists for each day, what time of day each process completed. I want to chart the time each peocess completed (Y-axis) against the date (X-Axis) but PowerPivot does not seem to be able to recognize the time as a value, to average the time (say if I summarise at a monthly level).

    The only way I have found around this is to convert the time to the an integer containing number of minutes since midnight. This works bot the values on the Y-axis (time) are meaningless to most people.

    Is there a way to work with time-of-day as a value?



    I’m looking to find a way to easily create multiple horizontal bar charts.

    Let say I have some categories running on the X axis. Let’s say they are IT, Sales, Marketing and I have a measure in the columns, number of jobs, for Q1 – Q4. I want to show a horizontal bar for each category for each quarter.

    I could do this with conditional formatting but was hoping for something which looks better!


    SUBJECT: Max number of measures allowed on measures grid.

    Hi Kasper,

    Do you know if there is a maximum number of measures that can be entered on a single measures grid?

    I have an Excel 2013 workbook with about 300 measures, 130 of which are on the same measure grid. When I tried entering the 131st measure, I received the message “Sorry, Powerpivot can’t save these changes back to the Data Model.”

    After a bunch of trial and error, I found that I could only enter my additional measures by putting them in a different measures grid so as to keep the maximum number of measures on any one grid under 130.

    I couldn’t find any references to this issue on the web, so am wondering if this is normal, or if there might be something specific to my workbook going on.



    • Hi Tak,

      I am not aware of a limitation like that but it sounds like a bug, can you file a bug at that will allow us to take a look at the issue.



    Hope this finds you doing well. My question – I have data that I’m gathering by week and filtered at the year level. The rows are month, then week. The columns are current week, MTD and YTD measures. My issue is with the YTD data. I want it to stop accumulating with the last MTD, instead it repeats all the way down to the end of the year. Anyway to get the TotalYTD function to stop at the last totalMTD entry that is >0?


    Thanks Kasper. Subsequent to my prior post regarding “Max number of measures allowed on measures grid”, I tried to replicate the issue on a fresh workbook and did not run into the same limitation, so it appears specific to the nuances of a specific workbook. I’m guessing the apparent workbook-specific nature of this issue makes it inappropriate to reporting at but let me know if I should report it anyway and I will do so.




    Hi Kasper,
    I haven’t set up any relationships between tables. The sales information is numeric and formatted as a Decimal Number.
    The column for the aggregate total is the same type. I can see the values that are omitted in the sales table (please see attached examples.).
    I’ve adjusted the data type and format in the powerpivot menu but the discrepancy remains the same. I have been able to correct the values when I change the import file from .txt to .csv. However, I’d rather avoid this extra manual step and I cannot publish my SQL queries in this format. I’m trying to automate the reporting as much as possible.
    Thanks for your help!

    • HI Sean, so this means that even when you do a simple aggregation for the entire sales table you get “wrong” results? Any chance you can send me the workbook?


    Hi Kasper,

    I have dataset with around 80 products and I am running top x products by revenue.
    I d like to add x+1 line in the report showing all the other products which are ranked below top x, aggregate them into one line – “other products”.

    top 5 products table
    other products total
    grand total

    Thank you for your help!



    Hi Kasper,

    I have to loop through the dataset having columns Batch, CustomerNo, Date ( have single date column) and calculate difference between dates within CustomerNo and Batch combination, then eliminate rows where difference is less than 10 days and keep doing that until all rows with difference less than 10 days are eliminated with in the group. It can be done in SQL using CTE but not sure if this can be achieved in powepivot? Any inputs are appreciated.

    • Hi, I see potentially two options:
      1 use a calculated column to loop through the table
      2 use PowerQuery and M to go through the table



    Hi Kasper,

    We’re searching for a hoster of Sharepoint PowerPivot Services on the Web. Do you use/know or could you recommend such hosters?
    Does Microsoft directly provide such services (for example through Office365 Sharepoint plans), too?

    Thanks a lot for any hint.

    • Hi Thom, we microsoft actualy now has PowerBI where you can share PowerPivot workbooks. Check out



    I have a 2.2 million row ShippingData.csv file that I can successfully load into the DataModel via the [OtherSources | TextFile] option from the PowerPivot 2013 Ribbon.

    As a trial, I tried loading that same file into a new 2013 DataModel using PowerQuery, but it failed giving me an “Out of Memory” error!
    I even tried eliminating some fields but the import continues to fail.
    Do you have any ideas what might be causing that to happen?



    Is it possible to be able to use the slicer for inclusion criteria of AND instead of OR. So basically, if I have a people and list of states they have been to. When I do a slicer, I want, if I select Ohio and Maine, I want to display only the people that have been to BOTH Ohio AND Maine…


    Hi Kasper,
    In my model, I have two memory-expensive SUMX functions (call them #A & #B). My 32-bit Powerpivot could not calculate the measure ‘[#A + #B]’, however, it could calculate both measures (at the same time) when separated into two measures ‘[#A]’ & ‘[#B]’.
    Does it always require less memory to split a measure into two or more of its components?
    (…I previously assumed the opposite to be true)
    Thank you

    • I think its safe to say that splitting measures up doesn’t always result in lower memory usage. It really depends on the calculations. Can you specify your formula’s?



    Hi Kasper,

    Thank you for your recent post on RANKX(). I have used the same setup (except deal with the blanks a bit differently) in my company’s calculations. I would like to use content from your blog post to teach others in my organization and just wanted to see if I could get your permission to copy some of the info from your post into an internal wiki. Please let me know if this would be ok.

    Thank you,

    • Hi Andrew,

      Sure go ahead!



    I have 10 millions of jobs I have aggregated. Each job has a job title. This can be broken down into component words. I then want to run an automatic frequency analysis for each word across each month for 2013. I need a set of DAX expressions that allows me to both split the job title field into component parts and then produce a cross tab showing a count of each word across the full universe of jobs for each month.


    • Hi Matthew,

      Just to make sure I understand. You have a predefined table with words (not auto generated based on the jobs). Then you want to see how often those words are being used in the jobs table?



    Have you thought of doing this text-string/word frequency analysis using another tool? (Don’t get me wrong, I’m a huge fan of PowerPivot.)

    I’ve seen this done in some example programs for both “RapidMiner” and in the “R” language tutorials. Try doing a web search on those.



    I’m new to Power Pivot tool,I have 2 tables information one is Master table and Another is Transaction table both the tables are related to CustomerID Column.In Transaction table some of ID’s are repeating but InvDate Column is Different.When I drag the Sales Amount column into report it is showing Sum of all CustomerID matched records,my requirement is need to pick max Invdate value from table where there is a same Customer ID,I written DAX formula for same but it is not Picking max InvDate.

    DAX Formula:

    =CALCULATE(SUMX(‘IR’,’IR'[SalesAmount]), FILTER(‘IR’,SEARCH(“*Manage*”,’IR'[Name],1,0)>0),Filter(‘IR’,’Sales'[Customer ID]=’IR'[CustomerID]&& ‘IR'[InvDate]=Max(‘IR'[InvDate])))

    Another approach that I followed is create one calculated column for Max(‘IR'[InvDate])
    that calculated I use in above query but there is no luck.

    In Above Query IR and Sales are two tables.



    Dear All,
    I have a requirement in Power Pivot for excel 2013.I have one master table Employee and Transaction table sales, in My transaction table I have only employee Name I can not give relation between Employee Master and Sales table because of employee names are not unique in Employee Master.I written DAX query to get EmployeeID in Transaction table.
    DAX query I shared in below:

    =CALCULATE(VALUES(‘Employee'[Emp ID]),FILTER(‘Employee’,’Employee'[Name]=Sales[Ename] && ‘Employee'[End date] Emp(Eid),Emp(Eid)


    I am working with a tabular model in SSAS 2012 that supports several fact tables. We’re trying to build perspectives into the model to support a couple different viewpoints. When we browse the model from Excel we see all of the facts grouped by the table they came from, but when we try to browse a perspective the facts from all of the different tables are lumped together. Browsing the perspective from Management Studio maintains the original table structure. Is this a known issue with Excel or just a feature we’ll have to live with?


    Hi Kasper

    GREAT resource.

    Here’s an easy one, how can I determine the number of rows in a pivot table without using VBA? Is there an excel formula I can use that can do it simply?

    My issue is I have a pivot that varies based on a category selected. E.g. category A might return 100 rows and 10 columns and category B might return 90 rows and 7 columns.

    =rows() obviously doesn’t take a pivot table as a reference so it’s not dynamic.

    Thanks in advance

    • Hi Nicholas, there is no good way to do this in PowerPivot. You could however do it in Power Query.


    If there is a column of your spreadsheet that is unique (no other formulas or values in the column) to your Pivot Table (Let’s use column B for example), then you could simply use the =COUNT(B:B) or COUNTA(B:B) to get the total number of rows.

    You may want to make adjustments for headers and grand totals, such as =COUNT(B:B)-n .

    Dealing with SubTotals is a whole other issue!

    • Thx Chris for helping out!


    I have a measure “Count Accidents” =DISTINCTCOUNT([Longitude])and I now want to create a Countif measure to count values >=-1.08 and =-1.08,[Longitude]<-2.8), I cannot get it to work, help please as I have failed to find a Google solution.


    Sorry for some reason the measure I pasted into my question did not appear in my message so the measurement I have tried is =CALCULATE([COUNT ACCIDENTS],[Longitude]1.08)

    • Hi Andy,

      An expression like this should work: =CALCULATE([COUNT ACCIDENTS],Table1[Longitude] > 1.08).
      What is the error message you are getting?



    Hi Kasper,
    I am trying to find a way in DAX to creat somthing like NEXTNONBLANK. I need to calculate WON/LOST Value due to the price change, and for most of the customers first price, would be “START PACK” wchich is 50% cheaper, I can’t take simply next month sales because, there is no rule, with buying frequency over the year.Other thing that might resolved this issue would be a function that states when the price was changed, and I have no idea how to write it.


    Try a Measure formula like this:
    Filtered Accidents:=CALCULATE([Count Accidents],Filter(Table1,[Longitude]>=-1.08 && [Longitude] = -1.08 AND < -2.08 would never result a value being returned. Hoping this was either a typo, or you meant OR, not AND. In which case change the && to ||



    Sorry, something went crazy in the previous post…

    Try a Measure formula like this:
    Filtered Accidents:=CALCULATE([Count Accidents],Filter(Table1,[Longitude]>=-1.08 && [Longitude] = -1.08))

    The way you described your filter criteria (> -1.08 AND < -2.08) would never result a value being returned. Hoping this was either a typo, or that you meant OR, not AND. In which case change the && to || in the FILTER portion.


    Hello Kasper,
    I put company related measures on PowerView and I want to add top 5 companies from same industry where is company. Do you have some sugestions how to do that?


    I need to calculate the % of total at aggregated level. I have a powerpivot model that have the following fields; SKU,brand,sales,cost,qty,Grossmargin by SKU.
    I created a pivot table that summarizes the data by brand so it has Brand,sale,cost,qty , GrossMargin (use a dax formula for Gross margin). I need a dax formula that calculate the % of the total by brand.


    Are you familiar with this error? “Errors in the high-level relational engine. The following exception occurred while the managed IDataReader interface was being used”


    I need to calculate the average order size of all orders that contain at least one sku that is category device. If I filter by device, I can’t include the value of the other items. I am thinking i need to use a ALL calculation, but not sure how.


    Hi Kasper,
    A recent organization change at my company now has me working very closely with SAP (which I currently have no experience in). I have experience with PowerPivot but none with PowerQuery. What is the easiest way to get SAP data into PowerPivot? Or, which skills should I be learning or books should I be reading to hit the ground running with PowerPivot and SAP.
    Thank you


    Hi Kasper. I never received a response for my question a few months back. Can you give me a simple calculation for removing the phone number from a customer name field (e.g. Phil Brown (561) 398-1234)? I managed doing it by using three calculated columns but I’m sure there is an easier way to do it.

    • hi Liz,
      Rica’s example does the trick I would say.


    Hi Liz, try this in calculated column “left([CustomerName],FIND(“(“,[CustomerName],1)-1)” but if you will have “(” in customer name it will remove everything after ( ,for ex: “Liz(Liz” – above formula will return “Liz” and remove “(Liz”


    I was wondering how people apply links to BISM models on SP so when the user creates a PowerView report from a link the default save directory is not the data connection folder.

    • Hi Philip,

      Do you mean when you create a PV report in Excel?



    Here’s the goal: Create a DAX measure to count the number of dates where the sum of category “C” transaction amounts per date is greater than $200.

    Date Category Amount
    5/6/2014 A 122.00
    5/6/2014 C 87.00
    5/8/2014 A 110.00
    5/8/2014 C 50.00
    5/8/2014 C 50.00
    5/8/2014 C 120.00
    5/9/2014 A 280.00

    The DAX measure would return the value, 1, because only 5/8/2014 has a daily total exceeding $200 for category “C”.

    Seems like I must be missing something obvious in creating the measure, but it has escaped me.

    Any suggestions?


    Try these measures:
    CSales:= CALCULATE([TotalAmount], FILTER(Table1,[Category]=”C”))
    nDates of C gt 200:=CALCULATE(Distinctcount([Date]),FILTER(ALL(Calendar),[C Sales]>200))

    Chris Gilbert


    Alternatively (and probably better for large transactions table), try:

    AltMeasure CSales over 200:=Calculate(DISTINCTCOUNT(Calendar[Date]),FILTER(ALL(Calendar),[C Sales]>200))


    Hi Kasper,

    Here’s one for you that might be interesting and that I haven’t seen addressed elsewhere on the web. I’ve always worked around this issue by using VBA, but it would be nice to handle natively in Power Pivot instead.

    Let’s say we have a dates table and fact table linked by a field called DatesID. The dates table has one field called DatesID and the fact table has two fields (DatesID and Quantity) as follows:

    Date, Quantity
    5/1/14, 1
    5/2/14, 2
    5/3/14, 3

    We have a measure for CurrentQuantity:=SUM(fact[Quantity]). When we select 5/3/14 on a slicer for dates[DateID], we get 3 from Pivot1. Everything is fine at this point.

    Where I run into trouble is with Pivot2 where I want to list all dates within the last 30 days of the slicer date along with the corresponding quantity.

    So for example: By selecting 5/3/14 on the date slicer, we want to produce the following on Pivot2:

    Date, Quantity
    5/1/14, 1
    5/2/14, 2
    5/3/14, 3

    Similarly, when we select 5/2/14 on the dates slicer, we would get 2 from our CurrentQuantity measure and 5/3/14 would drop from pivot2.

    Is this possible without using VBA to filter the date range for pivot2?

    Getting to a pure Power Pivot solution for this would be AWESOME!

    Many thanks for all you do for the Power Pivot user community, whether or not you can help with this one ;)



    • Hi Tak,

      this should be pretty straightforward. I’ll do a blog post over the weekend on how to solve it.


    I just got the book and am anxious to work through it. Where can I get the datasets that were used for the examples in the book?


    That would be wonderful…thanks Kasper! Regards, Tak


    Is there a way to get a max of a measure?

    For instance, I am summing up a distinct count of stores selling for a certain time period. Then I need the max from those groups to use as a filter.

    So for instance, Period 1 distinct stores is 80, Period 2 is 95, Period 3 is 60. I need to be able to use the max of each group.

    So my end result would be a formula to sum sales for those periods where the distinct store count is somewhere near the max (like within 10 stores.) So if Period 10, for instance, only had 35 distinct stores, it’s sales dollars would not be part of the sum.

    Can’t seem to get anywhere with it. Thanks, looking forward to the ebook version of Dboard & Reporting.


    Hi Kasper,
    I love Rob Collie’s book “DAX Formulas for PowerPivot: A Simple Guide to the Excel Revolution” and I have pre-ordered your book and am very much looking forward to receiving it.
    When searching for PowerPivot books, I see two by Rob Collie with slightly different titles, the other being “DAX Formulas for PowerPivot: The Excel Pro’s Guide to Mastering DAX”. Are these totally different books or just alternate titles? And if they are different, where can I purchase the latter – Amazon is out of stock.
    Thank you

    • I think these are the same with a different title.


    Hi Kasper,

    I loaded data into my Power Pivot datamodel using Stored Procdures. At this point I need an additional column in my datamodel. I have added it to my stored procedure, but Power Pivot doesn’t seem to pick up on it when reload the model. When I add this as a new table than it does pick up on that. Is this expected behavior from Power Pivot?

    Kind regards,



    Never mind. I already found the solution. A simple refresh was not enough. I had to go to table properties and press “save”.

    Kind regards,


    Is there a way to parameterise a power pivot such that the inputs for the query used to load the data model are taken from a worksheet?

    I need to build a dashboard but only import the most relevant data each time.


    Dear Kasper, please help on a measure to solve this problem. I have this table:

    Per No Amount Date
    123 230 5/31/2014
    123 300 4/30/2014
    145 125 4/30/2014
    120 250 5/31/2014
    136 300 4/30/2014
    120 350 5/31/2014
    136 150 4/30/2014
    120 100 5/31/2014

    As you can see from the table as loaded in my model, there three columns. The personnel number the amount paid and the date (period), in this case two. I have written a measure that calculates difference between what each per no was paid in the last period (4/30/2014) and the current period (5/31/2014) but where I run into problem is how to determine the following measures:

    1. The total number of staff (per no) with lesser pay in the current period (5/31/2014) as against last period with the amount of the difference in the pay.

    2. The total number of staff (per no) with more pay in the current period (5/31/2014) as against last period with the amount by which the difference in the pay.

    3. The total number of staff (per no) with no pay in the current period (5/31/2014) as against last period with the amount of the difference in the pay.

    4. The total number of staff (per no) with no pay in the past period (4/30/2014) as against the current period with the amount of the difference in the pay.

    Thank you for your help in advance.


    Dear Kasper,

    Please pardon my error in the above, this is the accurate table to use. Really sorry.

    PerNo Amount Date
    123 230 5/31/2014
    123 300 4/30/2014
    145 125 4/30/2014
    120 450 5/31/2014
    120 300 4/30/2014
    136 350 5/31/2014


    You may also want to consider using CUBE functions for constructing a dashboard based upon cell values.


    Hi Kasper

    I need some help in calculating a DAX measure with time intelligence. Basically I have
    -> Fact Table (Which contains Sales date, Order Date, Customer Join date)
    -> A Join Date Table (Essentially the customer join date table which holds dates and I have linked this table to fact table)
    -> Order Date Table ( A separate table with dates that is linked to Fact table)

    I’m trying to create a measure to find out the Sum of Sales for 3 months since a new customer joins.
    I have used the following DAX but I’m unable to get the right result
    – “calculate([Total Revenue],dateadd(‘Join date Calender'[Join Date],3,Month))”

    Appreciate any help to achieve the above.




    Dear Mr. Jonge,
    I see that the publish date for this book is July 14, 2014. But it is already available for sale in a paperback edition at in the United States.

    But there is NO information about the ‘Table of Contents’, OR the ‘Look Inside’ feature of

    I am very interested to get a preview and look at the table of contents for this book. Would it be possible to make that available?


    • I’ll ask the publisher what he thinks.


    I apologize Mr. Jonge,
    I forgot to mention the name of the book in the previous post/email:

    “Dashboarding and Reporting with Power Pivot and Excel: How to Design and Create a Financial Dashboard with PowerPivot”


    Hello Kasper,
    Not sure if this is the best forum for this issue but the PowerPivot Field List for my instance of PP on Excel 2013 (64 bit) is reverting to the classic Pivot Table Field List, even for new PP data models and worksheets. Wondering if this is a known issue related to the latest Office 2013 patch and/or there is a workaround?
    Many thanks,

    • Hi Ben,

      This is by design in Excel 2013. You can do slicers by using the right click menu, there are no slicers wells any more. You can rearrange slicers by using the Power Pivot tab. Excel owns the field list and we have given them this feedback.

      Hope that helps,


    Hello Kasper,
    How can I subscribe for your blog? I do not see any option to subscribe for your posts..May be it’s not appearing to me as I’m accessing your site through mobile..


    • Yes there is a subscribe option on the right. I just added email after your request :).


    Hi Kasper,

    In my company when we are trying to Sign In from Power Query tab in excel using our company email id we get error, 401 Unauthorized : Access is denied due to invalid credentials. Have you ever got such error? Any idea what could be the reason.


    • try clearing the cache of your browser and try again. Hope that helps.



    I have just discovered Power Pivot. Wow! I think this is going to solve a lot of my issues with a dashboard I have created in 2007 (too slow, too cumbersome to maintain etc).

    I am trying to replicate my existing dashboard using Power Pivot but cannot calculate financial variance.

    My data set is:
    cost code, account #, date (month), Type (actual or budget), dollars.

    A relationship sets the account as either income or expense.

    I have calculated variance using measure 1

    When I display my pivot table, I get 4 columns:
    1. date, expense and income as rows
    2. actual (dollars and measure 1), budget (dollars and measure 1). In both cases measure 1 = dollars ie – this is not variance. If I include row totals, the total sum of dollars’ and ‘total sum of measure 1′ equal each other and are the correct variance figure.

    My pivot table in Excel 2007 shows actual, budget and variance ie- 3 columns only.

    What have I done wrong?



    Hi Kasper

    I have been using powerpivot for about 2 years now and have a question about query designer.

    to modify a query that connects to an analysis cube, when i go to Table Properties > Design, it worked fine.
    But recently, I changed the query text and cannot go back to the Design anymore.
    Looked around on the web, but couldnt find a solution, if there is any.

    would you be aware of any trick/workaround to get the query design back?

    I am using powerpivot 64 bit with office 2013 64 bit.

    Thanks in advance for your consideration

    – Asit


    Power Query –

    I need to get a count where DefectStatus exists (“New”, “open”, or “reopen”) without affecting the other types of Defect Status-

    I just want to group all new/open/reopen


    Platform = 123 ABC Status “Open”
    Platform = 123 ABC Status “ReOpen”
    Platform = 123 ABC Status “New”

    then Platform = “123 ABC Status” then count = 1

    What is the proper syntax or can you directed me to a how to create this?




    You’ll want to write a condition using the || (logical OR) symbol, as in:

    (DefectStatus = “Open” || DefectStatus = “ReOpen” || DefectStatus = “New”)

    Usually as the second argument to a Measure := CALCULATE(expr, filter_expr) statement

    Hope that helps.


    Thank you for your quick response.

    I am relevantly new to power query – where do I create the condition?

    and what would be the correct syntax within Power Query?


    Here is my current PivotData Setup:

    Source = Table.Combine({TPR,LTE}),
    InsertedCustom = Table.AddColumn(Source, “Duration”, each [Closed On]-[Detected On]),
    ChangedType = Table.TransformColumnTypes(InsertedCustom,{{“Duration”, type number}}),
    FilteredRows = Table.SelectRows(ChangedType, each true)

    I am assuming I will need to modify the insertedCustom =…. What would be the correct syntax?




    Disregard last comment – posted by mistake – How do I modify this:

    Source = Table.Combine({TPR,LTE}),
    InsertedCustom = Table.AddColumn(Source, “Duration”, each [Closed On]-[Detected On]),
    ChangedType = Table.TransformColumnTypes(InsertedCustom,{{“Duration”, type number}}),
    FilteredRows = Table.SelectRows(ChangedType, each true)

    to modify the Defect Count to only count the Open, New, or reopen as just 1 when any of them = 1


    Hi, I just downloaded the file from the url specified( and follow your book “How to Design and Create a Financial Dashboard with PowerPivot – End to End” page 23 steps to collect the data. I found the pivot table data for the sum of RevenueAmount of $57928870.16 as specified in the book is different from what was retrieved form the access data which is $58057881.68. I don’t know if I have downloaded the correct version of the data file or not. Please advise.


    Jasper hi, Is it possible to create a measure that calculates the MAX Value of 2 or more other measures.




    You could do that through a simple IF() stt. comparing two measures as in,

    where: MaxProdID:=MAX([ProdID]) and

    One nice thing is that these two measures do not have to reference columns of the same table.


    Chris hi, many thanks fore your fast response, I have five measures from which to find MAX and when I tried to do it in excel ended up with a horrendous formula, which I did not think practical to incorporate into Measure! Grateful for your advice.




    I’m trying to estimate the size of my tabular environment using your spreadsheet, but the link posted in the blog post below goes to an invalid skydrive location. Can you provide an updated link to access the Excel 2013 version?


    Kasper, I am trying to log a new question but the captcha keeps failing. Can you help me?


    It keeps asking me for 3 – X =0.
    It doesn’t accept 3 as the answer!

    • it does, I just need to approve as well :)


    Hi Kasper

    I am looking to create a dashboard based of power pivot. Data will be loaded from SQL Server using a custom SQL script. However, I want to be able to amend this script and refresh the data in the data model as and when I need to. I want to keep the integrity of the existing pivots.

    Any suggestions on how I do this? Help appreciated!

    • Hi Matthew,

      Not sure what you are trying to achieve but it generally is best practice to build your model on top of views in the SQL database. That way the “model” stays the same even though the underlying database changes. If you change the model itself the PivotTables will change as well.


    I would probably do this by creating multiple IF() measures each comparing two of your “base” measures at a time… so two IF’s compare 4 “base”, 3 IF’s needed to compare 6 “base”. Then, creating another IF()measure to compare each two of the IF() measures. This will get you the “value” of the highest “base” measure = maximum value from multiple columns.

    I used this to find the MAX value among six measures (three measures in each of two tables)








    And to display which column first matches the Max of all 6, I used a SWITCH stt. which hard-codes the names of the column to return if its MAX matches the MAXofALL6 (Not sure how to handle ties, eloquently)




    I have a traditional date dimension linked via integer to a fact table. (One row for each date in the dimension)

    What I notice – whether linking to a Sybase database, or SQL Server Analysis services, is that the drop down list in PowerPivot shows all dates – whether they are in the fact table or not.

    The data is accurate – with regards to the dates that actually show in the powerpivot table.

    But why the dates that aren’t in the fact table show in the drop down list?

    The column is recognized as date (with the list of date filters).

    I have tried creating a link using date fields, per web suggestions, but still arrive at the some end result.


    Hi Kasper,
    I bought a copy of your book recently via Amazon. Is it possible to download a copy of the Workbook(s) that you used to illustrate the Dashboard?

    It would add significantly to the experience.



    I like your book “Dashboarding and Reporting with Power Pivot and Excel” 
    It would be very helpful if you are able to share the Excel files which includes the examples in the book. – Please advice



    I was thinking some more about your question, and realized that you could use the CUBEVALUE() function to directly pull measures into your EXCEL workbook, then you can use the regular =MAX(cell_range) function to get the MAX of the MAX’s (pretty much regardless of the number of measures involved.

    In 2010, =CUBEVALUE(“PowerPivot Data”,”[Measures].[your_measure]”)

    In 2013, =CUBEVALUE(“ThisWorkbookDataModel”,”[Measures].[your_measure]”)

    …and, if you have slicers, you can add additional arguments to have those slicer selections respected.

    Hope you get a chance to check it out. I think you’ll like it!


    Chris, Many thanks for both the ‘IF’ and ‘CUBEVALUE’ solution which appears to be the more elegant and flexible solution. Intrigued by your comments re adding additional arguments to have slicer selections respected, would welcome your comments please as I am enjoying the PPivot learning journey!


    Well, for instance…
    If I have a Slicer that I want to affect my =CUBEVALUE() formula, I just need to add “Slicer {Name}” as an additional argument to the formula. (where: {Name} = {Name to Use in Formula} value, as shown when I right click on the Slicer and choose [Slicer Settings].

    Multiple slicers simply require additional Slicer {Name} entries in the formula.

    So, with a Slicer named {ProdCode}, I simply add that to my formula as,
    ‘=CUBEVALUE(“ThisWorkbookDataModel”,”[Measures].[Total Cases_Shipped]”,Slicer_ProdCode)

    Slicer arguments DO NOT need quotes around them.

    Now, when I choose specific Product Codes via my slicer, the CUBEVALUE() formula will honor the slicer selections; whereas before, it would have returned the Measure results without any filters.

    Other arguments to the CUBEVALUE() function can include specific values of a field, such as for a specific date from your date table, as “[DateTable].[Date].[All].[12/31/12]”

    Pretty powerful stuff. But you’ll probably want to use some descriptive labels in your Excel worksheet so the end-user knows what they’re looking at.


    Also, check out the Help entry in Excel for the CUBEVALUE function. It’ll give you more details.


    I have a pivot table that has multiple connections from 2 SQL database tables. I have tried multiple times to get another users pc setup so that they are able to refresh the Pivot table data. I tried with a direct ODBC connection to the SQL Tables and also Creating an .odc file placed in their “My Datasource” folder.

    I tried to refresh the data connection string and it fails saying that not a valid path see administrator.

    I am looking for the best solution in setting up a user so that they are able to manipulate the Pivot table. Please keep in mind that the original Pivot was created on 64bit pc and the user is 32bit, would that make a difference?

    • Hi,

      The way I understand it you have native pivottables pointing to SQL using Excel. I would recommend importing the data through PowerPivot then you wont need to set it up with a ODC.



    I am enjoying your book “Dashboarding and Reporting with Power Pivot and Excel”. I would like know where I could get a copy of the sample files used in the book?


    Question related to PowerPivot performance and Excel workbook size.
    I’m working with a large database of dental practice data. One of the fact tables has 11 million rows; the workbook is 193mb. I’ve created various measures for each of a rolling 12 months.
    Generally, is it more resource and performance efficient to write somewhat repetitive measures in the PowerPivot window or to do essentially the same thing using Cube functions and adjustments to slicer values.

    Thank you for your help!

    • I don’t think there is any difference, measures don’t take any resources on their own same as the cube functions.


    Hello Kasper

    I have seen you previously have a table which has a date and then all the products which show wtd, mtd, qtd, ytd. I am trying to produce a table where it displays previous week, quarter and year growth from a weekly date, and also weekly and quarter growth from the same period the previous year-if that makes for example if the date was 1st jan 2014 it would show last week, quarter, and year growth, but also the weekly and quarterly growth from 1st jan 2013. I understand my weeks would have to be numbered to match, and for added confusion my years arw july-june.

    Does this make sense?


    Hello Jasper

    I contacted you on twitter initially, for some advise on a powerpoint issue which I cannot get my head around. I want to get to a position which is similar to the positon on pg 53 in the “Data analysis Expressions in the Tabular BI Semantic Model”- which is by date, then current sales, MTD, QTD,YTD, Last Year, PriorYear MTD, Prior Year QTD.

    However i am working with weekly data, which i have numbered, and instead of producing tables with YTD, MTD, QTD I would like to show current week sales (ie 1st jan 2014(numbered week 1)), last 4 weeks sales (from the current week ie 1st jan 2014), last 12 weeks sales (from the current week ie 1st jan 2014), last 52 weeks sales (from the current week ie 1st jan 2014)- as well as comparing growth from the previous year same week (1st Jan 2013 (numbered week 1)), growth between last 4 weeks, and the preious year 4 weeks (same period), previous year 12 weeks (same period)

    I hope this is possible, and makes sense?

    To add confusion to this issue, my financial years are July-June

    i look forward to your resposne, and any assistance you can give



    • Anthony, I would recommend making sure you have a separate date table with two columns, one column that contains a week number by year, the other one that contains a week number since the beginning of your date table. You will use the second column to work with in DAX not to display and will be really helpful with your calculations.

      Now you can use DAX like
      Sales same week 52 weeks ago = calculate([measure], datetable[weeknumberfromstart]=values(datetable[weeknumberfromstart])-52)
      Sales same last 52 weeks sales = calculate([measure], datetable[weeknumberfromstart] >= values(datetable[weeknumberfromstart]-52)
      && datetable[weeknumberfromstart] <= values(datetable[weeknumberfromstart]))

      Hope that helps and gets you started. These two columns will get you a long way.



    i have a dimension table with month_key having values (201201,201202,201203…….202011,202012) and month name ( Jan 12, feb 12,……NOV 20, Dec 20) and a fact table with columns (month_key ,measure_types, Amount)

    My requirement is to create a power pivot report in which when a user select a month from the filter, the report should display the (selected month+18 ) month’s data against each type . when JAN 12 is selected ,the jan 2012 +18 = june 2013 , month name should be populated with months till june 2013 only .

    i tried creating calculated column”END DATE ” in the fact table with dax expression to calculate the 18th monh from the current month as below

    month_key END DATE

    201201 201306

    201202 201307

    and thought of filtering the table with month key <= ENDDATE but it is not working as expected. could you please guide me on this ? Is there any time intelligence function that serve the purpose . Iam using excel 2010 ..hence could not do any calculation on the report side also. please suggest .

    Thanks in advance


    Hi Kasper

    Thanks for the helpful response. I thought I would have a go before I responded, and the DAX equations make sense to me.

    However i have come across an issue. When I try to put the “Sales same week 52 weeks ago” into a table, the following error comes uo “A table of multiple values was supplied where a single value was expected”

    Sales same week 52 weeks ago (With my table names in):

    Sales same week 52 weeks ago:=CALCULATE([UnitSales], Dates[WeekSinceStart]=values(Dates[WeekSinceStart])-52)

    Have you a solution to this error?

    For the second DAX equation when I create the equation in the powerpoint window a yellow error flag comes up with the following statement: “Semantic Error:The Values function expects a column reference for argument ‘1’, but a string or numeric expression was used”

    The DAX equation with my column names in:

    Sales same last 52 weeks sales:=CALCULATE([UnitSales], Dates[WeekSinceStart] >= values(Dates[WeekSinceStart]-52)
    && Dates[WeekSinceStart] <= values(Dates[WeekSinceStart]))

    I bet there are simple things to fix these however at present I cannot work them out.

    Thanks for the help



    Hi Kasper
    I have a problem with the Data Refresh on PowerPivot. I have an application which I have extended to allow an external site on port 443 use Forms based authentication. On the default site in Port 80 I can upload PowerPivot workbooks and my snapshot works correctly. However when I set up a scheduled refresh the job completes successfully but the snapshot image is replaced by a red X. The ULS logs show the following:
    INFO: Terminating capture process: ‘Illegal redirection to resources outside of the web application
    …..:80′ cannot be added to the list of trusted intranet sites: ERROR_ACCESS_DENIED
    Any ideas would be greatly appreciated


    I have a data model with a list of job advertisers and jobs for six months. I want to create a set of advertisers who didn’t advertise in Jan v a comparison month of June. I need to flag these accoringly so I can then filter them and run some analysis on them.

    What I am looking to do is to provide analysis around where the growth in the market has been.



    I downloaded the MecDataMarket.accdb referenced on this website. While the tables follow the same structure in your “Dashboarding and Reporting Book”, the actual data in the tables are different or am I doing something wrong? It is frustrating to follow the examples and get different numerical results than what is contained in the book.


    Hi Kasper,

    If you could point me to a resource (I’m able to find nothing yet) instead of answering that would be great.

    My problem is that I am using TOTALYTD(…,”28/02″) to indicate financial year end. This is a problem for leap years as Feb29 is accumulated into the next year.

    This is not strictly speaking a fiscal\financial year – it is an academic period.

    Any help would be greatly appreciated!

    Thank you :)

    • What happens when you use 29/02 as parameter?


    Hi Kasper,
    My company is switching to office 365 and I have the luck to be a beta tester.
    I have just discovered Power View in Excel which is extremely powerful with amazing interactive maps and you seem to be an expert on that.
    My ultimate goal would be to include these interactive maps in Powerpoint but it seems that I need the stand alone version of powerbI as excel only allows export in PDF ?
    Is there another way ?





    Can you suggest for a solution?
    Or an article or Forum?
    Or you know somebody in Israel I can consult with?
    Or page in “MS SQL Server 2012 Analysis Services”?

    for our challenge :

    We have a need to analyze sales by sales type , region and period (Month/Quarter/Year).
    When choosing the values from the above filters
    the 100% reference should be by the chosen filters and
    not by the whole derived data.
    For example, If I choose to filter and present Sales order of type Lease, Rent (without type Sold)
    for Q 1, Q2 and for regions EMEA & APAC the total percentage per column should sum to 100%.
    It means that I would expect to see the data as follows:

    Order Type | Period
    ……………………| Q1 | Q2
    Lease………….| 30% | 40%
    Rent …………..| 70% | 60%
    Total ………….| 100% | 100%

    I use the following DAX formula:

    Bookings:=if(sum(ORDERS[PRICE]) = 0 ,BLANK(), sum(ORDERS[PRICE]) )

    Grand Total Bookings by Market Segment:=
    calculate(sum(ORDERS[PRICE]),ALLSELECTED(ORDERS[Market Segment]))

    % Bookings by Market Segment:=
    [Bookings]/[Grand Total Bookings by Market Segment]

    but I can’t have the 100% sum by the criteria that I chose – I need to have total
    sum of 100% per column as shown in the table above.
    I need to have in the formula a parameter that holds the user
    filtering values instead of ALLSELECTED.



    Hi Kasper,
    I’ve come across a weird scenario and am hoping you have some insight. My company uses Excel 2010 (although we have Excel 2013 – we use Excel 2010 exclusively for PowerPivot because we have SharePoint 2010). I’ve published several PowerPivot files to SharePoint. Everything works fine except there are times when I open the file that is saved on SharePoint and find that the linked tables are no longer linked. Any relationships I’ve built between a linked table and other data still exist, the slicers work…but the table is no longer linked. So if I want to add a row to a linked table (we often do this because of adding new customers), I have to delete the once linked but now unlinked table from PowerPivot, create a new linked table, rebuild the relationships and redo the slicers. Do you know what would cause this and how we can prevent this from happening going forward?
    As always, thanks for your insight and love your new book!


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

    To give you the picture. I am trying work out the Meal % it shows at materialization of meals and I need the slicers

    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 in 1 table, but under different “codes” The codes are for meal periods, restaurant and hotel.

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

    Jan No of
    Dept Year Meal Period Guests Meals Meal%
    Lobby 2013 Breakfast
    Dinner 3,223 60.31%
    2014 Breakfast
    Dinner 5,071 47.94%
    Outlet Total 8,294 51.97%
    Bar 2013 Breakfast 1,223 22.89%
    2014 Breakfast 2,233 21.11%
    Bar Total 3,456 21.66%
    Rooms 2013 NON F&B 5,344
    2014 NON F&B 10,577

    Rooms Total 15,959

    • correct, you can use the ALL or ALLSELECTED function to calculated the ratio to parent.


    Am I correct in guessing ALLSELECTED is not giving you the results you want (or do you just want to use another function other than ALLSELECTED)?

    ALLSELECTED will work for your example.


    In Power View – how can I terminate a line graph at the current data point while the x-axis continues on out? Currently I get a flat line at the latest data point through the rest of the year instead of just stopping


    Hi. I am having an issue with updating the connection information for text files that I have previously imported. New columns have been added and some have been removed from a previously imported text file. I would like to change the connection information so the new columns are imported. If I go to the table in the powerpivot window, click “Table Properties”, the Source Name field is greyed out and I can’t change it to point to the revised text file, so I cannot seem to update the imported columns. Any ideas?

    Thanks in advance. (I am using excel 2010 with powerpivot v2.)

    • I would recommend using Power Query to import data from csv into Power Pivot. This also works in Excel 2010. The PowerQuery team has build a much better provider to import text files than the Excel provider that Power Pivot uses.


    Hi Kasper, I read somewhere (I can’t remember where), that you blogged about a way to append to tables in PowerPivot using Excel connections. Is there a way to do that?

    If not, what would be the best way to add similar tables into one?

    Thank you for any help.

    • you can use the Power Query append function to append two tables into a single one.


    Hi Kasper,

    We have multiple cubes that have either dates or datetime fields in our cubes. Moreover, the database from which we are pulling the data stores all dates / datetime fields in UTC (Coordinated Universal Time).

    What is the recommended approach to showing this in the users local time zone?




    Quick question. Can you put values into slices. For example if you were building a pivot table by country could you slice the information to show unit sales, then value sales, and volume ect?

    So what i am asking is can you use a slicer to alter the type of value rrpresented



    You might try this example for using PowerQuery Append


    @SIMON LEVINE, instead of going to the table properties, try the following.
    Click Existing Connections, then select the connection to your text file, then click Edit. You may be able to modify the connection to handles the changes to your text file. I have done this successfully for cases where an Excel source file has changed since the connection was initially set up.


    thanks got it ALL is very useful


    Need help with 12 month average formula. The data looks like,
    Product, 2013_12 months average, 2013_6 months average, 2014_ 6 months average,last 3 three months in 2014.
    I’m new to Dax formulas. I try this but it didn’t work.
    =calculate(average([total sales]),year[orderdate], 2013)

    Thank you so much for your assistance



    Hi Kasper,
    I attempted to run your macro from “What is eating up my memory the PowerPivot / Excel edition” but I received a ‘variable not defined’ error for ‘xlPivotTableVersion15′. I am running Excel 2010.
    When I attempted to update this error, I still receieve an error ‘Object doesn’t support this property or method’.
    Any advice?
    Thank you

    • Hi Sean, this macro only works in Excel 2013 not 2010.


    If you are using Professional Plus version, have you tried using Power Query?


    There is no one hard and fast rule for this; should be considered relative to your application. What are you trying to do?


    Hi Kasper

    I want to know, without using VBA, how I can make the data model resize to adjust to a contracting and expanding data set that it links to.

    I have data that streams into Sheet 1, this data can be hundreds of rows in one month and thousands the next, the problem comes in with the relationships to another table in Sheet 2, in that Sheet 1’s unique (Primary key) column now has blanks in it because the previous time that the data was fed in it was much larger than the current data that is in there, and this causes the Error message “Column “UniqueAccountKey” contains blank values and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as a primary key for a table”


    We have created a number of reports using PowerQuery, PowerPivot, and PowerView. We are thinking that it would be good to document the structure of our files (Connections, Queries, Relationships, Measures, maybe even Pivots and Slicers). We are thinking that it would be a good backup in case a file gets corrupt or needs to be rebuilt, and will help us to see if there are any flaws in the model, such as a table being sourced from a temp file instead of a production data source. Before we re-create the wheel, are you aware of this type of code out there?

    • yep, Thomas solution should work.


    Your can check out this entry ( on Rob Collie’s blog. Also, you can download DAX Studio for Excel at CodePlex.


    It sounds like you need Power Query, an Extract, Transfer Load utility, for Excel.


    Hi Kasper, my first time on this blog so not sure of best formatting of questions.
    I have a data set of many tables and thousands of rows of data. For the purpose of this question I will focus on a table called ‘LandSold’.
    I use a measure to count how many Lots of land were sold called ‘SM’ (Selected month). The first Measure formula is:
    1- Measure: SM (calculates the number of sales transactions for the slicer month selected)
    =IF(AND(HASONEVALUE(Calendar[Year]),HASONEVALUE(Calendar[Month Name Short])),TotalMTD(DISTINCTCOUNT(LandSold[ML Numb]),Calendar[Date]),BLANK())
    Two other measures are critical to this issue:
    2- Measure: 12MthSP (Rolling rolling total from next day after last day of selected period 12 months back i.e. this year’s rolling total)
    3- Measure: 24MthSP (calculates rolling total from next day 12 to 24 months back after last day of selected period i.e. previous year’s rolling total)

    The first two measures seem to work all the time. The third measure (24mthSP) works when the slicers choose a complete year otherwise when a specific month is selected the total seems incorrect.

    To illustrate the issue here are two years of data with month slicer selected in three successive steps 1) for Jan 2014, then 2) Feb 2014, then 3) Mar 2014 and the three measures applied

    Note that the other month/year combinations in columns resulted from selecting the indicated year & month slicers then a cut & paste into the table.

    • have you tried swapping out sameperiodlastyear with datedadd ?


    I have several welfare drop in centre that clients can visit several times. I need to find out how many clients were (for eg.) “sole parents with dependants” at their last visit made to the centre. There are slicers for year, month, quarter and location, and this report should change with a change in the slicers.


    I have several welfare drop in centre that clients can visit several times. I need to find out how many clients were (for eg.) “sole parents with dependants” at their last visit made to the centre. There are slicers for year, month, quarter and location, and this report should change with a change in the slicers.


    Assuming you have a calendar table (minimum columns of Date, Year, Quarter, Month) in your model, as well as a Visits table (with minimum columns of ClientID, Name, LocationID and Date) and a Clients table (with minimum columns of ClientID, IsSingleParent, HasDependents), you could create two measures: DistinctClientVisits:=DISTINCTCOUNT ( Visits[ClientID] ) and VisitsFromDistinctSingleParentsWithDependents:=CALCULATE ( [DistinctClientVisits], Clients[HasDependents] = 1, Clients[IsSingleParent] = 1 ). Create a pivot table (with slicers as described) and the measure VisitsFromDistinctSingleParentsWithDependents in values. You could add Client[Name] to rows.


    Correction to Previous Post: Name should have been in Clients table, not Visits table.

    Assuming you have a calendar table (minimum columns of Date, Year, Quarter, Month) in your model, as well as a Visits table (with minimum columns of ClientID, LocationID and Date) and a Clients table (with minimum columns of ClientID, Name, IsSingleParent, HasDependents), you could create two measures: DistinctClientVisits:=DISTINCTCOUNT ( Visits[ClientID] ) and VisitsFromDistinctSingleParentsWithDependents:=CALCULATE ( [DistinctClientVisits], Clients[HasDependents] = 1, Clients[IsSingleParent] = 1 ). Create a pivot table (with slicers as described) and the measure VisitsFromDistinctSingleParentsWithDependents in values. You could add Client[Name] to rows.


    From Thomas’s link, we are using DAX Studio with “select * from $SYSTEM.MDSCHEMA_MEASURES” to document our measures in worksheet. I’m still working on getting the other pieces, but getting the measures so easily was a huge help.



    I am looking to do the following.

    I have a list of say 1000 companies. From here I want to be able to select 10 companies (select by name) and then group the data into two sets. The 10 companies and everything else. When I report in pivot I just want to report with two lines.

    Is this possible?


    Sure, you can do that by just creating a calculated column identifying the “chosen” companies in the COMPANY (like a 0/1, TRUE/FALSE, or “PREFERRED/OTHER” value pair) Then, you just drop that field onto the ROW position for your pivot table.


    Thanks. What’s the best formula to use for multiple companies? IF or is there some other formula which allows me to define a group based on an array where I list the companies?



    Try this.

    You could use a separate linked table (With just your targeted companies in it – so it would be easy to modify), and then this formula would work in the full table listing all companies…


    Rename the calculated column to TARGET_GROUP, then just put TARGET_GROUP on ROWS.

    Don’t forget the relationship from the main list of companies, to your TARGET LIST of companies table.


    You can use Data Validation in the linked table to get a list of possible choices. Add a pivot to show the values for companies, and then use a dynamic formula as a Defined Name to get the values from the pivot for the Data Validation list: =OFFSET(Sheet2!$A$1,1,0,COUNTA(Sheet2!$A:$A)-1,1) You can use the pivot to filter the list of choices.


    Don’t know why the “” got left out of the previous formula…

    =IF(RELATED(‘Of Interest'[Selected])BLANK(),”TARGETED”,”OTHER”)


    Ahh, it’s thinking this is html text and removing the \ symbols for “not equal”, as in \BLANK()

    Trying some escape slashes…


    trying / for !=


    =IF(RELATED(‘Of Interest'[Selected])!=BLANK(),”TARGETED”,”OTHER”)

    Can’t figure out how to show the diverting arrows for NOT EQUAL…

    =IF(RELATED(‘Of Interest'[Selected]) ‘NOT EQUAL’ BLANK(),”TARGETED”,”OTHER”)

    Kasper, We need a better way of posting formulas and sample tables for this blog.

    • ill see what I can do..



    I currently have a Values fact table that has an As Of Date and Effective Date. The Fact table is joined to a Location Dimension table using surrogate key. Both the Fact and Dimension tables have a durable key for the dimension record. Both tables also join to Date Dimension.

    A new record will be inserted into fact when there is a new dimension record or when a value changes.

    What I would like to do in PowerPivot is be able to pass a date for both the As of Date and Effective Date fields and return only a single row from the fact that represents the maximum of both dates for a given location.

    lets say 2 fact records exist for Dimension A with AsOfDates of 1-1-2014 and 4-1-2014 respectively and EffectiveDates of 1-1-2014 and 4-1-2014 respectively and I filter using AsOfDate = 4-16-2014 and EffectiveDate = 4-16-2014, i only want the latest row since it is more current.

    looking for possible way of doing this using DAX if exists.



    Question: Why am I not able to create a running total on a running total using this methodology ->

    Running Total:=CALCULATE(
    [Simple Order Amt],
    EARLIER(‘Date'[Year]) = ‘Date'[Year] &&
    EARLIER(‘Date'[MonthOfYear]) <= 'Date'[MonthOfYear] ) ,1)))

    Running Total of Running Total:=CALCULATE(
    [Running Total],
    EARLIER('Date'[Year]) = 'Date'[Year] &&
    EARLIER('Date'[MonthOfYear]) <= 'Date'[MonthOfYear] ) ,1)))

    I am staying away from date specific functions because most of my data is running totals on non-dates.
    If I change the second measure to this

    Running Total of Running Total:=CALCULATE(
    [Running Total],
    EARLIER('Date'[Year]) = 'Date'[Year] &&
    EARLIER('Date'[MonthOfYear]) = 'Date'[MonthOfYear] ) ,1)))
    (notice the greater than & equal to now only becomes an equal to) it returns data (not a running total but data as you would expect). However the first one posted shows blanks. Why does the operator make any difference?
    Any thoughts on this? How would this be achieved?

    P.s. I know this seems like a ridiculous thing to attempt but it has a purpose in my situation.


    Paul, Happy for your success documenting your models using DAX Studio. Here are a couple more queries to try in DAX Studio:

    For calculated columns try this:

    select distinct Database_Name, Object_Type, [Table], Object, Expression, Referenced_Object_Type, Referenced_Table,
    Referenced_Object, Referenced_Expression, Query from $system.DISCOVER_CALC_DEPENDENCY
    where (Object_Type = ‘Calc_Column’)
    and [Referenced_Table] = [Table]
    and [Referenced_Object_Type] = ‘Column’
    order by [Object]

    For measures try this:

    select distinct Database_Name, Object_Type, [Table], Object, Expression, Referenced_Object_Type, Referenced_Table,
    Referenced_Object, Referenced_Expression, Query from $system.DISCOVER_CALC_DEPENDENCY
    where (Object_Type = ‘Measure’)
    and [Referenced_Table] = [Table]
    and [Referenced_Object_Type] = ‘Column’
    order by [Object]

    If you are interested, I have started a query to obtain relationships, but have not quite nailed it down to how I want the results.

    Let me know what you think.



    Sounds possible.

    Have you tried two pivot tables, both connected to one slicer (where you select 10 or however many companies you want)?



    DISCOVER_CALC_DEPENDENCY is an awesome table! It tells you all the measures you need to fix if you rename something. If you want to collaborate on something here, you can find me through LinkedIn. I’m the only Rudzinski at Cat.


    For the “try this for Calc Columns” and “try this for Measures” additional queries at the bottom…
    I needed to replace/overwrite the single quotes in the queries around ‘Calc_Column’/’Column’ and ‘Measure’/’Column’. I couldn’t just cut-and-paste.

    Somehow, the font makes a difference inside of DAXStudio under 2013.



    Great comment on the single quotes!

    Your comment will help other users as they try out the queries.




    The following is my current query for relationships:

    select distinct Database_Name, Object_Type, [Table], Object, Expression, Referenced_Object_Type, Referenced_Table,
    Referenced_Object, Referenced_Expression, Query from $system.DISCOVER_CALC_DEPENDENCY
    where (Object_Type = ‘ACTIVE_RELATIONSHIP’ or Object_Type = ‘RELATIONSHIP’)
    order by [Object]

    The results for individual relationships are given in parts over multiple rows, but could be pieced together. I looked at all of the other DMVs but could not find any that had better information on relationships.

    Aside: Mike, have you checked out Kasper’s book “Dashboarding and Reporting in Power Pivot and Excel”? I found it filled with jaw-dropping insights into both Power Pivot and Power View. The Kindle version is only $9.99 (USD) and the Kindle Reader for PC was a free download (and hopefully still is). If you would be interested in comparing notes, that would be great!

    – Thomas


    I have put together SQL Server code to create calendar tables within Power Pivot. If useful to you or if there are any questions, please comment.


    The concept for the following date table is based on Kasper De Jonge’s book, but instead extracting date information from an MS

    Access database for a fiscal year, the following functions are for extracting data from SQL Server for a calendar year. Should

    work with SQL Server versions 2008 and later.

    Running the SQL code will create 3 functions into a SQL Server database (assuming you have permissions to do so). The three

    functions are dbo.GetCalendar, dbo.GetDates and dbo.GetNums. GetCalendar calls GetDates and GetDates calls GetNums.

    The function GetDates is from code on page 137 of Itzak Ben-Gan’s book,
    Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions (Microsoft Press, 2012)

    GetNums is from code on pages 103, 135 and 136 of Itzak Ben-Gan’s book,
    Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions (Microsoft Press, 2012)

    During installation, if there are syntax errors, they are most likely because my single quotation marks were converted to another

    unicode character that looks like a single quotation mark. If so, the fix should be a simple replace. I copied and pasted my code

    from SQL Server Management Studio.

    Once the functions are installed in the SQL Server database, you can test the functions with a query something like the


    select * from dbo.GetCalendar(‘20140101′, ‘20141231’) order by [Date];

    Or, if you have SQL Server 2012 and the AdventureWorks2012 is where you have installed the 3 functions in this file, you could

    try this:

    declare @MinDate date = (select datefromparts(year(min(orderdate)), 1, 1) from sales.SalesOrderHeader where orderdate is not

    @MaxDate date = (select datefromparts(year(max(orderdate)), 12, 31) from sales.SalesOrderHeader);

    select * from dbo.GetCalendar(@MinDate, @MaxDate) order by [Date];

    Once you have successfullly installed the code, in PowerPivot create a SQL Server database connection, and in the table import

    wizard, choose “Write a query that will specify the data to import” and then click “Next”.

    Under specify a SQL Query, enter a friendly query name (your friendly name will become your table name in PowerPivot)

    Under SQL statement, you could use syntax like the first example above, or ifyou have SQL Server 2012, you could try something

    like the second example.

    Before proceeding, click “Validate” to ensure syntax is correct

    Once imported, mark your table as a date table, choose “Date” as the date column and set up relationships as needed.

    The calendar table contains a DateID column to support relationships based on integer columns in addition to relationships based

    on dates.

    The first queries below are confirm that your database does not alreay contain objects with the same names.

    Make sure the database you want into add the functions to is the current database.

    There are comment lines (marked /* and */) at the beginning and end of the installation code.

    — Start cutting from here
    IF OBJECT_ID(‘[dbo].[GetCalendar]’) IS NOT NULL
    print ‘An object named [dbo].[GetCalendar], is already in use, please modify the code before use’

    IF OBJECT_ID(‘[dbo].[GetDates]’) IS NOT NULL
    print ‘An object named [dbo].[GetDates], is already in use, please modify the code before use’

    IF OBJECT_ID(‘[dbo].[GetNums]’) IS NOT NULL
    print ‘An object named [dbo].[GetNums], is already in use, please modify the code before use’

    /* Remove before installing



    create function [dbo].[GetCalendar](@StartDate date, @EndDate date)
    returns @Calendar table (
    [DateID] int,
    [Date] date,
    [Year] int,
    YearMonth int,
    Qtr nchar(2),
    [Month] int,
    [Week] int,
    [DayOfYear] int,
    [DayOfMonth] int,
    [DayOfWeek] int,
    MonthName nvarchar(10),
    ShortMonthName nchar(3),
    DayName nvarchar(10),
    ShortDayName nchar(3))
    insert into @Calendar (
    year(d) * 10000 + month(d) * 100 + day(d) as DateID,
    d as [Date],
    year(d) as [Year],
    year(d) * 100 + month(d) as YearMonth,
    N’Q’ + datename(qq, d) as Qtr,
    month(d) as [Month],
    datepart(wk, d) as [Week],
    datepart(dy, d) as [DayOfYear],
    day(d) as [DayOfMonth],
    datepart(dw, d) as [DayOfWeek],
    datename(mm, d) as MonthName,
    cast(datename(mm, d) as nchar(3)) as ShortMonthName,
    datename(weekday, d) as DayName,
    left(datename(weekday, d), 3) as ShortDayName
    from dbo.GetDates(@StartDate, @EndDate)
    order by d


    create function [dbo].[GetDates](@StartDate date, @EndDate date) returns @GetDates table (d date) as
    declare @Days int = datediff(dd, @StartDate, @EndDate) + 1;
    insert into @GetDates
    select dateadd(dd, n, @StartDate) as Date from dbo.GetNums(0, @Days – 1)


    create function [dbo].[GetNums](@Low as bigint, @High as bigint) returns table as
    return with
    L0 as (select c from (values(1), (1)) as D(c)),
    L1 as (select 1 as c from L0 as A cross join L0 as B),
    L2 as (select 1 as c from L1 as A cross join L1 as B),
    L3 as (select 1 as c from L2 as A cross join L2 as B),
    L4 as (select 1 as c from L3 as A cross join L3 as B),
    L5 as (select 1 as C from L4 as A cross join L4 as B),
    Nums as (select row_number() over (order by (select null)) as RowNum
    from L5)
    select top(@High – @Low + 1) @Low + RowNum – 1 as n
    from Nums
    order by RowNum


    */ — Remove before installing



    I am facing certain issues with my SSAS tabular cube (version 11.0.2100.60) where my analysis services start eating up huge amount of memory (5 GB) when i deploy 2 cubes (cube definition file size 2 MB) even before i process these cubes.
    My Dev machine has 8 GB RAM and on processing these cubes the memory consumption increases to 9 GB and system becomes unresponsive.

    I have quite a no. of calculated columns in this cube but the cube and Analysis services used to work perfectly fine while eating only ~1 GB of space with cubes fully processed (cube size approx. 35 MB) but with the addition of a new table to the cube along with some other additions of measures i am now facing this issue.

    I know this should be some very small mistake that i made but can’t figure it out.

    Could you please help me solve my problem.




    See for Tabular sizing recommendations.

    • Running Process Full requires 2 to 3 times more RAM than to store the database at steady state. SSAS holds a copy for incoming queries while Process Full is running. The copy is released when complete.
    • Loading data stores both compressed and uncompressed data while it’s being read and encoded.


    Hi Mike

    I know these concepts and have been working on tabular model for last couple of years.
    the problem we are facing is that even though the cube size is under 35 MB (when processed full) analysis services are consuming over 9GB space when processed full and empty cubes (no Processing of cubes after cube deploy) consuming 5GB space. i have even mentioned the version of the SQL Server i am using (11.0.2100.60). having said that i am not getting the same error with SP1 (CU8).

    So just wanted to know if was an existing bug which was fixed in later releases, as our dev environment has (11.0.2100.60) with no currently no scope of update to SP1/2 in the near future.


    How do I clear multiple PowerPivot databases created on the server, with the workbook names and GUIDs, like


    Hi Kasper,
    How do you count lost customers with conditions? For example you have shops with caregories 1,2,3 and products with categories 1,2,3. A product category 1 should be bought by all shops from 1 to 3, a number 2 in all 2 and 3 shops and so on… not even in Italy were they able to solve this so it has to be something win DAX. Regards Patricio


    I think you would have to define your customers first: are customers the shops or persons that purchase in the shops? are shops virtual (online) or have actual store fronts or both? can you define customers in terms of who and where they are? can you define customer buying patterns and behavior (1 time buyer, outlier, coupon-junkie, regular, etc.)? can you define what it means to lose a customer (they have not made a repeat purchase in a given time period or have stopped responding to a specific marketing tactic, etc.)? Also, to me the discussion of categories is abstract; can you be provide more details about what categories 1, 2, and 3 for products are and what categories 1, 2, and 3 for stores are?


    Hi Kasper,

    why are ALL the links (like in your book “Dashboarding and Reporting” broken ???


    I am trying to work out a YTD for last year when my year runs July-June.

    My YTD for July-June is as followed:
    Value Sale YTD:=TOTALYTD(SUM(Total[Value Sales]),Dates[Time],Dates[Time],”30-06″)

    And this seems to give the figures I require

    But when I try:
    YTD Last Year:=TOTALYTD(sum(Total[Value Sales]),DATEADD(Dates[Time],-12,MONTH))

    It calculates the YTD from last year but Jan-Dec, and not the July-June I require.

    Any insight would be great




    I am trying to work out a YTD for last year when my year runs July-June.I use weekly data.

    My YTD for July-June is as followed:
    Value Sale YTD:=TOTALYTD(SUM(Total[Value Sales]),Dates[Time],Dates[Time],”30-06″)

    And this seems to give the figures I require

    But when I try:
    YTD Last Year:=TOTALYTD(sum(Total[Value Sales]),DATEADD(Dates[Time],-12,MONTH))

    It calculates the YTD from last year but not from the beginning of July like i requested, from the middle of July for some reason

    Any insight would be great



    Your “YTD Last Year” measure (calculated formula) doesn’t make any reference at al to the “Value Sale YTD” measure, or contain any information that the model would know that your year starts in July (your “30-06″ value). There has to be a common point of reference… The two don’t know anything about each other!


    Hi Chris

    Thanks for the response.

    Yes i see.. i must be having a mind blank at present, as how do i make the “ytd last year” relate to the “value sales ytd”. I tried simple to do “value sales ytd” minus 12 months but it didnt work.



    Have you looked at Kasper’s write-up on this?

    Maybe that will help.



    I think this is where Chris is trying to steer you:

    YTD Last Year:=CALCULATE([Value Sale YTD], DATEADD(Dates[Time],-12, MONTH))

    The CALCULATE references [Value Sale YTD] and [Value Sale YTD] explicitly defined that the year end was June 30.

    It works.



    Your insight and directions on Anthony’s issue were so-right-on-the-money.

    Have you ever thought about teaching this stuff?


    Filter for 2+ large sets of data for the same field in powerview (silver light on SharePoint)

    E.G> Filter for 1-1000 is easy (greater then 0 / less than 1001)

    Problem: I have 2 sets of number set 1-1000 and number set 4000-5000.

    Is there a way to filter data for 2 mutually exclusive filter values ?


    I was reading about changing RDL xml values where listing all 2000 individual values.

    Any help would be appreciated.


    Just realize this is powerPIVOT site .. not powerVIEW … sorry.


    We are using Excel 2010 and PowerPivot 11.0.3. I am very new to PowerPivot.

    We have data that needs to be aggregated at different granularities. The lowest granularity is the Hour table. The abbreviated structure of the Base table is
    Timestamp (including hour)

    Each “ID” has 24 records each day.

    The rollup for the Daily table record contains
    Timestamp (Day only)

    The rollup for the Month is aggregated from the Daily record and contains
    Timestamp (First day of the month)

    The rollup for the Year uses the same aggregation as the Monthly.

    We have a Date table with the following:
    Hour of Day

    Because of the one-to-many relation requirement, I was hoping to use the Date and Base tables, and create DAX formulas to emulate the Daily, Month, and Year tables. I’ve tried using SUMMARIZE and GROUP, but have had trouble with the syntax. Getting the data from Hour to Day seems to work using SUMX, but I have been unsuccessful averaging “day” values that have been summed into month values.

    Could someone point me in the right direction?


    Kasper and other.

    I have an odd problem that started recently (maybe after installing the updated version of PowerQuery).

    Scenario: tooltips appear when you write a Dax formular (both calculated formulas and measures). The tool tips show off the entities in your model as well as Dax methods.

    Problem: the delimiter in Dax is “;” (semicolon) and the formular can only compile when “;” is used as delimiter. However, the tooltips only appear when I use “,” (comma).

    If I want to see the tool tips I should write the Dax code as:

    Tooltip problem:=calculate(
    Customer[Post Code] = “2100”,

    However, this code can not be compiled. To compile the code should look like:

    Tooltip problem:=calculate(
    Customer[Post Code] = “2100”;

    This has never been a problem before. I do believe it started 1-2 days ago – maybe after installation of the newest update of PowerQuery and an Office 2013 update.

    Anyone that has experienced the same problem ?

    Any ideas how to solve?

    /Jens Ole


    The formula below(from one of your blogposts) gives me the first sales date. but in my sales table I also have different storeid. How do I get first sales date for respective store and productno?


leave your comment

− four = 0