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.

          • Jens

            Dear Kasper.

            Do you have an update on this one? After several Office 2013 updates the problem remains.

            For the record I do not have the problem in SQL Server Data Tools for Visual Studio 2013. This indicates that this problem is solely in Excel 2013. The problem arise on Dec 22h 2014 (or so) after an Office 2013 update.

            Hope that this problem gets attention.

            Thanks for your support.

            Jens Ole

          • Kasper de Jonge

            Hi Jens, it looks like they have a fix and it will go into one of the upcoming Excel bug releases.

  • 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

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

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

    • Thomas

      Yes – see image.

      Calculated Columns in Forecasts Table:

      Cumulative Role Forecast = CALCULATE ( SUM ( [Forecast] ), FILTER ( Forecasts, [Role] = EARLIER( [Role] ) && [Project] <= EARLIER( [Project] ) ) )

      Remaining Capacity = [Total Role Capacity] – [Cumulative Role Forecast]

      Measures (Calculated Fields):

      Total Remaining Capacity:=SUM ( Forecasts[Remaining Capacity] )

      Total Role Capacity:=SUM ( 'Role Capacities'[Capacity] )

      • Thomas

        Correction: In the Project Name column, second line from bottom, Project Name should have been Project 1.

  • 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

          • http://talentcube.bi Matthew Dewstowe

            Actually in this pivot I have location and job category as rows. It occured to me I should create a new field which concatenates the two, for example London-IT into a sinlge field. I can then report on this permutation.

            So my question is, how do I then create a field which shows the sum of jobs in 2013 and 2014 respectfully. From here I can then create a % change field and sort on this.

            Your help is appreciated!

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

            You make sure you select the current year (lets say 2014) and use the regular sum of values to select the current year. To get the previous year you do something like calculate([sum of value], previousyear(dimedate[date]) that will get the same value for last year. Now you can compare the two. I have several blog posts on this in the past.

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

      • paul

        Thanks kasper – suer hope you can come up with some ideas!

  • 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]), …

          • Thomas

            Kasper,
            Interesting.
            It took me a few minutes to change my perspective from “more thinking like procedures” to “more thinking like sets”, but I finally got it. I also had to think about how the formula engine would process each cell, and then it fell into place.
            Thanks for helping me see other solutions; I will remember this case.

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

  • Ron H

    I’m attempting to create an Excel workbook with PowerPivot data sourced from an SSAS Tabular model that has Row Level security applied. I’ve created a “Role” within the model and added users & the DAX filter query that trims the results of the logged in user via a user/Region bridge table. E.g.: Base table has a region column and the DAX query resolves the user’s region is Boston, so only Boston rows appear.

    While this produces trimmed results while testing the tabular model within VS & SSMS against the model, how does this get properly integrated within the Excel workbook? Hosting on SharePoint, I’ve created a refresh schedule and tested using different test account credentials that are associated with different regions. The only way I can get the refreshed results to reflect the trimmed results is to include the “Roles” property within the Excel workbook’s connection string. I realize this isn’t the secured method to get the desired results but have not got this working otherwise.

    Thanks.
    Ron

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

      Hi Ron,

      That doesn’t really work when you are using Power Pivot, I would recommend using PivotTables and Power View sheets directly on the Tabular mode. When you import data into Power Pivot the “security” gets lost, this is not the case for PivotTables build directly on top of SSAS.

      Hope that helps,
      Kasper

  • Rob Adams

    Need help scrubbing some “date like” data. I’m importing it from a table that stores user entries in a format like “xxxxxxx, mm/dd/yyyy”. We have pulled out the date like entry into a new column as text. Once we did that, we tried to format it and found bogus entries like “2/30/2015” or “do/nt/know”. I’d like to validate the entries as “valid dates” before putting them into a column, and if they’re invalide stick the on 01/01/2015.

    Suggestions?

  • Geir Forsmo

    Hi! Really New to DAX. Need to know how to get the last non empty cell in a column. Let us say I have a month column, jan to dec, and I have a second column with data up to october. And I need to pick up the last value in the second column (october). I thought I could use LASTNONBLANK but it won’t work. Could someone give me a short example on this?

    • Thomas

      Hello Geir,

      I think I can help, but before I try, I would like to know a little about your situation:

      When you say LASTNONBLANK did not work, do you mean you had an incorrect result or your formula raised an error? If it raised an error, what did the error message say?

      When you say column, are you referring to a column within the PowerPivot interface or in a pivot table (in a workbook) that is using PowerPivot data?

      Since you are really new to DAX, are you familiar with the difference between calculated columns and calculated fields (measures)?

      Do you have a calendar table in your model?

      Is your month column numeric (1,2,3…) or is it text (Jan, Feb, Mar…)?

  • Geir Forsmo

    Yes i know the difference. But I thought a function like lastnonblank wthhin a single column should return the last non blank value right a way like var:=lastnonblank ([column]). The powerpivot table has only 12 rows. The Month column will sletta have values as Jan feb and so on. But a measure column may have only value for Jan. So i want it to return the january value

    Not a big issue. I found a way around this it must be an easy way to find this

    Regards gf

    • Thomas

      Hello Geir,

      I created a simple PowerPivot table similar to what you described, I added an ID column as a “sort by” column for the months.

      I created a calculated column and a measure that retrieved the LASTNONBLANK value and they look like this:

      MyColumn = CALCULATE ( LASTNONBLANK ( MyTable[Data], SUM ( MyTable[Data] ) ), ALL ( MyTable ) )

      MyMeasure:=CALCULATE ( LASTNONBLANK( MyTable[Data], SUM( MyTable[Data] ) ), ALL( MyTable ) )

      If you enlarge the image below you will see that the value for the data in October (the last non blank value) is displayed in the calculated column and the measure.

      If I do not include the ALL ( MyTable ) I would not get the results I expect.
      Hope this helps.

  • Rajesh Achanta

    Hi,

    I’m new to Power BI, we’re planning to use Power BI reporting with Microsoft Dynamics CRM.
    What I’m about to ask are probably basic questions, please bear with me :).

    Consider two tables Accounts and Contacts as an example for my questions. Account has one-to-many relationship with Contact. Every contact has a Job Title (pre-defined options).

    1. Adding custom columns
    I need to create a Power Pivot table in which I want to show the Account Name, followed by counts of contacts by Job Title. For example, assuming ‘Sales Person’, ‘Marketing Manager’ as possible options for Job Title, I’d like to see

    Name | Count of Managers | Count of Sales Person |
    ABC Company 3 7

    How do I write the Formula for my custom column to achieve this ?

    2. Sub-reports with Pivot Table ?

    Following the same example above, if ABC Company has 10 contacts in total, I would like to have a functionality to show the related contact details (all) when I click on the company name. This data needs to be displayed directly under the Accounts pivot table.
    So when I click on ABC Company I should get the details similar to this

    FullName | Job Title | Email | …
    John Smith Marketing Manager …
    Jack … …

    Is this possible with Power Pivot ? If not, what is the best way in Power BI to achieve this ?

    Please assist, thanks in advance.
    Rajesh

    • Thomas

      Rajesh,
      I am certain you could do something like what you are requesting with Power Pivot. For getting a handle on DAX formulas, which are used in Power Pivot, I would recommend the following book: DAX Formulas for Power Pivot by Rob Collie.
      For your second question I think you will have a problem with the layout because pivot tables can expand vertically, so it is a moving target to establish what is directly below a pivot table. Consider the tables side by side instead. As far as clicking on the company name, I think your results could be obtained using a slicer for Company Name that is connected to both pivot tables.

  • http://batman-news.com Sarah

    I have a co-worker that create a PowerPivot using the wizard from 2 excel files and is getting the following error…
    OLE DB or ODBC error: No value given for one or more required parameters..
    An error occurred while processing table ‘CMP Load’.
    The current operation was cancelled because another operation in the transaction failed.
    Any ideas on how to fix it?

    • Thomas

      Hello Sarah,
      The message reads as if your co-worker was trying to connect to the data source (an excel file in this case) using the Others (OLEDB/ODBC) option with incomplete connection information.
      If so, a fix would be to use the “Build” command button to create the connection string and test the connection before leaving the Table Import Wizard.
      Better fix to try is the Excel file option under “Text Files”.

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

        Thanks Thomas, you can also try to use Power Query to import your data.

  • Sophie Marchand

    Hey,

    I bought your book “Dashboarding and Reporting with Power Pivot and Excel”. I do not find any link to download the excel file (with de power pivot model) that is used throughout the book. This makes it hard to follow… Would there be a way to get the file?

    Thanks a lot,

    Sophie

  • Andy Josolyne

    Kasper hi, I am designing a data model PP 2010 at home, and would like to install it on a friends pc. Can I modify the connections within PP to connect to my friends files. (files data structure and attributes will be the same)

    • Thomas

      If the type of data source is also the same, it will work. In other words, if you are getting data from Microsoft SQL Server and your friend is using Microsoft Access, it will not work–but if you are both using, lets say, CSV files, it will work.

      • Andy Josolyne

        Thomas hi, many thanks, is this true if you have made connections within the Power Pivot Model and made an external connection via Power Query or another file. Reason for asking is that I tried to modify connection and unless the location and file name is exactly the same it created a new connection and table – so danger of loosing all the measures from original table.

        Regards

        Andy

  • Mohammad Kokash

    Hi Kasper

    I am building a data model based on excel report from the customer but i have an issue creating the Fact Table and dimensions cause data comes in unbalanced levels like below:

    A. Revenues:-
    Food Revenues:
    – Organic food
    Non Organic Revenues:
    – Processed
    – Repayment
    – Other
    -oil
    -wheat

    whats your advice in such a case?

    Thanks

    • Thomas

      Hello Mohammad,
      In the PivotTable Options dialog, check the checkbox for “Show items with no data on rows”.

  • Volodymyr Paliy

    I recently connected 3 tables of data via PowerPivot. When creating a pivot out of data model I have an issue – if I select SKU# as a ROW and then I select EAN# from another table connected via SKU# it puts Each existing EAN under EACH SKU while I was waiting it to be tabluarly organized certain SKU- certain EAN. Any suggestions?

    • Thomas

      The default action in the field list when checking-off non-measures is to place the non-measures on rows. In the Fields list, drag your EAN non-measure to the Columns zone and you will have desired table effect.

  • Andy Josolyne

    On several occasions I have inserted a Power Pivot Table added some measures and find that it changes to a Pivot Table – so losing the ability to add measures/slicers etc. Advice requested why this is occurring and how to convert it back to a Power Pivot Table.

    • Thomas

      Andy,
      My first impression in reading your question is that I do not understand what is really happening–Power Pivot tables do not “convert” into Power Pivot pivot tables. You can insert a pivot table into a worksheet that has a connection to Power Pivot tables.
      In my mind there are:
      1) Power Pivot interface: a window accessible through the PowerPivot tab in Excel for managing the Power Pivot environment.
      2) Power Pivot tables: tables located in the Power Pivot interface.
      3) Power Pivot pivot tables: pivot tables inserted into Excel worksheets with a connection to the tables displayed in the Power Pivot interface.
      4) Non-Power Pivot pivot tables: Regular Excel pivot tables based on a single table (but can use functions like VLOOKUP to extend their reach into other tables)
      5) Linked tables – a data source option for Power Pivot tables (but is not the same thing as a Power Pivot table)
      None of the five items listed above can change into one of the other items. Can you post a picture of the “converted” pivot table that includes the ribbon interface above?
      I want to help, but I know my mental picture of what is happening is incomplete.

  • Ben Lezin

    Hello,
    Has anyone else had an issue with the ‘Analyze in Excel’ feature in a Tabular project in VS 2012 when trying to impersonate another user? Can’t figure out why it randomly works for one user using the USERNAME function to get the login ID for a DAX filter, but doesn’t for all others.
    Any suggestions would be appreciated.
    Thanks,
    Ben

  • Michael

    Hi
    In powerpivot, i have started using conditional formatting. I have created my own rules. The problem is when i refresh the powerpivot all the conditional formatting is lost.
    Looking around, this seems to be a Microsoft problem
    any ideas?
    thanks
    michael

  • Ben Lezin

    Still running into snags with role security using DAX filters using the tabular model. The role level security doesn’t persist once I deploy the VS 2012 project to SSMS 2012 on my local machine.
    Has anybody ever tried this or encountered the same issue?

    • Thomas

      Ben,

      Here is link to article “Getting Started with Row Level Security” by Bradley Ouellette which will hopefully help out:

      http://blogs.msdn.com/b/analysisservices/archive/2012/08/10/row-level-security-in-analysis-services-2012.aspx

      • Ben Lezin

        Thanks. However, I’ve implemented a similar solution that has inconsistent behaviour when members of the role connect to my local instance of the tabular database. The filters are not applied to some users and they can see all of the data. Yet it works for others. I’m guessing it has something to do with the privileges certain users have to access my local instance regardless of the the tabular roles. Still investigating.
        Ben

      • Ben Lezin

        Turns out this is related to the test user being a member of an AD group with local admin privileges. So it appears to be by design if the admin overrides the impersonation functionality.

        • Thomas

          Ben,
          This is a good insight.

  • David

    I have a data set that consists of hospital billing data. I am trying to develop a data model in PowerPivot that will allow for easy calculation of readmissions (for any given patient, any admission that occurs within 30 days of an initial admission is counted as a readmission). One column flags initial admissions (for a particular diagnosis that the hospital is tracking). And a calculated column flags all rows that are possible readmissions:

    =CALCULATE(COUNTROWS(DATAtable),ALL(DATAtable),DATAtable[Initial Admission]=TRUE,DATAtable[PatientID]=EARLIER(DATAtable[PatientID]),DATAtable[DischargeDate]=EARLIER(DATAtable[AdmitDate])-30)

    This formula works quite well 95% of the time. Occasionally, a single patient is admitted multiple times within the 30-day window. This should only count as one readmission, not multiple readmissions. It seems that I need to sum the calculated column for distinct patients in 30-day windows from the initial admission. Any suggestions?

  • Ben Lezin

    Hello,

    I’ve implemented a simple tabular model using SQL Server 2012 SP1 SSDT with a date dimension that has an active relationship with the date key in the fact table and has met the criteria for a date dimension to the best of my knowledge, i.e. no missing dates, etc.

    However, when I add a calculated measure in the VS project using any time intelligence function, i.e. =CALCULATE(SUM([NewPatientTotal]),TOTALYTD(DimDate[Date])), the calculated measure only returns the value of the current member, i.e. SUM(NewPatientTotal]) without throwing an error. This is happens for all time intelligence functions!

    Are there any best practices that I’ve missed? I’ve scoured the SSAS forums to no avail.

    Thanks,

    Ben

    • Chris Gilbert

      Ben,

      I think the problem is that you’re trying to use the TOTALYTD function as a Filter rather than as a Result. The syntax for the CALCULATE() function is:
      CALCULATE(, , , …)

      • Ben Lezin

        Didn’t work, but thanks Don’t see many examples using the tabular project in VS, so maybe it’s yet another bug that hasn’t been addressed by Microsoft.

        • Ben Lezin

          Incidentally, is there another forum that you would recommend for these issues? I’ve noticed the questions don’t get any traction from the moderator.

          • Chris Gilbert

            Hey, sorry it didn’t work for you. It’s so hard (at least for me) to know exactly what problems you’re seeing without a sample model to play with. I’ll still keep trying to point people in the “right” direction. (I wish we had an easy/automatic way to submit such sample data with our questions.)

            Well, anyway, I know that Kasper is pretty busy at times. His responses seem to come in spurts, but they’re just about always “spot-on”. It’s tough when you’re really looking for an immediate answer – and probably under a deadline, as well.

            You might try posting your question over on

            http://www.powerpivotpro.com/the-faq/

            http://sqlblog.com/blogs/marco_russo/default.aspx

            Some pretty sharp guys at both those sites, too.

            Most of my work is done in Excel, so I’m pretty much blind to any incompatibilities with tabular models in VS and/or SQL Server.

          • Ben Lezin

            Thanks, will try those. I know Marco Russo is outstanding. I wouldn’t be surprised if there is a different approach for developing DAX in VS versus Excel. Will be interested to see how easy it is to implement Tableau compared to Tabular, since we are still looking at other tools.

          • Thomas

            Ben,

            Another possible factor is if you are using “DirectQuery” mode in SSAS (many “out of the box” time intelligence functions need to be re-worked to obtain desired functionality). Please follow this link if you would like to assess if you are running in “DirectQuery” mode:

            https://msdn.microsoft.com/en-us/library/hh230898.aspx

            Marco Russo also has a lot to say about changes required in time intelligence functions when running in direct query mode. If DirectQuery applies to your application, there is another url I could leave with you.

    • Thomas

      Ben, instead of CALCULATE(SUM([NewPatientTotal]), TOTALYTD(DimDate[Date]))”, replace the TOTALYTD with DATESYTD:

      = CALCULATE(SUM([NewPatientTotal]), DATESYTD(DimDate[Date])),

      Notes: DATESYTD returns a table, while TOTALYTD only returns a scalar (non-table).

      If you want to use TOTALYTD, try using two parameters without the CALCULATE like this:

      = TOTALYTD( SUM([NewPatientTotal] ), DimDate[Date] ) )

      If this works (and I believe it will), I would recommend a great book, just published, on DAX called “DAX Patterns 2015” by Alberto Ferrari and Marco Russo. The solutions I proposed come straight out of page 27 (see the last formula on page) and page 28 (also, see the last formula on page). Except for Chapters 1 (DAX Fundamentals) and 2 (Time Intelligence Functions) all of the chapters in the book can be found through visiting these two websites:

      http://www.daxpatterns.com/
      http://www.daxpatterns.com/patterns/

      • Ben Lezin

        Nope, but thanks, anyway. Unfortunately, I get the same result, i.e. SUM(RejectMetrics[NewPatientCount]), when I use =TOTALYTD(SUM(RejectMetrics[NewPatientCount]),DimDate[Date])
        OR =CALCULATE(SUM(RejectMetrics[NewPatientCount]),DATESYTD(DimDate[Date])).
        Could it matter that the fact table may have gaps for some days for certain groupings? Never made a difference when I did it in PowerPivot in Excel.

  • Glo Ann Odevilas

    I created a PowerPivot Report.
    My data source is an Excel file located on a dropbox folder which is shared between team members.

    My problem is that when other people refreshed the data source they get an error saying that the data source is located on MY local machine even though they have it in their dropbox folder as well.

    Is it possible to build the link to the data source in a way that forces powerpivot to look in its current folder..?

    If i just write the file name and refreshes it seems to work, but when reopening the file the url has changed to this and only works on my computer.

    C:UsersMynameDropbox1 CompanyReportsReport Source Data.xlsx

    Thanks!

  • Kieren

    Hi – I found this post on exporting to .csv from power pivot:

    http://www.powerpivotblog.nl/export-a-table-or-dax-query-from-power-pivot-to-csv-using-vba/

    Is it possible to do something similar straight from a Power Query?

    Many thanks!

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

      no this is not possible directly from Power Query.

  • Ben Lezin

    Hello,

    I’m trying to calculate the previous day with a non blank value using the following but it doesn’t work:

    Previous Net Funded Volume:=CALCULATE([Net Funded Volume],LASTNONBLANK(PREVIOUSDAY(DimDate[FullDate]),[Net Funded Volume]))

    It calculates the previous day’s value but includes the blank values if the previous day was blank.

    Any ideas?

    Thanks,

    Ben

    • Ben Lezin

      BTW, figured this one out using the DATESBETWEEN function.

  • John Donnelly

    I have a table in PowerPivot which is a sequential time series of transactions. One column is the Date and Time of the transaction and the other column is the transaction number. I want the elapsed time for each transaction in a Calculated Measure. I have DateTable with Hour Minutes Seconds joined. Thanks!

  • Chris Bailiss

    Hi Kasper, I have a couple of follow-up questions from your SQL Bits “Developing for Power BI” session. The limitations chart at the URL below states maximum data sizes limit (1GB or 10GB) and maximum streaming throughput limits (10k rows or 1M rows per hour).
    https://powerbi.com/dashboards/pricing/
    Questions are: (1) How are the data size limits measured? Is this the size of the raw data or the size of the compressed tabular model? The page isn’t specific about what the size limit applies to. (2) Do the throughput limits apply ONLY when using the Azure Stream Analytics preview connector or do they also apply when using the REST API? e.g. if using the free Power BI tier (and assuming I don’t go over the size limit), is the maximum number of rows I can submit per hour limited to 10k (e.g. 2 calls within an hour of 5k or 4 calls of 2.5k, etc)? Am I understanding correctly? Thanks

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

      Hi Chris,

      ​I would recommend asking these questions at http://stackoverflow.com/questions/tagged/powerbi

      The developer API team will answer any questions there. I don’t know the answers to these questions myself either, asking it there is as fast as I would do it myself.

      Hope that helps,
      Kasper

      • Chris Bailiss

        Thanks Kasper.

  • THIAM HUAT Tan

    can I find where can I find the database (MecDataMarket.accdb) which you used in your book for all the numerical examples?

  • Gerald Magno

    Hi Kasper,

    I have encountered this issue in office 365 that power query cannot get data from a non-english subsite.

    also found same issue here by Jeroen: http://schoennie.blogspot.nl/2014/06/use-english-as-your-0365-sub-sites.html

    any updates on these?

    Thanks!
    Gerald

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

      Hi Gerald,

      I asked the Power Query team and it is still a known issue that is on the backlog to fix.

      Thanks,
      Kasper

  • J. gM

    Just bought you book Dashboarding and Reporting with Power Pivot and Excel, Holy Macro! Books.
    I cannot make heads or tails of the shortened web links you provide in the book. Is there a site map anywhere that would allow me to find that http://ppivot.us/DBRLO ?
    Is there anywhere I can download your Contoso database as used in your book to populate the spreadsheets ?
    These two serious issues make using your book much more difficult.
    Regards

  • J. gM

    must have a mistake when I typed in the DBRLO url, it works correctly. Got confused with the book indication that this was only shorthand.

    • Thomas

      Hello J.gM,

      The reasoning (which Kasper does give in the first part of the book) can be summarized as: which would most people prefer to use, a short url or an url that can be two or three times as long?

      The database used for most of the examples in the book is the MecDataMarket (an MS Access database) can be found here:

      https://onedrive.live.com/?cid=7f4e0559cc74581a&id=7F4E0559CC74581A%2116667&ithint=file,.zip&authkey=!AKSV_LWMtukEJb4

      • J. gM

        Hello,

        Thank you for your quick response. Only noticed it this morning 09:00 local time (UTC-5.00 – New York – Montreal).

        Try as I might, I always end up with the following error message:

        “Unrecognized database format ‘C:Excel PivotMecDataMarket.accdb’.”
        Cannot even open it in Access 2013.
        Would you please help me with respect to this ?
        Regards

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

          Hi, please send me an email me directly at kasper at powerpivotblog dot com and well sort it out.

          • J. gM

            Tried to email from the corporate mail server, which is not the GMail account I used to post to this blog. System is unable to deliver the message.
            In any case everything works just fine now.
            I will soon put up a 5-star review of your Holy Macro! book on Amazon (where I bought your book).

          • Kasper de Jonge

            great to hear you have it all figured out. thanks your interest!

  • David Arranz

    I have a big folder with 2500 files (85GB in total) when loading in Power pivot (Excel 2010) any of those files even the smallest ones (17KB) always take at least 1m30s to load. The same file in another folder could take 5-10 secs. Why the size of the folder matters?

    • Kasper de Jonge

      Hi David,

      Are you using import from “text file” in the Power Pivot window?

      Thanks,
      Kasper

  • MadeinAmerica

    Hi Thomas – Good evening! I hope you may be able to help me with what seems to be a fairly easy proposition. I have a series of cells each containing 8 digits. I have another set of cells which contain 8 digits. I want to be able to identify those that have only 1 digit difference – not necessarily value difference. The difference can be in any part of the digits. For example: #1: 43568994. #2: 42568994….so these two have only 1 digit difference, the “3” and the “2” and would show up as a “match”. Basically the formula hopefully can find the #’s that have 7 out of 8 digits that are the same. I hope this makes sense! Thank You so much, and keep up the awesome work!!!

    • Thomas

      Hello MadeInAmerica,

      Is this for Excel or PowerPivot?

      Since all the cells contain 8 digits, consider handling the values as text.

      If this is for Excel, and assuming columns A and B contain 8 digits each to compare, and beginning at row 1, you could enter in C1 something brutal like

      = IF(7= (IF(MID(A1,1,1)=MID(B1,1,1),1,0) + IF(MID(A1,2,1)=MID(B1,2,1),1,0) + IF(MID(A1,3,1)=MID(B1,3, 1),1,0) + IF(MID(A1,4, 1) = MID(B1, 4, 1),1,0) + IF(MID(A1,5, 1) = MID(B1, 5, 1),1,0) + IF(MID(A1,6, 1) = MID(B1, 6,1),1,0) + IF(MID(A1,7, 1) = MID(B1, 7, 1),1,0) + IF(MID(A1,8, 1) = MID(B1, 8, 1),1,0)), TRUE, FALSE)
      or you could isolate your logic in a custom function.

  • J. gM

    Odd thing in the formula editor for a calculated field – a Name like “AbsolutelyTheLastInvoiceFiscalDate” when pasted in the formula edit box is shown as “Absolutely The Last Invoice Fiscal Date” — spaces magically appear.
    On top of that, “fixing” the name back to what it was entered – suppressing the spaces – causes the rejection of the entire formula !

  • nathan f

    Reading your book Dashboarding and Reporting with Power Pivot and Excel and the short URLs aren’t working to bring me to the web pages example: http://ppivot.us/Zz1zb on page 114.

    • Kasper de Jonge

      hmm it looks like Excel broke some of their links. I have to investigate this, thanks for mentioning it.

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

      hmm it looks like Excel broke some of their links. I have to investigate this, thanks for mentioning it.

  • J. gM

    Stuck at page 79 of your HolyMacro! Dashboarding and Reporting book.
    How can I add the “period” (YTD) in the pivot table of the “Datasheet” (i.e. getting the selected Slicer option)? And how do I add the “CurrentDate”?
    By the way, congratulations for your url regarding charts.

  • Josh

    Hi, I’m using Excel 2010. I have a PowerPivot graph with dates in the x-axis. Currently I have 3 slicers, “Years”, “Months”, “Date” which all come from my calendar table. I can easily change the graph to display only the selected time period of my choice. I would like to remove the three slicers and replace with one slier with the following options “Previous Week”, “Previous Month”, “Previous Year”. All three would simply change the graph to display todays today and go back that set amount of time. Tried using datesbetween, filters, all to no avail. This problem is kicking my but so have come to the master. Thanks for your help!

    • Thomas

      Hello Josh,
      In Kasper’s, “Dashboarding and Reporting with Power Pivot and Excel”, he describes this technique in the section “Reporting Dynamic Date Ranges using Slicers” (see pages 72-82 of the printed version of the book).

      • Josh

        Thanks for the reply and my apologies for not posting my question correctly the first time. I’m not trying to change the data being displayed, simply change the time span which I’m viewing. So for example if I select “Previous Week” the x-axis of the graph only displays 3/19/2015, 3/20/2015…..3/26/2015.

        • Thomas

          Josh,
          I think I better understand your needs (you do not want to change the data being displayed, but rather simply change the time span that the graph is letting you view), but I am still convinced that the technique would be similar (using a disconnected table, as the source for the slicer, and a switch statement to that would effectively manipulate the date range that is being graphed).
          If you could post some sample data, I would like to try my idea out.
          I want to better understand you time criteria. Given “today” is March 26, your given range for previous week (3/19 to 3/26) spans an eight day period that ends with today, so a previous month would be a 31 day period that ends with today and a previous year would be a 366 (or a 361) day period that ends with today?
          If I really do understand the criteria, I definitely have an answer that could be applied to sample data and a sample measure that could be supplied by you.
          What type of graph are you using: a line or bar or column or area or something else?

          • Josh

            You got it perfectly. Regarding how long a week last (7 or 8 days) or a month last (30 or 31 days) I’m not picky. I’m using line graphs, bar graphs, and stacked area graphs. Not sure how to post sample data so I posted a link to my file here https://www.dropbox.com/sh/oomt1zwjh7n660p/AAB0xDAjogUEk9WtJXLETAD9a?dl=0 (simply press the x button when it asks you to sign up). Much appreciated!!!

          • Thomas

            Josh,
            I downloaded file. Should get back to you Monday at latest.

          • Josh

            fantastic. Thank you.

          • Thomas

            Josh,
            I downloaded file. Should get back to you Monday at latest.

          • Josh

            fantastic. Thank you.

          • Thomas

            Josh,

            Here is link:

            https://www.dropbox.com/s/i5w13po1pcdf4r2/DateRangeDemo.xlsx?dl=0

            Except for what I needed to demo, all of the other content (worksheet pages and power pivot tables) have been removed to simplify the presentation.

            The linked spreadsheet contains two graphs, both representing Actual BOED. The top graph is static and used for comparison only.
            The bottom graph responds to the slicer “Previous Period” (options for previous week, previous month and previous year). The title over the bottom graph is also coordinated with the slicer and responds appropriately to slicer selection.
            Let me know when you download file. After you evaluate, and it is as expected, I’ll explain what I did.

          • Josh

            Absolutely genius! Works Perfectly. So simple yet I would have never come up with this solutions. Really appreciate your help on this one. THANK YOU! I hope others can benefit from this post as it seems like a common feature that folks would want to use when using dates.

  • Josh

    Hi, I’m using Excel 2010. I have a PowerPivot graph with dates in the x-axis. Currently I have 3 slicers, “Years”, “Months”, “Date” which all come from my calendar table. I can easily change the graph to display only the selected time period of my choice. I would like to remove the three slicers and replace with one slier with the following options “Previous Week”, “Previous Month”, “Previous Year”. All three would simply change the graph to display todays today and go back that set amount of time. Tried using datesbetween, filters, all to no avail. This problem is kicking my but so have come to the master. Thanks for your help!

    • Thomas

      Hello Josh,
      In Kasper’s, “Dashboarding and Reporting with Power Pivot and Excel”, he describes this technique in the section “Reporting Dynamic Date Ranges using Slicers” (see pages 72-82 of the printed version of the book).

  • Ben Lezin

    Hello again,

    I ran into another issue which seems like a DAX bug. The DAX function ALLSELECTED works fine until I clear the filter of a slicer and it recalculates at more granular context than what is selected (see image below).
    The column RecoverCountPerSelected is using the following DAX: RecoverCountPerSelected:=DIVIDE([RecoverCount],Calculate([RecoverCount],ALLSELECTED())).
    Is this a known bug and is there a workaround?
    Thanks,
    Ben

  • Ben Lezin

    Hello again,

    I ran into another issue which seems like a DAX bug. The DAX function ALLSELECTED works fine until I clear the filter of a slicer and it recalculates at more granular context than what is selected (see image below).
    The column RecoverCountPerSelected is using the following DAX: RecoverCountPerSelected:=DIVIDE([RecoverCount],Calculate([RecoverCount],ALLSELECTED())).
    Is this a known bug and is there a workaround?
    Thanks,
    Ben

    • Ben Lezin

      This is only reproducible when I use a hierarchy for the row values. So it looks like the bug is related to using a hierarchy for the row values, the ALLSELECTED calculation, and clear filter from the slicer.
      You’re welcome, Microsoft.

  • Ben Lezin

    Hello,
    Got another question for anyone who is willing to help. I’m trying to create a measure that sums the RejectVolume from the Reject table for each distinct Reject Reason (AEExceptionType in the Exception table). The Exception table is related to the Reject table via the RejectID – one reject can have many reject reasons, i.e. AEExceptionType(ID). However, I can’t seem to find a calculation that works using relatedtable, etc.
    Any ideas would be much appreciated!
    Thanks,
    Ben

    • Thomas

      Ben,
      You always have interesting questions, I wish I could answer more of them. I may be able to help here, so let me see if I can wrap my mind around this one.
      The unique record identifier in the Reject table is the RejectID. Reject is related to Exception via RejectID, so Reject is the parent of Exception. I would like to learn more about the RejectVolume, I am reading the description as RejectVolume is a column in the Reject table. Do I have that right?
      Let me know if I have the right picture of your model so far, and if not, let me know what I am not understanding..

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

        great questions, and what is the shape you want to report this in?

        • Ben Lezin

          Trying to summarize the reject balance associated with each distinct reject reason for starters. And then I should be able to slice for everything in the context of the rejects with subtotals for reject reasons.
          Is that what you mean by “shape to report in”?
          Thanks,
          Ben

      • Ben Lezin

        Hi Thomas,
        Yes, you’re spot on regarding the data model and relationships. The reject volume is just a balance associated with each reject record in the reject table.
        Since the tables are related by rejectID, it seems like it’s not necessary to use the relatedtable function.
        Really appreciate any ideas since I’m stumped and the model works for all of my other calculations thus far. Stumbled upon the following article to help better understand the nuances of DAX filtering.

        http://mdxdax.blogspot.com/2011/03/logic-behind-magic-of-dax-cross-table.html

        Thanks,
        Ben

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

          Ben can you share a simple example workbook?

          • Ben Lezin

            Will do first thing tomorrow.

          • Ben Lezin

            Hello Kasper,
            I have a sample workbook with the model if you’re still interested in taking a look to solve the calculation of total rejects per reject reason, i.e. exception reason in the model.
            What is the best way to forward it to you or Thomas?
            Thanks,
            Ben

          • Ben Lezin

            Hello Kasper,
            I have a sample workbook with the model if you’re still interested in taking a look to solve the calculation of total rejects per reject reason, i.e. exception reason in the model.
            What is the best way to forward it to you or Thomas?
            Thanks,
            Ben

          • Thomas

            Ben,
            Do you have a DropBox folder (or something similar for file sharing)?

          • Ben Lezin

            Here’s the link to the PowerPivot sample on my OneDrive. The original model was created in SSDT tabular, but I created a sample Excel PowerPivot worksheet using the same relationships and calculations explained in the post. The pivot table hopefully demonstrates the problem where the exception reason isn’t filtering the reject amount.

            http://1drv.ms/1NlYCh6

            Thanks so much in advance!
            Ben

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

            well the answer is that we introduced a new relationship type in the Power BI designer that will take care of this: https://cwebbbi.wordpress.com/2015/01/01/bidirectional-relationships-and-many-to-many-in-the-power-bi-designer/

      • Thomas

        Ben,
        Sorry, my goof, I needed to expand the image to see the Reject table and that RejectVolume is a member of that table.
        My first pass at a solution is that you are dealing with a many-to-many relationship. The Reject table can have many Exceptions and a given value for ExceptionType can appear in many different Exception records (for different Reject records).
        The solution that I have is to create a lookup table for exception types, which consists of ExceptionTypeID and ExceptionType. In the Exception table, replace ExceptionType with the appropriate ExceptionID. Relate the ExceptionTypes, lets say the new table is ExceptionTypes and consists of

        • Ben Lezin

          Thanks, Thomas. I was trying to avoid a many-to-many by using the rejectID as a foreign key. What would you recommend as the bridge table in order to accomplish the calculation for total rejects by exception description?
          Thanks,
          Ben

        • Ben Lezin

          Thanks, Thomas. I was trying to avoid a many-to-many by using the rejectID as a foreign key. What would you recommend as the bridge table in order to accomplish the calculation for total rejects by exception description?
          Thanks,
          Ben

          • Thomas

            Ben,

            I posted the following earlier today, but I do not see it elsewhere on the blog.

            I think you are trying to make a calculation using Many-to-One logic when the relationship is really Many-to-Many (Exception is the bridge table) and your model structure does not currently support the Many-to-Many relationship.

            The solution that I currently see is:

            Create a table (EMDescriptions) for each distinct EMDescription, and include a column like EMDescriptionID.

            In Exception, replace the EMDescription column with EMDescriptionID so that you can successfully link EMDescriptions[EMDescriptionID] with Exception[EMDescriptionID].

            Now create a measure using bridge table logic:

            Total Reject Volume:=CALCULATE ( Reject[RejectVolume], Exception )

            Create a pivot table and drop EMDescriptions[EMDescription] on Rows and [Total Reject Volume] in values. You should get correct totals for each EMDescription. You may want to turn off grand total (grand totals for Many-to-Many relationships are not additive).

          • Ben

            Thanks, I followed those steps and still get the total volume for each exception type. I created a separate table with the distinct exceptions linked to the exception fact table and added the following measures with the same result:

            =CALCULATE (Reject[RejectVolume], ExceptionMaster)

            =Calculate(SUM(Reject[ARAccountBalance]),SUMMARIZE(‘ExceptionMaster’,ExceptionMaster[EMExceptionID]))

            Any ideas?
            Thanks,
            Ben

          • Thomas

            Ben,
            Hmmm…
            Let me think then some more…

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

            its pretty hard to debug this without an example. Could you share one with us?

          • Ben

            Hi Kasper,

            Thomas helped me solve the problem by adding a dimension table for the exception (reject) reasons joined to the exception fact table and then adding the following calculation:

            Total Reject Volume:=CALCULATE (Reject[RejectVolume], Exception)

            Please note that previously the same calculation returned the grand total reject volume for each reason. But somehow adding the exception reason dimension and referencing the exception fact now allows me to get subtotals for the exception reason. Not sure I understand why since the measure doesn’t reference the dimension. Also, the grand total is slightly understated versus the actual reject volume (.001%), but pretty sure that it’s due to discrepancies in the fact table versus the dimension.
            Anyway, really appreciate the effort by Thomas to assist me. Hopefully, the inner workings of DAX will become more intuitive to me as time goes on.
            Thanks,
            Ben

          • Ben

            Wondering if I need an additional table with the unique RejectID/ExceptionType combinations? Or should the ExceptionMaster table suffice?
            Thanks,
            Ben

          • Ben

            Way to go, Thomas! Got it to work by using the fact table for the filter instead of the exception dimension – as you suggested above. Not sure why the measure suddenly works when I added the bridge table, even though the bridge table isn’t referenced in the measure. Is it because I created the measure in the context of the bridge table, i.e. in the bridge table tab in SSDT? Sounds like there are some inner workings under the hood that I obviously don’t understand.
            In any event, thank you for your time and helping me find a solution!

        • Ben

          The following measure is doing something, i.e. it’s itemizing the reasons with the balance from the other table, but seems to be overstating the amount. So not sure it’s grouping correctly. Seems like I need a summarize nested in there somewhere?

          =sumx(‘Exception’,RELATED(Reject[ARAccountBalance]))
          Ben

        • Ben

          Also, tried this but it just gives me the overall grand total for each reason, which is no improvement from using the sum measure from the table.

          :=SUMX(Distinct(Exception[EMDescription]),SUM(Reject[ARAccountBalance]))
          Ben

        • Ben

          And when I run the following DAX in SSMS, the numbers are way too high:

          EVALUATE

          SUMMARIZE(‘Exception’

          ,’Exception'[EMDescription]

          ,”Amount By Reason”,’reject'[RejectVolume])

          Holy cow, is it really this hard to do a group by in DAX???

          • Thomas

            Ben,The bridge table for the your many-to-many relationship will be the Exception table.

      • Ben

        Hi Thomas/Kasper,

        Since you were so helpful with the many-to-many solution, I thought you may have some thoughts regarding the issue I posted regarding ALLSELECTED with a date hierarchy for rows.

        For some reason the bug is only reproducible for me when I use a calendar hierarchy, i.e. Year, Quarter, Month, Date, for rows and then use a year slicer (either the year from the hierarchy or the regular year field). And then clear the filter in the slicer.

        I’ve submitted a bug with Microsoft but I’m not counting on an answer anytime soon.

        https://connect.microsoft.com/SQLServer/feedback/details/1206092/allselected-doesnt-work-when-clear-filter-from-a-slicer-is-selected

        Otherwise, the ALLSELECTED function works great for everything else, including other hierarchies.

        Thanks,

        Ben

        • Thomas

          Ben,

          If you had sample data that I could work with, I would be glad to evaluate the issue–may be come up with an idea or two (or after working with things for a while, I might also get frustrated and file a bug report with Microsoft also, but two documented bug reports will count more than one).
          I am familiar with another issue with slicers and data not refreshing, but that did not involve hierarchies.

          • Ben

            I can reproduce this with any hierarchy, so it’s not related to the date dimension.
            1. Create an ALLSELECTED function, i.e. =DIVIDE([ExceptionCount],Calculate([ExceptionCount],ALLSELECTED())).
            2. Add a hierarchy to the rows.
            3. Add a slicer with the top level of the hierarchy.
            4. Clear the filter of the slicer.
            RESULT: The measure value in the pivot doesn’t reset for the value selected in the slicer.

          • Kasper de Jonge

            try replacing the calculate statement with the same part in a measure, i suspect that will solve it. or wrap the part with another calculate.see this blog post for more details http://www.powerpivotblog.nl/where-your-calculated-field-is-executed-matters/

  • J. gM

    re: HolyMacro! Dashboarding and Reporting with Power Pivot and Excel – More questions.

    1. As directed on p. 79, I have created the new varPeriod table for the slicer – but after adding it to the data model, there is no way to create a relationship with the other tables. And there is now a yellow background message stating the “Relationships between tables may be needed”. There is simply nothing I can see that would permit to create a relationship between the three text values of varPeriod table with any other field in the tables of the data model.

    I cannot add the currently selected period from the slicer in the pivot table created for the varPeriod table.

    I also cannot create the calculated field LastFyMonthofYear defined in page 106. Even in its simplified to the extreme form:

    =[Date]

    the following error message is produced:

    Calculation error in measure ‘DateTable'[Calculated field 1]: The value for ‘Date’ cannot be determined. Either ‘Date’ doesn’t exist, or there is no current row for a column named ‘Date’.

    This occurs even thought typing =[ in the formula editor window opens the list of fields, including [Date].

    I am doing this in the Revenue worksheet.

    The calculated field can be created in the second Excel (2013) window – the data model for table DateTable. However this calculated field is not available in the list of fields of each table in the PivotTable Fields pane.

    So what am I doing wrong ?

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

      1: you are not supposed to create a relationship. It uses a measure to determine what is selected as is described in page 76/77
      2 LastFyMonthofYear is not a measure but a calculated column, hence you get the error message you see. A calculated column can reference the fields in its table without having to qualify it completely. You can find more details in page 25.

      Hope that helps.

      • J. gM

        Point 2 has definitely helped. Tks! Guess I was still confused between “Metrics” and calculated columns. Got it to work. (I am still annoyed at the yellow “Relationships between tables may be needed”.)

        Point 1 is still obscure for me. I am unable to get figure 4.51 pg. 79 to include the line 7 YTD under line 6 Q2, so I am not yet able to refer to cell A7 for the title of the spreadsheet.
        Thank you very much for your assistance.

        • J. gM

          OK, through trial and error I have managed to solve point 1 as well. The “trick” (?) was to select the cell holding CurrentFyMonth filter. Just checking ON the checkbox next to the Period field automatically brings it into the ROWS section, it is not even necessary to drag the field. The currently selected option in the slicer does correctly reflects in the table.

  • Chris

    Hello,

    I have two separate fact tables. These tables have mutual context but one of them has 2 dimension more.

    Table 1:

    Context: Productgroup, Ordertype, SalesID, Date and Item

    Measures: Turnover

    Table 2:

    Context: Productgroup, Ordertype

    Measures: ExpectedTurnoverPerSalesID

    I need a report which gives all data from table one combined with the ExpectedTurnoverPerSalesID. The solution is rather simple when I use an if to see whether Table 1 has rows then return ExpectedTurnoverPerSalesID. But the problem I have SalesID and Item are rather large dimensions and the measure seems to iterate over all SalesIDs and Items to see whether it should return ExpectedTurnoverPerSalesID. Is there a performance friendly solution for my problem?
    Chris

    • Thomas

      Chris,

      Let me know if I am getting the right picture or am way off base.
      Usually I can make a helpful comment if I can get a handle on the “big” picture as well as the detail of concern (performance).

      I am trying to walk through this one using my mind’s eye and experience with similar reports. Since both tables are fact (data) tables, there is probably not an explicit relationship between the two, only an implied relationship based on that they share dimensions (ProductGroup and OrderType).

      In my minds-eye, I see a pivot table, using Table 1 as its data table, and the pivot has row (and/or column) labels based on ProductGroup, OrderType, SalesID, Date, and Item. The gist of the pivot table is going to be comparing planned (“expected”) to actual turnover. The measure, Turnover, is dropped on the Values zone, and everything is OK. So far, no performance problem; performance problems only come when the measure ExpectedTurnoverPerSalesID is also dropped on the values zone.
      Can you describe the layout of the pivot (what is on rows/columns, what labels are nested inside others)?
      Can you give the formulas for Turnover and ExpectedTurnoverPerSalesID?
      It is noteworthy to me that ExpectedTurnover”PerSalesID” is associated with a table that does not have a SalesID context. If the measure does have a component based on SalesID, does it also have a component based on Item and/or Date?
      Without enclosing ExpectedTurnoverPerSalesID with an IF, what happens if you just drop it on the Values zone, below Turnover?

      • Chris

        Hey Thomas,
        My Pivot is indeed as in your minds-eye. Rows are Productgroup, Ordertype, SalesID, Date and Item. ExpectedTurnoverPerSalesID is the amount we, as a company, expect per SalesID for a given combination of a Productgroup and Ordertype. One SalesID could have different Items, Productgroups and Ordertypes. We want to compare the expected turnover with the realized. If I don’t enclose in an if the pivot gives for each combination of Item and SalesID the ExpectedTurnover. When I do enclose the calculation is done for each combination but it isn’t returned. Performance is poor for both. In my opinion context for ExpectedTurnover should be determined by table1 in stead of doing the calculation for all combinations.
        A possible solution I implemented now is enriching the datamodel of table 1 with the data from table2 but I encounter the problem in different situations.
        Chris

        • Thomas

          Hi Chris,
          Your idea regarding ExpectedTurnover determined by Table1 makes a lot of sense to me.
          Sometimes an items table contains a record for all parts (not just items that are sold to customers, but also for items like office supplies, materials consumed in production, etc.). If this is the case, consider when importing the items table into Power Pivot to only pull in items that are sold to customers–this could be accomplished if there is a SalesID column in the items table that could be checked for empty or maybe there is a column like “product class” in the items table that could be checked for a description like “finished goods”. In my case, I worked for a company that had tens-of-thousands of items in the items table, but only a hundred (or fewer) would be considered in turnover analysis.
          Let me know if this helps.
          Another question: In the pivot, are you using slicers for ProductGrooup, OrderType, SalesID, and Item? In a lot of crunching, sometimes slicers can impact performance. If you are using slicers, consider turning cross-checking off.(in the Slicer context tab, go to slicer settings and uncheck “Visually indicate items with no data”). Sometimes simply unchecking this box makes performance go from poor to good.

  • J. gM

    Ever since I added a PowerView to the Excel workbook built-up by following Mr. K. de Jong’s HolyMacro! book, Excel has become unstable.
    This occurs when trying to refresh the data model.
    “Excel is running into problems with the ‘microsoft office powerpivot for excel 2013”

    When clicking on the Refresh button of the data model window:
    . “We couldn’t refresh the connection. Please go to existing connections and verify they connect to the file or server.”

    When trying to open the existing connection and refreshing it:
    . “Failed to connect to the server. Reason: Unspecified error”
    Any suggestions ? I am running Excel 2013 32-bit version.

  • tchakounte

    i bought “Dashboarding and Reporting with Power Pivot and Excel: How to
    Design and Create a Financial Dashboard with PowerPivot” by Kasper de
    Jonge…..and I would like to practice from start to finish , but I do not
    see the data used in this book.
    how to possess it
    jeandaniel4@yahoo.fr

  • Tim Langford

    I have a test pivot table bringing together data from 2 excel files connected with a powerpivot relationship. Where there is no data (quantity) for a particular sales rep / region / size combination, it still shows the data from another zize combination (column) rather than showing a blank space. May be best shown by sending the files, they are not large, but not sure if I can add Attachments..

    • Thomas

      Tim,
      Sounds interesting and I think there is a good solution that can be worked through quickly.
      Like you, I am a visitor to Kasper’s “Ask your questions here” web page, but I often come to the site to help and learn. What some visitors do want to share a file is to use a resource like dropbox or Excel online. I could be corrected, but I think the choices here on site are to post comments and attach an optional image. Some site visitors with small data sets (which is probably not the case here) have listed their data at the bottom of their comments.
      If you would respond to this comment with a link to dropbox or Excel online that would be great. You could also email me (by replacing the following AT with an @):

      tallanATprodigy.net

      If emailed, I will respond to you with answers over this web page (or use a dropbox link also posted on this web page).

      • Tim Langford

        Thanks Thomas, I’ve discovered the answer I’m happy to say – I had for example Sales Rep in 2 tables and bought it into the Pivot table from the wrong source. Once I had the right ‘version’ of data, it worked perfectly

    • Thomas

      Tim,
      Sounds interesting and I think there is a good solution that can be worked through quickly.
      Like you, I am a visitor to Kasper’s “Ask your questions here” web page, but I often come to the site to help and learn. What some visitors do want to share a file is to use a resource like dropbox or Excel online. I could be corrected, but I think the choices here on site are to post comments and attach an optional image. Some site visitors with small data sets (which is probably not the case here) have listed their data at the bottom of their comments.
      If you would respond to this comment with a link to dropbox or Excel online that would be great. You could also email me (by replacing the following AT with an @):

      tallanATprodigy.net

      If emailed, I will respond to you with answers over this web page (or use a dropbox link also posted on this web page).

  • Ben

    Hello Thomas/Kasper,

    Any thoughts on how to calculate the closing balance for a column where each item, aka account, has a different last day? For example, I’m trying to aggregate the last value of the deferredincomecp column for all accounts for the month of February even though each account has a different last day. In other words, not all accounts on the attached image have a transaction on 3/31 so using that day for the closing balance would exclude accounts that had their last transaction earlier in the month.
    Thanks,
    Ben

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

        The problem with the closingbalance function is that it only aggregates accounts that have a record for the last day of month, quarter, year. Also, the lastdate function only aggregates records that have a transaction on the last date. I need to aggregate all closing balances for the last available record for each account. I tried this, but the OLAP query runs out of memory:

        :=SUMX( ALL( vwCPAccountRevenueRecognition[CPAccountID] ),
        CALCULATE( SUM( vwCPAccountRevenueRecognition[DeferredIncomeCP] ),
        LASTNONBLANK( DATESBETWEEN( vwCPAccountRevenueRecognition[RecognitionDate],
        BLANK(),
        LASTDATE( vwCPAccountRevenueRecognition[RecognitionDate] ) ),
        CALCULATE( COUNT( vwCPAccountRevenueRecognition[DeferredIncomeCP] ) ) ) ) )

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

          Can you give me a sample dataset? just a few lines. I think I understand but that would give me the opportunity to test it myself.

          • Ben

            Hi Kasper,

            The image I posted is a sample data set, where only a portion of the accounts (CPAccountID) have records on 3/2/2014, but I want the closing balance for the last day (or prior), so the accounts with records on 3/1/2014 will be excluded if use the following =Calculate(SUM([DeferredIncomeCP]),LASTDATE(DimDate[FullDate])). All of the DAX I’ve tried, including the CLOSINGBALANCE function will only aggregate records that have a transaction on the last day of the period. I need to aggregate the sum of the last value for each account on the last day of period in the context OR prior to the last day. It’s the ones that are in the prior bucket that are getting excluded.
            Thanks in advance for your help!

          • Ben

            This is the T-SQL to get the result that I need in DAX:

            SELECT SUM(deferredincomecp) as totaldeferredincome

            FROM

            (

            SELECT cpaccountid, Max(recognitiondate) maxrecdate

            FROM [CarePayment].[DWFin].[vwCPAccountRevenueRecognition]

            GROUP BY cpaccountid

            ) A

            JOIN [CarePayment].[DWFin].[vwCPAccountRevenueRecognition] B

            ON B.RecognitionDate = a.maxrecdate and a.cpaccountid = b.cpaccountid

            WHERE b.recognitiondate <= getdate()

          • Ben

            Hi Kasper,
            Could you please, at least, tell me whether you think this is possible with DAX, especially since there is a simple solution with T-SQL that I provided?
            Thanks,
            Ben

          • Thomas

            Ben,
            This is another interesting question, and I have an opinion. I think there are some things more easily done with SQL than with DAX, as well as there are some things more easily done with DAX than SQL. That you have the SQL code, I say “use it” and import the data into Power Pivot if you can use DAX to analyze the result set.
            – Thomas

          • Ben

            Thanks, Thomas. Always appreciate your feedback. Unfortunately, I would like to include account level granularity which would be easier with dax since it’s already in the dataset used by the model.
            Otherwise, the summary table generated by the t-sql wouldn’t be scalable since it requires a record for every account for every day, even if there isn’t a transaction. I suppose I could also create a slowly changing dimension for the account balance, but was hoping that there would be a dax solution.
            Perhaps there is a way to rank all preceding transactions by date outside of the context of the current row, and then sum all of the closing balances for the most recent transactions. That is essentially what I did via T-sql.
            Ben

          • Thomas

            Ben,

            Sometimes turning over some ideas can trigger a solution.

            Just thinking out loud here, and picking up where the last comment said, “Perhaps there is a way…”, let me put the following thought on the table:

            There is a way to rank all preceding transactions by date (or transaction ID) for each account outside of the context of the current row (using functions like ALL, TOPN and/or EARLIER), and then use the last closing balance for each account (and since DAX would be getting only the amount from the last transaction, using either SUM, MIN, MAX or AVERAGE would all yield the same intermediate result)”

            Let me know your thoughts, there is an answer.

          • Ben

            Right, that is what I’m attempting but the sum is still the same as the generic sum. For example, I created the following measure to get the max ID per account (which works):

            lastrevecid:=CALCULATE(MIN(vwCPAccountRevenueRecognition[RevRecognitionID]),ALL(vwCPAccountRevenueRecognition[CPAccountID]))

            And then I created the following to sum the amount for the above revrecognitionid (which references the above measure):

            sumofalllastrevrecamounts:=calculate(sum(vwCPAccountRevenueRecognition[DeferredIncomeCP]),filter(vwCPAccountRevenueRecognition,vwCPAccountRevenueRecognition[RevRecognitionID]=[lastrevrecid]))

            However, the latter is only returning the same as sum(vwCPAccountRevenueRecognition[DeferredIncomeCP]).
            So there’s obviously something I’m missing in the second calculation to aggregate all of the amounts related to the max id from the first measure.
            Thanks,
            Ben

          • Ben

            Thought this solution was my silver bullet, but I get the error “table of multiple values was supplied where a single value was expected” when I try to implement it with my data:

            https://javierguillen.wordpress.com/2012/06/09/lastnonempty-in-tabular-mode-part-2-last-ever-non-empty-calculations-in-dax/#comment-1563

            Nonetheless, it’s addressing the same dilemma so it’s an interesting read.

          • Ben

            Hi Thomas, the following post provides the solution, but, of course, it doesn’t completely satisfy my requirement – which is to sum the closing balance at every grain of the calendar hierarchy. It works if you filter for a unique year. Will try to tweak it accordingly.

            http://www.sqljason.com/2012/06/lastnonempty-in-tabular-mode-part-1.html?showComment=1430411663466#c1834655899115267161
            As always, thanks for your feedback!
            Ben

  • http://talentcube.bi Matthew Dewstowe

    I am looking for the equivalent of VLOOKUP. I have two datasets in the data model. The first contains the field job_title. Based on a list of job_titles in the other data set, I would like to run a lookup and provide the category of the look-up from the other table. It’s important also the lookup can run as a partial string match.

    So for example, if the data set contains job_title = senior java developer

    We use java developer from the other table as a match and then use the category, IT, from here.

    Help appreciated!

    • Thomas

      Hello Matthew,
      I think that the best practice here would be to clean up (make consistent) your data before importing into Power Pivot so that partial matches are not necessary.
      The idea of using relationships between tables should entirely replace the use of VLOOKUP.

      • http://talentcube.bi Matthew Dewstowe

        This is not going to work as the strings are too messy. Are there any other ways to do this or should I do this in SQL?

        • Thomas

          Matthew,
          There are almost always “ways”, but that the strings are “too messy” is all the more reason to clean them up. That’s the best practice.

    • FerroMinera Argentina

      I think the RELATED function is what you need.

  • Ward

    I have the situation where I have multiple PowerPivot workbooks in SharePoint 2013. The workbooks are refreshed daily through unattended data refresh in SharePoint. Some of the workbooks have a report filter. That filter is set to a certain value so that the correct selection of data is presented to the users.

    It happens quite often that the report filter loses its selection, causing the workbook to show all data
    instead of just a selection of it. I can’t figure out why this keeps happening, can you help me?

    Thanks in advance.

  • Segal

    Hello,

    I have a situation in my excel/powerpivot dashboard in which I want to calculate value for a revenue funnel for: Same Month Last Year. To do so the ParallelPeriod function works perfect for me, however, the results only show when I have marked this year as well as last year in my Year slicer.

    eg: To calculate value for the dashboard for April, I need to do the comparison for the same period last year as well. April 2015 vs April 2014.

    SAME MONTH Last Year:=CALCULATE(sum([Rev. wgt (k$)]),PARALLELPERIOD(DimDate[Date],-12,MONTH))

    and then I compare with this month:

    This Month vs Same Month LY:=((sum([Rev. wgt (k$)])- ([SAME MONTH Last Year])) / [SAME MONTH Last Year])

    To Calculate April 2014, I use the ParallelPeriod function. But to have the value in the dashboard, I have no other option but to select multiple year combination in the year slicer to get results. (See image, pls)

    I want to avoid using the Year slicer and want that slicer only to have current year (2015) selected for result. I do not want 2014 to be marked as it changes some of my other views which are yearly focused.

    Therefore, is their any way I can use only months or any other formula to show the result for “Same Month Last Year” without marking the previous year (2014) in the year slicer? I want avoid using the target year slicer to return value.

    Any suggestions, guidance would be highly appreciated. Many thanks in advance.

  • kthejoker

    This is PowerView related:

    I have an SSAS Tabular model I’m importing in to use as a source for a Power View report.

    It contains a location dimension which includes Longitude and Latitude as text fields.

    When I put a Map chart in and use these fields, it translates them as measures, which as you know for text fields the default aggregation is Count, which for each row is naturally 1. So it sticks all the locations at lat long 1,1 (off the coast of Nigeria.)

    By setting them to decimal data types in the model, it then uses an Average aggregation rather than a Count and produces the correct map.

    Even if I set the summarize method to “Do Not Summarize” it still preforms the Average aggregation.

    Soooo this seems like a bug. If I put text fields in to latitude, longitude fields in a map chart, it shouldn’t summarize at all. And if it absolutely has to summarize, it should treat the fields as, you know, actual longitude and latitude and perform an average aggregation.

    Thoughts?

    • Birgit

      I have the same problem.. But when I change the columnname of Longitude to X and Latitude to Y it doesn’t give the average and it looks like it’s working then.. Only I don’t know what kind of colomn I should drag into location.. Do you have an idea?

      • kthejoker

        Any column which uniquely identifies the “dots” you’re placing with lat long (StoreID, for example, or AddressID, etc); as I said, mine calculates as averages so if you use a grouping column like Region for a set of stores, then it simply shows the average position of the stores within that region as a single point.

  • disqus_G5Mdr39qUt

    Can you please do this without calculating values over weekends and holidays?

    http://www.powerpivotblog.nl/get-values-within-a-start-and-end-date-using-powerpivot-dax/

  • Arnoux Olivier

    Good day
    I have been trying to write queries in powerpivot, but each time I want to refresh it prompts me to enter a password before I can refresh. “Re-enter password The password is missing or invalid”. And when other users wants to refresh it also gives an error even when they enter the right one.
    Also when writing the query I tick the box that says allow saving password. But each time I go in again after saving, it is as if I didn’t change anything. It jumps back to default
    How do I get past this.
    I want it to not prompt for passwords, and let any user refresh this

  • David Greenberg

    Has SSAS Multidimensional cubes been added as a native data source for Power View using Power BI for Office 365 and the Data Management Gateway?

  • Josh

    For the life of me can’t figure this one out, any help would be greatly appreciated! Need a measure that will count how many cities exist in each state for each month, provided the population isn’t zero.

    • Josh

      Someone found a solution for me.
      CityCount:=CALCULATE(COUNTROWS(DISTINCT(Table1[City])),FILTER(ALL(Table1[City]),Table1[City]=Table1[City]))

      StateCityCount:=calculate(SUMX(DISTINCT(Table1[State]),[CityCount]),Table1[population]>100)

      This works but I have multiple other tables and they all link back to a parent table called ‘StateCityUnique’.

      When I use my City column from the ‘StateCityUnique’ table in my PivotTable my StateCityCount measure no longer works. Any ideas what to do?

      • Josh

        Can someone PLEASEEEEEEEEEEEE help!!!

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

          I am not sure I understand the city count. What do you want the result to be? The count of all the city’s that are in the table?

          • Josh

            Hi Kasper, thanks so much for taking a look. I would like the results to be the count of all cities for that particular state. All cities in the same state should share the same results. In my example I listed three cities in California: Los Angeles, San Francisco, and San Luis Obispo. Therefore in a particular month, as long as the population isn’t zero for any of the cities, the results should be 3 for each city. If one city has a population of zero then the result would be 2 for each city.

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

            could you include a XLSX file that contains a sample?

          • Josh

            Not sure of the best way to do that. Will this work?

            https://www.dropbox.com/s/pu1q9pfz72dmtr5/City%20Example.xlsx?dl=0

  • Josh

    Have another question which hopefully is much more simple then my last. How do I hide rows that don’t meet a certain criteria?
    My raw table has three columns. Date, Status, and Name.
    On my Pivot table I would like to see the Status on the columns and the Names listed on the rows. A simple lastdate(Date) is the measure getting calculated.
    I don’t want to show rows where the Completed Date (Completed is one of the Status) is more then 90 days old.
    Thanks, Josh

  • Andy

    Hi, I am trying to display the Top N using a disconnected table and slicer, and using the expression: IF (

    HASONEVALUE ( ‘Top'[Top] ), IF ( RANKX ( ALL (Register[SHE]), [Sum Existing Risk]

    ) <= VALUES ( 'Top'[Top] ), [Sum Existing Risk], BLANK() )).

    However when I select '5' it returns the top 5, but when I select 10 or 15 or 20 it returns 29 results, 30 = 34 results and 40 or 50 = 55 results. Within the Sum Existing Risk there many values of the same value. Request your advice please.

    Regards

    Andy

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

      hi Andy, any chance there are ties in your rank? also instead of values I would use MAX that will help when someone selects multiple slicer values.

      hi Andy, any chance there are ties in your rank? also instead of values I would use MAX that will help when someone selects multiple slicer values.

  • Freddy Angarita

    I’m watching your https://channel9.msdn.com/Events/TechEd/NorthAmerica/2014/DBI-B312#fbid= … video and it is great, is the workbook used on the demo is available for download? Thanks!

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

      unfortunately this dataset is not really in sharable state :(

  • Mike Moore

    Is there an Excel downloadable dataset for use with ‘Dashboarding and Reporting with Power Pivto and Excel’ ?

  • Alyce Solomon

    Hi, I would like a DAX that updates sums of the last completed 6 months, at month level

    • FerroMinera Argentina

      How do you determine that a month is completed? Do you have a table with completed months? If so, I would use the MAX function to find out what is the last completed month and with the DATEADD function work my way back 6 months.

      • Alyce Solomon

        Hello, I am very very new to power pivots
        I have a Dim_Date table related to my dataset which also has dates, but battling to get the calc correct so that I can select the last 6 months, at month level, so that it can update monthly / dynamically

        • Thomas

          Hello Alyce,

          Assuming your pivot has only year-months on rows (or columns), consider a CALCULATE formula that has your measure and then, in the filters section use DATESINPERIOD(Dim_Date[Date], LASTDATE ( Dim_Date[Date] ), -6, MONTH ) ).
          This concept is based on a formula in the book, “DAX Patterns 2015”, pp. 35-36, by Alberto Ferrari and Marco Russo.

          So, the entire formula together would look something like this:

          YourNewMeasureName :=
          CALCULATE (
          [YourMeasureToPerformCalculateOnGoesHere],
          DATESINPERIOD (
          Dim_Date[Date],
          LASTDATE ( Dim_Date[Date] ),
          -6,
          MONTH
          )
          )

      • Alyce Solomon
  • Michael

    Hi Kasper,
    I was importing an excel powerpivot model into SSAS tabular model and was experiencing that SSAS is acting differently than the excel powerpivot. When using a column with a number data type without formatting, i can use it as a measure within the excel variant. After importing the model in SSAS i cannot use it as a measure unless i specify the formating of the column in the excel model before importing it. But it then shows up as a measure in the measure folder of the particular table and is also availabel as a dimensional attribute.
    So am i right that there are some differences in behavior between the two or am i doing something wrong?

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

      Are you importing the data or are you Importing the whole model to create a new project?

      • Michael

        I’m importing the whole model. Did it once through visual studio, with new project – import from powerpivot, and once through management studio, with restore from powerpivot. Both with the same result

  • FerroMinera Argentina

    I have the following dataset and need to obtain subtotals by cost center for every subset of consecutive days. Every time there is a day with no hours, the subtotal is reset and each subtotal is assigned a consecutive number, so I can later identify them. I think that the answer is by creating a few calculated columns but I cannot get it to work. I included the expected results. Please provide some guidance to get me in the right direction. Thank you.

    • Thomas

      Hello FerroMinera
      You might consider a solution with a couple calculated columns: The first column would be either the first non-blank calendar date for hours logged to the current row’s cost center prior to the current rows date or (when such a prior date does not exist) the current rows date. The second column counts the values of unique dates in the first calculated column less than the date in the current row’s previously calculated column (and then adds 1). Then create a pivot with Cost Center and second calculated column on rows, and the sum of hours in the values drop zone…

      • FerroMinera Argentina

        Thomas,

        Thanks for your response.

        I agree that the I will need a few calculated columns to set up the information. However, I think that the solution you provided does not allow me to tell apart different wells but rather adds up all the wells prior to the current one (“The second column counts the values of unique dates in the first calculated column less than the date in the current row’s previously calculated column (and then adds 1”). Do you see my point? Can you think of another solution that will have incremental project order by each particular cost center?

        Thanks again.

  • marc

    I’m just starting out using Power BI Designer and wondering if Designer will have the same capabilities as Power Pivot?
    The presentation tool will be Power BI, so like to know which tool is better suited PowerPivot or BI Designer.

    Also, anyone can advise me which book is better suited for beginner.
    1. DAX Formulas for PowerPivot
    2. Power Pivot Alchemy
    Many thanks

    • Thomas

      Hello Marc,
      DAX Formulas for PowerPivot is better suited for a beginner, its only assumes that you are an Excel professional (work with Excel regularly) and builds from there. Power Pivot Alchemy assumes that you have some experience with Power Pivot.
      Both are excellent books.

      • marc

        Hi Thomas,
        Many thanks for your advice.

  • SAIFE

    Hello, I am trying to obtain promotional expense_per unit from the table 1 (blue color) looking through the date_from,date_to,product_code of the table1 against the trans_date and product_code of table 2 (green). Please help .

    Saifee

    • Thomas

      Hello Saife,
      It sounds like you have just about figured it on your own.
      What formulas have you tried so far that did not work?
      Also, what is your comfort level with the CALCULATE and FILTER functions?

      • SAIFE

        Thanks Thomas for contacting, i guess i have found the solution =calculate(average(table1[promotional_expense_rate]),filter(table2,table1[DATE_FROM]=table2[TRANS_DATE]&&table1[PRODUCT_CODE]=table2[PRODUCT_CODE]))

        • Thomas

          Cool!

  • BI_User

    Probably a newbie question, but when I “Browse”
    my tabular model in SSMS, and I pull over fields from 2 or more tables I always
    get a Cartesian Product, even if there are PK to FK relationships set up
    between the two tables.

    For example I have a Claims table and a Patient
    table. I can pull in ClaimID and Date of Service from Claims table and
    add a filter for a particular date and get just those claims.

    But if I try to pull in the Last Name of a client from
    the Patient table, I get every claim returned for an many patients there
    are. If I have Claim ID’s 1 thru 10, and 5 Patient ID’s 1 thru 5, and
    only Patient ID 1 is associated with Claim ID 5 …it should only give me that
    one claim.

    Instead I get all 10 claims with Patient ID #1, then 10
    claims with Patient ID #2, etc …for 50 claims.

    Same thing when connecting to my Tabular model via
    PowerPivot.

    What gives?

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

      This is default behavior for Excel and the cube browser, you need to use a measure to reduce the rows.

  • marc066

    Hi All,

    Is it possible to calculate rows based on the table below. For example, P10 Review less P11 Review(shipment), P10 Review less P12 Review(shipment)

    Sum of Shipment Volume
    Date Index DP Review Total
    1/7/2014 1 P10 Review 11969.53
    2 P11 Review 19151.28
    3 P12 Review 14937.96
    4 P13 Review 13568.63
    1/8/2014 1 P10 Review 23362.98
    2 P11 Review 37380.82
    3 P12 Review 29157.02
    4 P13 Review 26484.17
    1/9/2014 1 P10 Review 121474.26
    2 P11 Review 194358.88
    3 P12 Review 151600
    4 P13 Review 137703.06
    Grand Total 781148.59

    Many thanks,
    Marc Lim

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

      Hi Marc, its pretty hard to read the table, maybe you can add a screenshot?

      • marc066

        Hi Kasper,
        Many thanks for your reply. I’ve gotten my answers from tomallan from another forum,
        Kind Regards.

  • Barry

    For a self service BI demo in Excel 2013 I’m working on I’d like to use the Timeline Slicer. The dashboard consists of an asymmetric financial table that needs to display current & prior period results. How might this be achieved?

  • Josh

    Using the Table Import Wizard is it possible to filter the dates to only bring in the most 30 days worth of data? Was thinking something like Field Name is more than date()-30 but that doesn’t work. When I tried writing the SQL code it says that ‘Date’ is not a recognized built-in function name. Any suggestions? Thanks!!!

    SELECT
    WttrendImg.SampleDate
    ,WttrendImg.WeluWintId
    FROM
    WttrendImg
    WHERE
    WttrendImg.SampleDate = Date() -30

    • Thomas

      Hello Josh,

      If you are using a recent version of SQL Server, the following code, if used to replace your WHERE clause, would work:

      where WttrendImg.SampleDate >= dateadd(dd, -30, cast( getdate() as date) )

      • Josh

        I’m using excel 2010 PowerPivot Version 11.0.3000.0 and your code worked like a champ!!! Man I love smart people. Thank you so much Thomas!

  • Christiane Pereira

    Hello,

    I have some indicators I need to update every month in a report. The problem is that I have to change my report every time a new month is included in the database. The indicators I need to show in the report are related only to the last month updated, it’s not year to date. So, I have to create formulas for each month and change the information every month in the report columns, which is not practical.

    I am using the following formula for each month (in this case May) to inform the Gross Revenue:

    May – Total Gross Revenue:=CALCULATE(SUM(INCOME_STATEMENT_2015[Gross
    Revenue])+SUM(‘Managerial Adjusts'[Gross
    Revenue]);DRE_2015[COMPMONTH]=”05 – May”)

    I think it would be better if there was any formula that understands I need the information of the last month added in the database. In this example, when I insert in the database “06 – June”.

    I don’t know if I am being clear…
    Does Anybody know any DAX formula that can help me?

    Thank you in advance!

    • Richa Gupta

      Not sure how to share excel here but I did an sample for this scenario and looks like its totally possible. I will try to give you steps I did if you can recreate them in your environment that would be great.

      1. I used AdventureWorksDW2012 tables , DimDate and FactInternetSales.

      2. Imported data using this query and named the table “SalesData” . Consider this your table which has amount field.

      select FIS.SalesAmount,Month(FIS.OrderDate) as OrderDateMonth,Year(FIS.OrderDate) as OrderDateYear,Cast(Month(FIS.OrderDate) as varchar)+ ” +cast( Year(FIS.OrderDate) as varchar) as OrderDateMonthYear from FactInternetSales as FIS

      3. Imported data using this query from DimDate and named the table “LastClosedPeriod”. Consider this your table which has closed months information. I have a where clause just to make it quick, you dont have to put where clause.

      SELECT distinct Month(FullDateAlternateKey) as ClosedMonth, Year(FullDateAlternateKey) as ClosedYear
      FROM [AdventureWorksDW2012].[dbo].[DimDate]
      Where Year((FullDateAlternateKey))=2007

      4. Created these 3 measures in LastClosedPeriod –

      MaxMonth:=max(LastClosedPeriod[ClosedMonth])
      MaxYear:=max(LastClosedPeriod[ClosedYear])
      LastClosedPeriod:=CONCATENATE([MaxMonth],[MaxYear])

      5. Created Total Sales Amount measure in SalesData

      Total Sales Amount:=CALCULATE(SUM(SalesData[SalesAmount]),FILTER(All(SalesData),SalesData[OrderDateMonthYear]=[LastClosedPeriod]))

      It gives me correct data. So now everytime there is a new month in that table and you refersh the excel it will set the measures as last closed month details and all calculation will work according to last closed month. Let me know if this helps. There might be other ways to do it but I was able to do it using this method.

      Thanks,
      Richa

  • Arun Sreenivas

    Hello Kasper,

    I would like to know if there are any formula in DAX to implement Holt Winter’s Multiplicative approach for forecasting.

  • Thomas Woods

    Hi,
    I am trying to work out correct investment returns on some of my shares using XIRR function.
    I would like to list the shares in a column and drag the formula down next to them to show the returns.
    I have a table of the shares, transaction dates and amounts… How do I do this?
    I can do it with a pivot and looking up using MATCH and OFFSET formulas but this is cumbersome.
    The problem is that XIRR requires two ranges of data that flex depending on number of transactions.

  • Robin A

    Hi

    I would be thankfull if anyone could help mig with a problem. I want to aggregate distinct Days by month in a timeperiod. my material contains ID, activestart, activeend and coststart, costend. There are serveral rows with same ID, some times with same period some times with different period. I need to now distinct Days for each period. so that I can wiev them in a month table.

  • Maria

    Hi Jasper, I hear my company is going to use Oracle’s ENDECA pretty soon. Is ENDECA seen as a tool that can do what Excel and Power Pivot does? Thank you!

  • Matt

    I am new to PowerBI. I am looking to calculate the monthly holidays by staff member using DAX. I am
    able to calculate it if all entries (Start Date & End Date) are confined to a unique month. The issue is where a persons holidays go across several months

    I have 2 tables

    Table 1

    Name StartDate EndDate
    Joe Bloggs 27July 2015 7th Aug 2015
    Jenny Jones 22nd July 2015 23rd July 2015
    Jenny Jones 27th July 2015 28th July 2015

    Table 2

    Month/Year
    June 2015
    July 2015
    August 2015
    September 2015

    I am looking for two types of Output (high level and detailed)

    Detailed

    Month/Year Name Total Days OOO
    July 2015 Joe Bloggs 5
    July 2015 Jenny Jones 4
    August 2015 Joe Bloggs 5

    High level

    Month/Year Days OOO
    June 2015 0
    July 2015 9
    August 2015 5

    Any help would be appreciated.

    Thanks

    Matt

    • Thomas

      Matt, Power Pivot is more about making summaries (aggregations) from details and looks like your input is a summary (a date range expressed as “start” and “end” date) which cannot be aggregated into the results you need.

  • Maria

    Hi Kasper, repeating my question because I think you would know, being that you are so involved in BI and new tools. Here goes again: “Hi Jasper, I hear my company is going to use Oracle’s ENDECA pretty soon. Is ENDECA seen as a tool that can do what Excel and Power Pivot does? Thank you!”

  • John

    Hi Kasper,

    I am trying to create a calculated column in a single table Power Pivot file using a conditional IF expression.

    My first column only contains random recurring values of 5,9 and 0. My goal for the new calculated column is to replace the instances of 0 with the last prior rows instance of either a 5 or a 9 (see visual below).

    Column1data / Column2 New Calculated Column Desired Result

    9 / 9
    0 / 9
    0 / 9
    5 / 5
    9 / 9
    0 / 9
    0 / 9
    5 / 5
    0 / 5
    0 / 5
    5 / 5

    Any suggestions on a calculated column formula on how to accomplish this seemingly simple task?

    I assume I may have to use an index column, or look up function? My first draft of the calculated column is:

    =if([Column1data]=5,5,IF([Column1data]=9,9,?previous non zero value in [Column1data]?))

  • Michael Deneys

    Hello,

    I am struggling to display a Count of projects in Construction by Contractor & Week Start. I have the following data: –

    date tbl – Date, WeekNum, WeekStart, WeekEnd, Month, MonthNum, Year, Quarter, Day of Week
    Contractor tbl – Code, Company, OrderBy
    Working tbl – WBS, Activity, Start, Finish, Contractor Code, CP=where Activity=Construction Period, HO=ActivityCP

    Filter by CP=1
    Contractor| Week start 1|Week Start 2| Week Start 3| Week Start 4| Week Start 5| etc…

    MC1………………..0…………………1……………….1………………0……………………0……………….

    MC2………………..1…………………2……………….2………………1……………………1………………..

    MC3………………..0…………………0………………..1……………..1……………………1……………….

    MC4………………..1………………….0……………….1……………..1……………………0………………

    Totals………………2………………….3………………5………………3…………………….2…………….

    So MC1 has no projects in construction Week1, but 1 starts in Week2 and ends before week3.
    MC2 has 1 project in Construction Week1 running for 3 weeks, a 2nd starts in Week2 for 4 weeks.
    MC3 has 1 project starting in Week3 running for 3 weeks.
    MC4 has 2 projects running 3 weeks, then another for 2 weeks, then 2 for 3 weeks.

    I can do this in Excel with the formula: –
    =SUMIFS(CP,Activity_Name,”Construction Period”, ContractorALL,,Finish,”>=”WeekStart,Start,”<="WeekEnd)

    I have tried the Calculate with filter & DateBetween but no joy. Please help. Thank you.

  • Jesper Bork

    Hi Kasper,
    I have been wondering for some time why PowerPivot seems not to be able to filter correctly on bigint values. I have to tables with a relationship btw. bigint keys and when I copy a key value from my fact table and pastes that value into an “is equal to” filter in my dimension table, it filters everything away, although I can manually find the records. My bigint key columns are of type “Whole number” with format “General”. Is this a well-known limitation or am I missing something ?

    Jesper

  • Ben

    Hello,
    I created partitions on one of my tabular tables (segmented by quarters) in order to optimize the daily processing but using a ‘full process’ on the database arbitrarily stops before processing each partition for the table.
    Any thoughts as to why would that would happen?

  • Umesh Reddy

    Hello Kasper. I am new to Power BI desktop and learning DAX at the same time. I have the following scenario I am trying to solve for:

    I have created a fact table called “Data” with the following columns Sales $, Sales $ LY, Sales Units, and Sales Untis LY along with Calculated measures Sales $ %Change, Sales $ Share, Sales Units %Change, and Sales Unit Share.
    I have a stand alone table called “MeasureType” with two values “Dollars” and “Units” and have created a slicer using this table.

    In a report when I select “Dollars” in the slicer I want [Sales $], [Sales $ %Change], and [Sales $ Share] to show up in the results and when I select “Units” in the slicer then I want [Sales Units], [Sales Units %Change], and [Sales Units Share] to show up in the results.
    In the slicer I want to limit the selection to either “Dollars” or “Units” and not both at the same time.

    Is there a solution to make this happen using DAX functions?

  • Maria

    Repeating my previous question to Kasper – or to anybody who knows about ENDECA and how it compares to Power Pivot, if at all:

    “I have been following powerpivot talks and tutorials for a few months and happily built myself a few data models that provide great monthly stuff, literally with a couple of clicks to update my data sources. I got encouraged and tried my luck with a cumbersome management report: The result I got was brutally awesome and I was delirious with my achievement but management sees it as ‘yet another tool to introduce’ to the busy managers (though they would NOT have to build it, only to use it). Oh well, change, who moved my cheese, and all that. Still, one moves on, right?

    Now it looks like management is going to be using ENDECA. I have been trying to get a sense of what ENDECA provides to the end user, but am not getting a good enough picture from the little that is available to outsiders. Here is my key question to you:

    Do you see ENDECA as a substitute for powerpivot? Is it able to work with humongous data sources and bring them in to update a data model as powerfully as PowerPivot does? Is it comparable?

    I do not anybody else to ask who would actually ‘get’ my question. Would you please throw some light, so I know what to expect?

    Regardless, thank you so much for having shown me such a powerful tool. It made my year and took my mind and my excel abilities to the next level.

    Maria”