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: http://www.powerpivotblog.nl/Askyourquestionsarchive.html

  • http://Website Mikko Lipsanen

    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?

    =CALCULATE(
    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!
    BR,
    Mikko

    • Kasper de Jonge

      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?

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

  • http://Website Mikko Lipsanen

    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.

    BR,
    Mikko

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

    • Kasper de Jonge

      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..

  • http://Website Mikko Lipsanen

    Dear Kasper,

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

    • Kasper de Jonge

      Hi Mikko,

      please use kadejoatmicrosoftdotcom

      Kasper

  • http://Website P. Jones

    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?

    • Kasper de Jonge

      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.

  • http://Website P.Jones

    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

  • http://www.v-2-a.com humberto

    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:
    CALCULATE(SUM(Accounts[CF_BALANCE_FINAL]),Accounts[CF_ACTIVE_FILTER]=1)

    The moving avg formula is the following:
    if(HASONEVALUE(Accounts[Snapshot_Date]),
    calculate([MEASURE_ENDING_BALANCE]/COUNTROWS(VALUES(Accounts[Snapshot_Date])),DATESBETWEEN(Accounts[Snapshot_Date],FIRSTDATE(PARALLELPERIOD(Accounts[Snapshot_Date],-11,month)),lastdate(PARALLELPERIOD(Accounts[Snapshot_Date],0,month))),all(Accounts[Snapshot_Date])))

    Any help would be appreciate.

    Thanks.

  • http://www.keyforce.no Patricio

    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??
    Txs!

  • http://Website Liz

    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.

  • http://AboutPowerPivot Advait

    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?

  • http://Website Jess

    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,
    Jess

  • http://Website Ellia

    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?

    Thanks!

  • Kasper de Jonge

    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:
    http://office.microsoft.com/en-us/excel-help/create-a-memory-efficient-data-model-using-excel-2013-and-the-power-pivot-add-in-HA103981538.aspx

    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
    Kasper

  • http://Website Jess

    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,
    Jess

  • http://PowerPivotandSQLExpress Goodman

    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.

    Thanks

    • Kasper de Jonge

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

      Thanks,
      Kasper

  • http://Website Sean

    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?
    Thanks,
    Sean

    • Kasper de Jonge

      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?

      Thanks,
      Kasper

  • http://Website Roger

    Hi,

    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?

    Thanks!

  • http://talentcube.bi Matthew Dewstowe

    Hello

    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?!

    • Kasper de Jonge

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

      Would that work?

      Thanks,
      Kasper

  • http://talentcube.bi Matthew Dewstowe

    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:

    France
    Wales
    Ireland

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

  • http://talentcube.bi Matthew Dewstowe

    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.

  • http://talentcube.bi Matthew Dewstowe

    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!

  • http://Website Peter Wood

    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?

    Thanks,
    Peter

  • http://talentcube.bi Matthew Dewstowe

    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!

  • http://Website Tak

    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.

    Thanks,

    Tak

    • Kasper de Jonge

      Hi Tak,

      I am not aware of a limitation like that but it sounds like a bug, can you file a bug at http://connect.microsoft.com/sqlserver that will allow us to take a look at the issue.

      Thanks,
      Kasper

  • http://Website Rob Adams

    Kasper,
    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?

  • http://Website Tak

    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 http://connect.microsoft.com/sqlserver but let me know if I should report it anyway and I will do so.

    Regards,

    Tak

  • http://Website Sean

    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!
    -Sean

    • Kasper de Jonge

      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?

  • http://Website top n and aggregated others

    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”.

    e.g.
    top 5 products table
    1
    2
    3
    4
    5
    other products total
    grand total

    Thank you for your help!

    Petr

  • http://Website RG

    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.

    • Kasper de Jonge

      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

      Thanks,
      Kasper

  • http://Website Thom

    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.
    Thom

    • Kasper de Jonge

      Hi Thom, we microsoft actualy now has PowerBI where you can share PowerPivot workbooks. Check out http://www.powerBI.com

      Thanks,
      Kasper

  • http://Website Chris Gilbert

    Kasper,
    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?

    Thanks,
    Chris

  • http://Website Tracy

    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…

  • http://Website Sean O’Mara

    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
    -Sean

    • Kasper de Jonge

      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?

      Thanks,
      Kasper

  • http://Website Andrew T

    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,
    Andrew

    • Kasper de Jonge

      Hi Andrew,

      Sure go ahead!

      Thanks,
      Kasper

  • http://talentcube.bi Matthew Dewstowe

    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.

    Possible?

    • Kasper de Jonge

      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?

      Thanks,
      Kasper

  • http://Website Chris

    Matthew,
    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.

    Chris

  • http://Website Praveen

    Hi,
    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.

    Regards,
    Praveen

  • http://Website Praveen

    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)

  • http://Website Jim Sinclair

    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?

  • http://Website Nicholas

    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

    • Kasper de Jonge

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

  • http://Website Chris Gilbert

    Nicholas,
    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!

    • Kasper de Jonge

      Thx Chris for helping out!

  • http://Website Andy

    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.

  • http://Website Andy

    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)

    • Kasper de Jonge

      Hi Andy,

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

      Kasper

  • http://Website Adrianna

    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.

  • http://Website Chris Gilbert

    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 ||

    Chris

  • http://Website Chris Gilbert

    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.

  • http://Website Alekasndar

    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?

  • http://Website Gjor

    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.

  • http://Website Sheena

    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”

  • http://Website Evan

    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.

  • http://Website Sean

    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
    -Sean

  • http://KasperdeJongePowerPivotBlog Liz

    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.

    • Kasper de Jonge

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

  • http://Website Richa

    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”

  • http://Website Phillip

    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.

    • Kasper de Jonge

      Hi Philip,

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

      Thx,
      kasper

  • http://Website Ron Barrett

    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?

  • http://Website Chris Gilbert

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

    Chris Gilbert
    cgilbert@jerviswebb.com

  • http://Website Chris Gilbert

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

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

  • http://Website Tak Yamamoto

    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 ;)

    Regards,

    Tak

    • Kasper de Jonge

      Hi Tak,

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

  • http://www.rosebudtech.com Joe Treanor

    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?

  • http://Website Tak Yamamoto

    That would be wonderful…thanks Kasper! Regards, Tak

  • http://Website AllBee

    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.

  • http://Website Sean

    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
    -Sean

    • Kasper de Jonge

      I think these are the same with a different title.

  • http://Website Stijn Wouters

    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,

    Stijn

  • http://Website Stijn Wouters

    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,

  • http://Talentcube.bi Matthew Dewstowe

    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.

  • http://Website Tak Yamamoto
  • http://Website Enemona

    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.

  • http://Website Enemona

    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

  • http://Website Ron Barrett

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

  • http://Website Krishna Mysore

    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.

    Thanks

    Krishna

  • http://Website im2fast4u

    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 Amazon.com in the United States.

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

    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?

    Thanks

    • Kasper de Jonge

      I’ll ask the publisher what he thinks.

  • http://Website im2fast4u

    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”

  • http://Website Ben Lezin

    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,
    Ben

    • Kasper de Jonge

      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,
      Kasper

  • http://Website Rifkhan

    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..

    Thanks

    • Kasper de Jonge

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

  • http://Website Richa

    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.

    Thanks,
    Richa

    • Kasper de Jonge

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

  • http://Website kym

    Hi

    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
    =sumx(filter(‘DataAccounts’,’DataAccounts'[Type]=”Budget”),’DataAccounts'[Dollars])-sumx(filter(‘DataAccounts’,’DataAccounts'[Type]=”Actual”),’DataAccounts'[Dollars])

    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?

    Thanks

  • http://Website Asit

    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

  • http://Website kfschaefer

    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

    example

    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?

    Thanks,

    Karen

  • http://Website Chris Gilbert

    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.

  • http://Website kfschaefer

    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?

  • http://Website kfschaefer

    Here is my current PivotData Setup:

    let
    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)
    in
    FilteredRows

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

    Thanks,

    Karen

  • http://Website kfschaefer

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

    let
    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)
    in
    FilteredRows

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

  • http://nowebsite David Wu

    Hi, I just downloaded the file from the url specified(http://1drv.ms/1miiVPG) 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.

  • http://Website Andy

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

    Regards

    Andy

  • http://Website Chris Gilbert

    Andy,
    You could do that through a simple IF() stt. comparing two measures as in,
    MAXProdOrDevice:=IF([MaxDeviceID]>[MaxProdID],[MaxDeviceID],[MaxProdID])

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

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

  • http://Website Andy

    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.

    Regards

    Andy

  • http://Website Brock Dietrich

    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? http://www.powerpivotblog.nl/what-is-using-all-that-memory-on-my-analysis-server-instance/

  • http://Website Matthew

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

  • http://Website Matthew

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

    • Kasper de Jonge

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

  • http://Website Matthew Dewstowe

    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!

    • Kasper de Jonge

      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.

  • http://Website Chris Gilbert

    Andy,
    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)

    MaxR1:=MAX(Table1[Random1_1])
    MaxR2:=MAX(Table1[Random1_2])
    MaxR3:=MAX(Table1[Random1_3])

    MAX_R1R2:=if([MaxR1]>[MaxR2],[MaxR1],[MaxR2])

    Max_R1R2vsR3:=if([MAX_R1R2]>[MaxR3],[MAX_R1R2],[MaxR3])

    and

    MaxV1:=MAX(Table2[Values2_1])
    MaxV2:=MAX(Table2[Values2_2])
    MaxV3:=MAX(Table2[Values2_3])

    MAX_V1V2:=IF([MaxV1]>[MaxV2],[MaxV1],[MaxV2])
    Max_V1V2vsV3:=if([MAX_V1V2]>[MaxV3],[MAX_V1V2],[MaxV3])

    Finally,
    MaxOfAll6:=If([Max_R1R2vsR3]>[Max_V1V2vsV3],[Max_R1R2vsR3],[Max_V1V2vsV3])

    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)

    FirstMax:=SWITCH([MaxOfAll6],
    [MaxR1],”Random1_1″,
    [MaxR2],”Random1_2″,
    [MaxR3],”Random1_3″,
    [MaxV1],”Values1_1″,
    [MaxV2],”Values1_2″,
    [MaxV3],”Values1_3″,
    “Ties”
    )

    Chris

  • http://Website cpv

    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.

  • http://Website Ted

    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.

    Thanks.

  • http://Website Claus

    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
    Thanks

  • http://Website Chris Gilbert

    Andy,

    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!

  • http://Website Andy

    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!

  • http://Website Chris Gilbert

    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.

  • http://Website Chris Gilbert

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

  • http://Website kfschaefer

    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?

    • Kasper de Jonge

      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.

      Thanks,
      Kasper

  • http://Website ceegee

    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?

  • http://Website Ron Barrett

    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!

    • Kasper de Jonge

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

  • http://Website anthony good

    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 sense..so 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?

  • http://Website Anthony Good

    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

    Thanks

    Anthony

    • Kasper de Jonge

      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.

  • http://Website sruthi

    Hi,

    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

  • http://Website Anthony Good

    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

    Anthony

  • http://Website Sean

    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
    And
    …..:80′ cannot be added to the list of trusted intranet sites: ERROR_ACCESS_DENIED
    Any ideas would be greatly appreciated
    -Seán

  • http://talentcube.bi matthew

    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.

    Thanks!

  • http://Website Thomas Allan

    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.

  • http://Website MarkD

    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 :)

    • Kasper de Jonge

      What happens when you use 29/02 as parameter?

  • http://Website Bénigne Mathieu

    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 ?

    KR

    Bénigne

  • http://Website Ela Shaked

    Hi,

    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.

    Thanks,
    Ela

  • http://Website Jess

    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!
    Jess

  • http://Website Michael Goodwin

    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%
    Lunch
    2014 Breakfast
    Dinner 5,071 47.94%
    Outlet Total 8,294 51.97%
    Bar 2013 Breakfast 1,223 22.89%
    Dinner
    Lunch
    2014 Breakfast 2,233 21.11%
    Dinner
    Lunch
    Bar Total 3,456 21.66%
    Rooms 2013 NON F&B 5,344
    2014 NON F&B 10,577

    Rooms Total 15,959

    • Kasper de Jonge

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

  • http://Website T Allan

    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.

  • http://Website Ron Risden

    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

  • http://Website SIMON LEVINE

    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.)

    • Kasper de Jonge

      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.

  • http://Website Elma

    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.

    • Kasper de Jonge

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

  • http://Website Michael Costello

    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?

    Thanks,
    Michael

  • http://Website anthony good

    Hello

    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

    Thanks

  • http://Website Chris Gilbert

    You might try this example for using PowerQuery Append

  • http://Website Chris Gilbert
  • http://Website ruve1k

    @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.

  • http://Website Michael Goodwin

    thanks got it ALL is very useful

  • http://Website Jorge

    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

    Jorge

  • http://Website Sean

    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
    -Sean

    • Kasper de Jonge

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

  • http://Website tallan

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

  • http://Website tallan

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

  • http://Website Clint Grove

    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”

  • http://Website Mike Rudzinski

    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?

    • Kasper de Jonge

      yep, Thomas solution should work.

  • http://Website Thomas Allan

    Your can check out this entry (http://www.powerpivotpro.com/2014/03/automatically-create-data-dictionary-for-your-power-pivot-model/) on Rob Collie’s blog. Also, you can download DAX Studio for Excel at CodePlex.

  • http://Website Thomas Allan

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

  • http://Website Peter

    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)
    =IF(HASONEVALUE(Calendar[Year]),CALCULATE(DISTINCTCOUNT(LandSold[ML Numb]),DATESBETWEEN(Calendar[Date],NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE(Calendar[Date]))),LASTDATE(Calendar[Date]))))
    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)
    =IF(HASONEVALUE(Calendar[Year]),CALCULATE(DISTINCTCOUNT(LandSold[ML Numb]),DATESBETWEEN(Calendar[Date],NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE(DATEADD(Calendar[Date],-24,Month)))),LASTDATE(DATEADD(Calendar[Date],-24,Month)))))

    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.

    • Kasper de Jonge

      have you tried swapping out sameperiodlastyear with datedadd ?

  • http://Website Megha

    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.

  • http://Website Megha

    COULD YOU HELP ME PLEASE BEFORE I LOSE MY MIND!
    MUCH MUCH APPRECIATE YOUR HELP
    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.

  • http://Website Tom Allan

    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.

  • http://Website Tom Allan

    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.

  • http://Website Mike Rudzinski

    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.

  • http://www.talentcube.bi Matthew Dewstowe

    Hey!

    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?

  • http://Website Chris Gilbert

    Matthew,
    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.

  • http://www.talentcube.bi Matthew Dewstowe

    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?

  • http://Website Chris Gilbert

    Matthew,

    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…

    =IF(RELATED(Table2[Selected])BLANK(),”TARGETED”,”OTHER”)

    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.

  • http://Website Mike Rudzinski

    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.

  • http://Website Chris Gilbert

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

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

  • http://Website Chris Gilbert

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

    Trying some escape slashes…

  • http://Website Chris Gilbert

    trying / for !=

  • http://Website Chris Gilbert

    =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.

    • Kasper de Jonge

      ill see what I can do..

  • http://Website Scott

    Hi,

    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.

    thanks
    Scott

  • http://Website Derek

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

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

    Running Total of Running Total:=CALCULATE(
    [Running Total],
    FILTER(ALL('Date'),
    SUMX(
    FILTER('Date',
    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],
    FILTER(ALL('Date'),
    SUMX(
    FILTER('Date',
    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.

    • kjonge

      Derek, what if you change ‘Date'[MonthOfYear] to values(‘Date'[MonthOfYear]). The current reference only retrieves data from the current context not from the pivottable that you are in.

  • http://Website Thomas

    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.

    Thomas

  • http://Website Thomas

    Sounds possible.

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

    Thomas

  • http://Website Mike Rudzinski

    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.

  • http://Website Chris Gilbert

    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.

  • http://Website Thomas

    Chris,

    Great comment on the single quotes!

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

    Thomas

  • http://Website Thomas

    Mike,

    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

  • http://Website 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

    following:

    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

    null),
    @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

    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    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))
    as
    begin
    insert into @Calendar (
    [DateID],
    [Date],
    [Year],
    YearMonth,
    Qtr,
    [Month],
    [Week],
    [DayOfYear],
    [DayOfMonth],
    [DayOfWeek],
    MonthName,
    ShortMonthName,
    DayName,
    ShortDayName)
    select
    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
    return
    end

    go

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

    go

    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

    go

    */ — Remove before installing

  • http://none Deepak Sharma

    Hi

    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.

    Regards
    Deepak

  • http://Website Mike Rudzinski

    Deepak,

    See http://msdn.microsoft.com/en-us/library/jj874401.aspx 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.

  • http://none Deepak Sharma

    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.

    • kjonge

      It could definitely be a bug. Let me know if you are still looking for an answer and I can look into it.

  • http://Website Vishal

    How do I clear multiple PowerPivot databases created on the server, with the workbook names and GUIDs, like
    MyWorkbook_ek5b-hk23-j4b-tjrgkrb
    MyWorkbook_ek5b-hkcvb-cvbv-vbvcb
    MyWorkbook_dsf-asdfk-5bhkc-vbcdee
    MyWorkbook_retgb-cxbxcvb-dfg-adfg

    • kjonge

      Use SSMS to delete the models. These are probably left behind by SSDT.

  • http://Website Patricio

    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

  • http://Website Thomas

    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?

  • http://Website Frank

    Hi Kasper,

    why are ALL the links (like http://ppivot.us/Ufs2W) in your book “Dashboarding and Reporting” broken ???

    • kjonge

      HI Frank, this should be fixed. It was broken for a short while. Sorry for the inconvenience.

  • http://Website Anthony Good

    Hello
    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

    Thanks

  • http://Website Anthony good

    Hello

    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

    Thanks

  • http://Website Chris Gilbert

    Anthony,
    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!

  • http://Website anthony good

    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.

    Thanks

  • http://Website Chris Gilbert

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

    http://www.powerpivotblog.nl/get-the-ytd-of-same-period-last-year-using-dax

    Maybe that will help.

  • http://Website Thomas

    Anthony,

    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.

  • http://Website Thomas

    Chris,

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

    Have you ever thought about teaching this stuff?

  • http://Website BROCK

    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 ?

    Thanks.

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

    Any help would be appreciated.

  • http://Website BROCK

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

  • http://Website Chris Gilbert
  • http://Website Lila

    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)
    ID
    Value1
    Value2
    Value3

    Each “ID” has 24 records each day.

    The rollup for the Daily table record contains
    Timestamp (Day only)
    ID
    Sum(Value1)
    Sum(Value2)
    Average(Value3)

    The rollup for the Month is aggregated from the Daily record and contains
    Timestamp (First day of the month)
    ID
    Average(Daily.Value1)
    Average(Daily.Value2)
    Average(Daily.Value3)

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

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

    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?

    • http://powerpivotblog.com/ Kasper de Jonge

      could you give an example of the input and output table?

  • http://Website Jens Ole Taisbak

    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(
    SUM(Customer[Amount]),
    Customer[Post Code] = “2100”,
    )

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

    Tooltip problem:=calculate(
    SUM(Customer[Amount]);
    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

    • http://powerpivotblog.com/ Kasper de Jonge

      Hi Jens, which version of Office do you have? Office 365?

      • Jens

        Thanks for your reply. I’m using Office365 with Excel 2013 Prof edition x64. The system is running on a Mac with virtuization from Parallels on Windows 8.1.

        The problem occurs after an Windows update. I don’t know the KB number. I did reinstall Office 2013 and it worked fine/again until the Office 2013 windage as applied.

        I did install Office 2013 on a fresh Win10 with the same scenario and results.

        Have you heard about this problem before? I have not seen it being reported. If I can assist in any way please let me know.

        Thanks,
        Jens Ole

        • http://powerpivotblog.com/ Kasper de Jonge

          I haven’t seen this either, let me follow up internally and ill get back to you.

          • rwestly

            Hi, I will add to this discussion that I have the same problem with my Excel 2013 version 15.0.4675.1000. But not on Excel 2013 version 15.0.4569.1504. Both Excel installations are on Windows 7 Enterprise SP1.
            Rolf

          • http://powerpivotblog.com/ Kasper de Jonge

            Thanks guys for reporting this, we’ll look into it. @jensoletaisbak:disqus can you please share your Excel version number?

            Thanks,
            Kasper

          • Jens

            Microsoft Office Prof Plus 2013
            Excel 2013 (15.0.4675.1000) MSO (15.0.4675.1002) 64-bit
            English Language

            PowerPivot:
            – Language Options: Match Excel Language
            – Current Language: English (United States)

            If relevant:

            Power Query: Version 2.17.3850.242

            I would be happy to test a KB release – just let me know.

            /Jens Ole

          • http://powerpivotblog.com/ Kasper de Jonge

            can you try to check the List Separator settings under Control Panel
            Region à Additional Settings à List Separator
            under the Numbers tab?

            If it is set to a semicolon
            (“;”) can you please ask the customer to change it to a comma (“,”) and let me
            know if it solves the problem?

          • Jens

            The List Separator was set to “;”. Changing it to “,” didn’t change behaviour – that is;

            1) the DAX code is still compiled with “;”
            2) the tooltip does only apply using “,” as delimiter.

          • Amdi Silword

            Hi Jens Ole

            I have the same problem and also for about 1 month. I have the latest version 15.0.4675.1002. The version is from dec.2014.
            I have been seaching on the net and have not found other writing about this issue.

            I can see you are from Denmark and me too. If rwestly also is from Denmark, then maybe the issue is located to DK. rewestly have ver 15.0.4675.1000. There is no earlier ver which end on .1000.

            Have you find any solution on the issue ??

            In the last four month there have been a new version. Hope there also will come a new ver. in January and the problem will be solved with that.

            Maybe you have some input, Kasper

            Thanks
            Amdi

          • http://powerpivotblog.com/ Kasper de Jonge

            I have followed up with the Excel team and they are looking into it. I’m sorry I dont have more information.

          • Amdi Silword

            Many many thanks for your info. Now we know that the excel team are made aware of the problem.

  • http://Website Dan Falck

    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?

    firstsalesdate:=MINX(
    FILTER(‘Artikelförsäljning_Oms_1′,’Artikelförsäljning_Oms_1′[productno]=EARLIER(‘Artikelförsäljning_Oms_1′[productno])),
    ‘Artikelförsäljning_Oms_1′[salesdate])

    • http://powerpivotblog.com/ Kasper de Jonge

      Try adding store as additional filter argument:
      firstsalesdate:=MINX(
      FILTER(‘Artikelförsäljning_Oms_1′,’Artikelförsäljning_Oms_1′[productno]=EARLIER(‘Artikelförsäljning_Oms_1′[productno]),’Artikelförsäljning_Oms_1′[storeid]=EARLIER(‘Artikelförsäljning_Oms_1′[storeid])),
      ‘Artikelförsäljning_Oms_1′[salesdate])

  • JohnL

    Hi Kasper
    What’s the most efficient DAX way to calculate daily moving weighted average? say i’m tracking past 21 days weighted average sales – past 7 days weighted 50%, Day8 to Day14 weighted 30% and Day15 to Day21 weighted 20%. sales records will be added daily so if the DAX can handle the math part after I refresh the data table that will be great! /JohnL

  • Ben Lezin

    Hello Kasper,

    The DAX formula below threw the error in the image after refreshing my data model with additional data. Is there an upper limit for the LOG10 function since the underlying data structure has not changed? The calculation had been working fine for over a year.
    =IF(HospitalStay[Count of sound_connect_stay_id] <= 0,Blank(),power(10,(Sumx(HospitalStay,LOG10([facility_los]))/COUNTX(HospitalStay,[facility_los]))))
    Thanks,
    Ben

    • Chris Gilbert

      Ben,
      If it isn’t too much trouble, you could create “intermediate” measures to evaluate parts of your formula, which might help you diagnose things.

      • Ben Lezin

        Good idea, thanks. I discovered the root cause after simplifying the DAX. There were negative values in the model that broke the log function. User error.

        • http://powerpivotblog.com/ Kasper de Jonge

          Great find, Thanks Chris for helping out.

  • amit

    Hello Kasper,

    I am trying to Power Query with Office 365 MS Access database.

    I am using Data Source => Microsoft Azure SQL Database.

    I am using the Server & Credential from MS Access Info screen.

    However post passing the above information, i am getting error,mention in the below screen shot

    Can you please let me know where i am going wrong….

    Thanx
    Amit Singha

    • http://powerpivotblog.com/ Kasper de Jonge

      Hi Amit, I don’t see a screenshot. Can you please add it?

  • Joelle Lamarche

    Hello Kasper,

    I wanted to send you this screen shot. This occurred as I was working on PowerView. Per online recommendations:

    “To start or stop the service using the Reporting Services Configuration tool. Start Reporting Services Configuration tool and connect to the report server. On the Report Server Status page, click Stop or Start.

    “To start or stop the service using Services in Administrative Tools. In Administrative Tools, open Services, right-click SQL Server Reporting Services (MSSQLSERVER),
    and click Stop or Restart.”

    I was unable to find the Reporting Services Configuration Tool and I do not have SQL installed on my system. The other recommendation was to refresh the reports. I did this and unfortunately, the issue still persists.

    Any help would be greatly appreciated. The last option is to re-create the PowerView worksheets…
    Thank you,
    Joelle

    • http://powerpivotblog.com/ Kasper de Jonge

      HI Joelle, the text you found is for Power View for SharePoint and does not help for Excel. What does the error tell you when you expand the details?

  • amit

    Hello Kasper,
    Thanx for the quick revert.
    Sorry i had pasted the screen shot earlier,but missed uploading it. doing it now.

    Screen 1 : Am capturing the Office 365 SQL Server information from MS Access Info TAB.
    Screen 2 : In Pivot Query, Server Connection Error.

    Need help on connection.

    Regards
    Amit

    • http://powerpivotblog.com/ Kasper de Jonge

      I talked to the Power Query team, it looks like there is some issue with the ADO.net driver on your system.

  • amit

    Hello Kasper,
    Thanx for the quick revert.
    Sorry i had pasted the screen shot earlier,but missed uploading it. doing it now.

    Screen 1 : Am capturing the Office 365 SQL Server information from MS Access Info TAB.
    Screen 2 : In Pivot Query, Server Connection Error.

    Need help on connection.

    Regards
    Amit

  • Jordy Vondermans

    Hello Kasper,

    I’m new to DAX and I’ve got a question about date functions which you have already explained quite a few times, but is will not seem to work for me. i’m working in excel in standalone modus. i have to alter the functions all the time from using a ; in stead of a , as separator.

    I was working on de TOTALMTD function and getting the same result for the same month last year. (http://www.powerpivotblog.nl/get-the-ytd-of-same-period-last-year-using-dax/)

    But the results of the dates that are used in the fomula: FIRSTDATE = first row in the table, LASTDATE = last day of the month one year before (in this case 31-1-2014 in stead of 8-1-2014).

    i have a table with dates starting in 1950 until today with sale figures. What can i do to correct this problem.

  • tkaresz

    Hello Kasper,

    I read your article Templated Excel workbooks with PowerPivot and Macro’s, which i found very interesting. I tried to use your solution but I got stucked. I am getting error at line OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=” + Servername + “;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=temp;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=” + DatabaseName.

    The solution described by you is working if changed the powerpivot table name ?

    Than you,

    • http://powerpivotblog.com/ Kasper de Jonge

      No you cannot change the model in any way.

  • Zack Goldman

    Hi Kasper,

    I want the people using my report to be able to double click on a particular entry and get a table of the rows that generated that entry.

    I have a calculated field that counts how many students got a certain score on a particular test. The rows, columns, and filters are some combination of school name, grade level, subject, teacher, etc. The calculated field uses the calculate() function to filter for only students who got the particular score I am looking for.

    In one pivot table cell, for example, let’s say that it correctly shows that 8 students in the particular category got this certain score. If I double click the 8, I want a list of those 8 students. However, I get a list of ALL students in that grouping (school, grade, subject, teacher, etc.)–maybe 25 kids, not just the students in that grouping who got the score I care about.

    Any filters I use in the calculate() function (to identify students with the score I’m looking for) ARE being correctly used to generate the number 8, but are NOT being applied to the underlying table when I double click to see the specific rows.

    How can I set this up to list only the 8 students?

    Thanks!

    • http://powerpivotblog.com/ Kasper de Jonge

      Unfortunately this is not possible, the filters are not being pushed down to the table of data.

      • Zack Goldman

        Hmmm…. Are there any workarounds you suggest that would allow for these tables to show the set of rows that are actually being counted?

  • Kate S

    Hi Kasper,
    I’m looking to create a measure that would take the capacity value (a column in a table) for each resource and subtract the forecasted project hours (measure) from the capacity value in order of project priority. Is this possible with DAX?

  • Nick Banbury

    Hi Kasper

    Do you know if it is possible to export data directly from a dashboard created in PowerBI? Say for example I have created a dashboard which includes a bar chart showing registered users by age band. Could I click on the bar for, say, age 25-34, and then export the underlying user data for that bar to CSV?

    Thanks in advance.

    • http://powerpivotblog.com/ Kasper de Jonge

      Hi Nick, this isn’t available today. Can you add this suggestion to Power BI? Click on the ? icon on the top right corner.

      • Nick Banbury

        Will do – thanks Kasper.

      • Nick Banbury

        Sorry – just realised that I can’t do this as I don’t have Power BI installed :) Will do so via the PowerBI support webpage

      • Nick Banbury

        I’ll do this via the web as I don’t have it installed. Thanks.

  • http://talentcube.bi Matthew Dewstowe

    Hello Kasper

    I am looking to create a measure which shows the QoQ% growth for a combination of two variables.

    One field is job category and one location. It’s easy enough to do the volume of jobs and % change for one variable. But what I am most interested in is combining the job_category & locations into permuations, like IT_scotland, IT, wales, IT_england, marketing_scotland, marketing_wales etc. From here I want to see the volume of jobs and then the % change.

    Any help appreciated.

    • http://powerpivotblog.com/ Kasper de Jonge

      Hi Matthew, can you show me pivottable example of the output you want to see?

      • http://talentcube.bi Matthew Dewstowe

        Hi

        Please find a screenshot. I’ve done a simple pivot here, The data is in a sheet not the data model. Essentially I want to remove the yellow column and sort by the green column.

        • http://powerpivotblog.com/ Kasper de Jonge

          Hi Matthew, what I would probably do is create a measure that show the values for last year instead of putting the years on columns. That way you can select a single time period and see all the values you need for that period. Sorting should be possible there as well.

          Hope that helps,
          Kasper

  • Vincent van der Kruit

    Hi, I have a dataset with lineitems, having a start and an end date. Can Powerpivot give me the ‘open position’ for specific dates? (open position is all values after start, before end).

  • Jeff

    Hi…I am new to Powerpivot and have just started reading your book…I am trying to fine the MecDataMarket.accdb file to use through examples…any help would be appreciated. Tx opps…find not fine…

  • paul

    Hi Kasper, the “PowerPivot Management Dashboard Processing Timer Job” in SP2013 fails with this message:

    Errors in the high-level relational engine. The following exception occurred while the managed IDbConnection interface was being used: Login failed for user ‘DOMAINservice_account’

    (this service account has read access to external data sources; used for PowerPivot data refresh)

    Any ideas as to what the error is symptomatic of?

    Thanks,

    Paul

    • http://powerpivotblog.com/ Kasper de Jonge

      Hi Paul, I am following up to see what could be causing this.

  • Joe Berish

    Hello Kasper. I am relatively new to PowerPivot. I am looking to do conditional ranking. Looking at the image below, I want to rank CostPerUnit within RegionID while ignoring blanks. The blue outline contains the table, called TBL_Plants. The red outline contains my desired output. Any help would be much appreciated.

    • Thomas

      Hi Joe, try this as a calculated column (not as a measure/calculated field):

      = CALCULATE (
      COUNTROWS ( TBL_Plants ),
      FILTER (
      ALL ( TBL_Plants ),
      TBL_Plants[CostPerUnit] 0
      )
      Thank you for the sample data; it helped me find the solution.

      • Joe Berish

        That worked like a charm. I have to now go back and understand what the heck it is doing. Thank you very much.

        • Thomas

          Joe,

          Let me toss out a couple of thoughts starting at the bottom and go up.

          TBL_Plants[CostPerUnit] > 0 just ensures where [CostPerUnit] is not a positive number, that row will be left out of the ranking process.

          For the EARLIER functions, I think like: when PowerPivot applies a filter on a table, PowerPivot first creates a copy of that table and “iterates” that that copy one row at a time. As PowerPivot is going row by row through the first copy (the “earlier” copy), PowerPivot creates a second copy of the table that it will scan–from top to bottom–for the purpose of comparing each record in the second table with the “current” record in the “earlier” table. The EARLIER function is a help to PowerPivot to avoid confusion.

          The line

          TBL_Plants[RegionID] = EARLIER ( TBL_Plants[RegionID] )

          ensures that there will be a separate ordering for each RegionID.

          The line

          TBL_Plants[CostPerUnit] <= EARLIER ( TBL_Plants[CostPerUnit] )

          lets only the rows stay in the result set that meet the filter condition applied from the "earlier" table.

          The line
          ALL ( TBL_Plants ) is my attempt to ensure that no pre-existing filters on TBL_Plants will affect the current calculation. It would be interesting to remove the ALL around TBL_Plants and see if it truly makes a difference here–I tend to be cautious (= do some extra things that are not necessarily "wrong" but perhaps just unnecessary).
          The COUNTROWS just counts, for each row in the "earlier" table copy, the number of filtered rows remaining in the second copy. The source of the ranking is just the number of rows in the second copy where [CostPerUnit] is less than or equal to the [CostPerUnit] in the "earlier" copy..
          Hope this helps.

          • http://powerpivotblog.com/ Kasper de Jonge

            Thanks Thomas, Joe as with anything in DAX there
            many ways to skin this cat :). You can also solve this in a measure using Rankx( crossjoin(all(TBL_Plants[RegionID]), all(TBL_Plants[SiteID])),

            [CostPerUnit]) or even something like RankX(Summarize(all(T), T[col1], T[col2]), …

  • Zack Goldman

    Hi Kasper,

    I have two related tables.Say Table 1 is the “many” and Table 2 is the “one” in the many-to-one relationship. For some values in Table 1, there is no matching value in Table 2, if I put the table 2 field in a pivot table, these values show up as as a “(blank)” category (which is what would be expected).

    Is there any way in PowerPivot to automatically name this “(blank)” unknown member category to some other name? I want to provide the users of my report with more context about what is included in this category and why. In a single pivot table, I know I can simply just add this field in as column headers, for example, and then just rename this particular item. However, I will have many different pivot tables that will all use this same field, and which all need the (blank) field named the same thing. Is there a good way to do this?

    Thanks!

    Zack

    • Thomas

      Hello Zack,
      The following is one way. It may not be exactly what you are looking for, but maybe it will spark some ideas:
      I created two tables, Categories (the one side), and Instances (the many side).
      .

      • Thomas

        Hopefully these images will come through.

      • Zack Goldman

        This is perfect! Basically: add the new entry to the categories table and create a calculated column that looks up the categories that match (and assigns the new value to anything that doesn’t match) and create an inactive relationship using the new calculated column! Nice! I haven’t previously used inactive relationships, but I will now! This is a good idea! Thanks!