I have decided to close the “ask a questions page” as I couldn’t get around answering all the questions in a timely manner. Besides Power BI now has a great active community where much more folks can help you answer you questions, please find it here: http://community.powerbi.com/.

The archive of old questions, everything before 2014 can be found here: http://www.kasperonbi.com/Askyourquestionsarchive.html

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

BR,
Mikko

1. Kasper de Jonge says:

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

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

1. Kasper de Jonge says:

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

3. Dear Kasper,

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

1. Kasper de Jonge says:

Hi Mikko,

Kasper

4. 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 “optimization” table is also downloaded via sql from our MRP system

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?

1. Kasper de Jonge says:

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.

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

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

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

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

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

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

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

12. Kasper de Jonge says:

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

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

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

Hope that helps
Kasper

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

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

1. Kasper de Jonge says:

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

Thanks,
Kasper

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

1. Kasper de Jonge says:

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

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

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

1. Kasper de Jonge says:

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

Would that work?

Thanks,
Kasper

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

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

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

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

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

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

1. Kasper de Jonge says:

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

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

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

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

1. Kasper de Jonge says:

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?

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

Petr

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

1. Kasper de Jonge says:

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

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

1. Kasper de Jonge says:

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

Thanks,
Kasper

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

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

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

1. Kasper de Jonge says:

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

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

1. Kasper de Jonge says:

Hi Andrew,

Thanks,
Kasper

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

1. Kasper de Jonge says:

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

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

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

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

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

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

1. Kasper de Jonge says:

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

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

1. Kasper de Jonge says:

Thx Chris for helping out!

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

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

1. Kasper de Jonge says:

Hi Andy,

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

Kasper

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

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

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

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

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

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

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

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

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

1. Kasper de Jonge says:

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

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

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

1. Kasper de Jonge says:

Hi Philip,

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

Thx,
kasper

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

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

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

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

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

1. Kasper de Jonge says:

Hi Tak,

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

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

59. That would be wonderful…thanks Kasper! Regards, Tak

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

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

1. Kasper de Jonge says:

I think these are the same with a different title.

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

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

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

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.

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

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

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

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

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

1. Kasper de Jonge says:

I’ll ask the publisher what he thinks.

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

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

1. Kasper de Jonge says:

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

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

1. Kasper de Jonge says:

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

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

1. Kasper de Jonge says:

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

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

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

– Asit

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

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

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

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

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

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

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

Regards

Andy

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

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

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

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

1. Kasper de Jonge says:

it does, I just need to approve as well 🙂

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

1. Kasper de Jonge says:

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.

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

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

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

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

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

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

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

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

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

1. Kasper de Jonge says:

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

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

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

1. Kasper de Jonge says:

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

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

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

1. Kasper de Jonge says:

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.

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

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

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

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

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

106. 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 fiscalfinancial year – it is an academic period.

Any help would be greatly appreciated!

Thank you 🙂

1. Kasper de Jonge says:

What happens when you use 29/02 as parameter?

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

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

Thanks,
Ela

109. 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?
Jess

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

1. Kasper de Jonge says:

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

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

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

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

1. Kasper de Jonge says:

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.

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

1. Kasper de Jonge says:

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

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

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

117. You might try this example for using PowerQuery Append

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

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

1. Kasper de Jonge says:
120. 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’.
Thank you
-Sean

1. Kasper de Jonge says:

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

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

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

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

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

1. Kasper de Jonge says:

yep, Thomas solution should work.

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

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

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.

1. Kasper de Jonge says:

have you tried swapping out sameperiodlastyear with datedadd ?

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

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

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

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

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

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

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

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

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

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

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

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

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

Trying some escape slashes…

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

1. Kasper de Jonge says:

ill see what I can do..

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

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

1. kjonge says:

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.

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

143. Sounds possible.

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

Thomas

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

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

146. Chris,

Great comment on the single quotes!

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

Thomas

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

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

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

Regards
Deepak

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

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

1. kjonge says:

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

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

1. kjonge says:

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

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

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

1. kjonge says:

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

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

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

Any insight would be great

Thanks

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

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

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

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

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

160. Chris,

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

Have you ever thought about teaching this stuff?

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

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

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

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

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

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

1. Jens says:

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

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

1. rwestly says:

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

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

Thanks,
Kasper

3. Jens says:

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

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

5. Jens says:

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.

6. Amdi Silword says:

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

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

8. Amdi Silword says:

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

9. Jens says:

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.

Jens Ole

10. Kasper de Jonge says:

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

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

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

166. JohnL says:

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

167. Ben Lezin says:

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

1. Chris Gilbert says:

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.

1. Ben Lezin says:

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.

168. amit says:

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

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

169. Joelle Lamarche says:

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

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

1. Joelle Lamarche says:

Thank you for getting back so quickly!
Here are the error details.

2. Joelle Lamarche says:

Thank you for getting back so quickly!
Here are the error details.

170. amit says:

Hello Kasper,
Thanx for the quick revert.

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

171. amit says:

Hello Kasper,
Thanx for the quick revert.

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

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

172. Jordy Vondermans says:

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.kasperonbi.com/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.

173. tkaresz says:

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,

174. Zack Goldman says:

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!

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

1. Zack Goldman says:

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

175. Kate S says:

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?

1. Thomas says:

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

1. Thomas says:

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

176. Nick Banbury says:

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?

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

1. Nick Banbury says:

Will do – thanks Kasper.

2. Nick Banbury says:

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

3. Nick Banbury says:

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

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

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

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

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

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

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

178. Vincent van der Kruit says:

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

179. Jeff says:

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…

180. paul says:

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’

Any ideas as to what the error is symptomatic of?

Thanks,

Paul

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

1. paul says:

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

181. Joe Berish says:

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.

1. Thomas says:

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.

1. Joe Berish says:

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

1. Thomas says:

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.

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

2. Thomas says:

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.

182. Zack Goldman says:

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

1. Thomas says:

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

1. Thomas says:

Hopefully these images will come through.

2. Zack Goldman says:

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!

183. Ron H says:

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

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

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?

185. Geir Forsmo says:

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?

1. Thomas says:

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…)?

186. Geir Forsmo says:

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

1. Thomas says:

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.

187. Rajesh Achanta says:

Hi,

I’m new to Power BI, we’re planning to use Power BI reporting with Microsoft Dynamics CRM.

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

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 ?

Rajesh

1. Thomas says:

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.

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

1. Thomas says:

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

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

189. Sophie Marchand says:

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

190. Andy Josolyne says:

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)

1. Thomas says:

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.

1. Andy Josolyne says:

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

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

Thanks

1. Thomas says:

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

192. Volodymyr Paliy says:

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?

1. Thomas says:

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.

193. Andy Josolyne says:

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.

1. Thomas says:

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.

194. Ben Lezin says:

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

195. Michael says:

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

196. Ben Lezin says:

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?

1. Ben Lezin says:

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

2. Ben Lezin says:

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.

1. Thomas says:

Ben,
This is a good insight.

197. David says:

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:

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?

198. Ben Lezin says:

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

1. Chris Gilbert says:

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(, , , …)

1. Ben Lezin says:

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.

1. Ben Lezin says:

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.

1. Chris Gilbert says:

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

2. Ben Lezin says:

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.

3. Thomas says:

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.

2. Thomas says:

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:

1. Ben Lezin says:

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.

199. Glo Ann Odevilas says:

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!

200. Ben Lezin says:

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

1. Ben Lezin says:

BTW, figured this one out using the DATESBETWEEN function.

201. John Donnelly says:

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!

202. Chris Bailiss says:

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

1. Chris Bailiss says:

Thanks Kasper.

203. THIAM HUAT Tan says:

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

1. Kasper de Jonge says:
1. Hi Gerald,

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

Thanks,
Kasper

204. J. gM says:

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 ?
These two serious issues make using your book much more difficult.
Regards

205. J. gM says:

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.

1. Thomas says:

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

1. J. gM says:

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

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

1. J. gM says:

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

2. Kasper de Jonge says:

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

206. J. gM says:

duplicate post sent by mistake.

207. David Arranz says:

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?

1. Kasper de Jonge says:

Hi David,

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

Thanks,
Kasper

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

1. Thomas says:

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.

209. J. gM says:

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 !

210. nathan f says:

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.

1. Kasper de Jonge says:

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

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

211. J. gM says:

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.

212. Josh says:

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!

1. Thomas says:

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

1. Josh says:

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.

1. Thomas says:

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?

1. Josh says:

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

2. Thomas says:

Josh,

3. Josh says:

fantastic. Thank you.

4. Thomas says:

Josh,

5. Josh says:

fantastic. Thank you.

6. Thomas says:

Josh,

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.

7. Josh says:

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.

213. Josh says:

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!

1. Thomas says:

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

214. Ben Lezin says:

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

215. Ben Lezin says:

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

1. Ben Lezin says:

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.

216. Ben Lezin says:

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

1. Thomas says:

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

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

1. Ben Lezin says:

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

2. Ben Lezin says:

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

1. Ben Lezin says:

Will do first thing tomorrow.

2. Ben Lezin says:

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

3. Ben Lezin says:

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

4. Thomas says:

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

5. Ben Lezin says:

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

Ben

3. Thomas says:

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

1. Ben Lezin says:

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

2. Ben Lezin says:

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

1. Thomas says:

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

2. Ben says:

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

3. Thomas says:

Ben,
Hmmm…
Let me think then some more…

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

5. Ben says:

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

6. Ben says:

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

7. Ben says:

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!

3. Ben says:

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

4. Ben says:

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

5. Ben says:

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

1. Thomas says:

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

4. Ben says:

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

1. Thomas says:

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.

1. Ben says:

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.

217. J. gM says:

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 ?

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

1. J. gM says:

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.

1. J. gM says:

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.

218. Chris says:

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

1. Thomas says:

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?

1. Chris says:

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

1. Thomas says:

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.

219. J. gM says:

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.

220. tchakounte says:

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

1. J. gM says:

I do not know how I could have missed it, the blog owner states flat out where to find the file:

The reference was given at:
http://www.kasperonbi.com/d

This one works. Excel can swallow it.

221. Tim Langford says:

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

1. Thomas says:

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

1. Tim Langford says:

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

2. Thomas says:

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

222. Ben says:

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

1. Ben says:

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

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

1. Ben says:

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.

2. Ben says:

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

3. Ben says:

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

4. Thomas says:

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

5. Ben says:

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

6. Thomas says:

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

7. Ben says:

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

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

1. Thomas says:

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.

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

1. Thomas says:

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.

2. FerroMinera Argentina says:

I think the RELATED function is what you need.

224. Ward says:

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?

225. Segal says:

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.

226. kthejoker says:

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?

1. Birgit says:

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?

1. kthejoker says:

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.

227. Arnoux Olivier says:

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

228. David Greenberg says:

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?

229. Josh says:

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.

1. Josh says:

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?

1. Josh says:

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

1. Josh says:

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.

2. could you include a XLSX file that contains a sample?

230. Josh says:

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

231. Andy says:

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

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

1. unfortunately this dataset is not really in sharable state 🙁

232. Mike Moore says:

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

233. Alyce Solomon says:

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

1. FerroMinera Argentina says:

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.

1. Alyce Solomon says:

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

1. Thomas says:

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

234. Michael says:

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?

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

1. Michael says:

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

235. FerroMinera Argentina says:

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.

1. Thomas says:

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…

1. FerroMinera Argentina says:

Thomas,

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.

236. marc says:

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

1. Thomas says:

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.

1. marc says:

Hi Thomas,

237. SAIFE says:

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

1. Thomas says:

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?

1. SAIFE says:

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

1. Thomas says:

Cool!

238. BI_User says:

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?

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

239. marc066 says:

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

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

1. marc066 says:

Hi Kasper,
Kind Regards.

240. Barry says:

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?

241. Josh says:

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

1. Thomas says:

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

1. Josh says:

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!

242. Christiane Pereira says:

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

1. Richa Gupta says:

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

243. Arun Sreenivas says:

Hello Kasper,

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

244. Thomas Woods says:

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.

245. Robin A says:

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.

246. Maria says:

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!

247. Matt says:

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

1. Thomas says:

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.

248. Maria says:

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

249. John says:

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]?))

250. Michael Deneys says:

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)

251. Jesper Bork says:

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

252. Ben says:

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?

253. Umesh Reddy says:

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?

254. Maria says:

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”

1. Maria says:

Thank you, Thomas. Appreciate the pointer.

255. Elizabeth says:

I’m building a PowerPivot Data Model in a human services setting which compares individual client transactions (entries in this context) against each other and provides the first and last transaction (entry) date and last exit date for each client on the row level context as part of the comparison.

I built a series of formulas using the earlier function to return the specific value of other columns in the model at the first and last values. When I try to return the exit date associated with the first or last entry date, I get this error:

Calculation error in column ‘fRawData'[]: A table of multiple values was supplied where a single value was expected.

=CALCULATE(DISTINCT(fRawData[Functional Exit]),
FILTER(fRawData,fRawData[EntryID]=EARLIER(fRawData[Last EntryID])))

[EntryID] is a compound key that is a concatenation of the Client ID and the Entry Date, returned to a numeric value.
[Last EntryID] is the most recent EntryID.
[Functional Exit] is the exit date with a substitution of the last day of the report if the exit date is null.

The identical formula works beautifully if I substitute [Functional Entry] (which is the entry date backed up 6 hours to get whole day calculations for entries before 5am) or any other column other than the [Functional Exit] or the raw exit date column.

Thanks!

Elizabeth

1. Elizabeth says:

I hacked it using Max instead of Distinct, which doesn’t make much sense to me, because I’m looking at one row of data. There shouldn’t be multiple values in one field of one row. Thoughts?

1. Thomas says:

Hello Elizabeth,
Probably the difference between “should be” and reality. I am convinced the error message is correct and somewhere in your data you have an unexpected and unwelcome surprise. All that is needed is one exception somewhere in the entire table. Because this formula uses EARLIER I am assuming that it is for a calculated column and not a measure; however, if you did get such an error message for a measure, remember it could apply not only at the row level, but could also refer to a problem with a sub-total or grand total while you are currently focused on a detail record.

1. Elizabeth says:

Thanks so much. It was a calculated column. The data I deal with uses a lot of individual record specific details and calculations because they’re people in a human services setting. This report sort of triages those whose needs are greatest instead of responding to the loudest or most charismatic. I got it working and my clients are thrilled.

256. Fred Wolterink says:

Hoi Kasper,
I have a small problem with my calculation in Powerpivot. Situation:
I got 2 tables connected with a in between table. To get a multiple / multiple relation.
But if I want to get a column value in to the other table is got blank values back. And that is for me a little strange 😉
Any tips on this?
Vriendelijke groet / Best Regards,
Fred

257. Sugi Gunamijaya says:

Is there a sumif function for M language? How do I create a sumif in power query? Thanks very much.

258. dominique Fischer says:

I have bought your book ‘Dashboarding etc.. in EXcel’
but cannot find the example used throughout.. should be able to download it.. from where?

1. Thomas says:

Hello Dominique,

259. Quido Degen says:

Hello, i’m struggling with below example. What is the best way to calculate the numner of shop for a region, where the sales is above 5…..
I have a account table with region&shop, and a separate salestbale (with shop)

1. Thomas says:

Hello Quido,

…the best way is always a moving target…

I named the example table Table1. I used two formulas in this case because the best way to calculate does not necessarily limit the number of formulas, especially in this case because these two formulas illustrate one of the most powerful features of DAX: the formulas in Power Pivot are portable. You write them once and use anywhere, even inside other formulas.

Total Sales:=SUM(Table1[Sales])
Sales Above 5:=CALCULATE ( [Total Sales], Table1[Sales] > 5 )

After creating these two measures, create a pivot, put region and shop on rows and put the [Sales Above 5] in the Values drop zone. Right away the results will tell you that Store b in Region S (*** not *** Store b in Region Q) also sold more than 5.

I think you would benefit greatly from these three books:

1) Dashboarding and Reporting with Power Pivot and Excel by Kasper de Jonge
2) DAX Formulas for Power Pivot by Rob Collie
3) Power Pivot Alchemy by Rob Collie and Bill Jelen

The best calculations flow from the best models.

1. Quido Degen says:

Hi Thomas txs for quick answer. It still does not really work. I would need a formula that tells me per shop if the sum of sales (within million records) is above 5. So more like count the number of stores per region where the sum of sales in a period is above 5 (certain threshold)

1. Thomas says:

Hello Quido,

Appreciate the feedback. Try this measure:

Shops with Sales Above 5:=CALCULATE(DISTINCTCOUNT(Table1[Shop]), Table1[Sales] > 5 )
In a new pivot, when I put Region on Rows and drop the above measure in the Values zone, I get a result similar to the one you show, except that for Region S I get 3 instead of 2 ( in region S, shops b, d and f have sales above 5).

1. Quido Degen says:

Hi Thomas, the last part: Table1[Sales] > 5, will give me the number of shops that have at least 1 order of 5+. But I would like to check the sum of sales in this table…

2. Thomas says:

Hi Quido,

Assuming the measure for Total Sales in my first comment is the same as “Sum of Sales”, try this measure:

Shops with Total Sales Above 5:=CALCULATE ( DISTINCTCOUNT ( Table1[Shop] ), FILTER ( Table1, [Total Sales] > 5 ) )
The FILTER function is a requirement in this formula because it uses a measure when applying a filter.
Let me know if the above formula meets your needs.

260. Mr TK says:

I have a list of stock prices and I want to calculate the
percentage change from the previous day. The raw data is on the left but the
desired end result should be similar to the table on the left. How do I pull up
data in pivot table so that I can calculate the changes?

1. Thomas says:

Hi,

To get the values you show, consider the following calculated columns:

PreviousDayStockPrice= CALCULATE ( AVERAGE ( [StockPrice] ), TOPN ( 1, FILTER ( Data, Data[Date] < EARLIER ( Data[Date] ) ), Data[Date] ) )

Increase=DIVIDE ( [StockPrice] – [PreviousDayStockPrice], [PreviousDayStockPrice] )

When it comes to division in DAX, I almost always use the DIVIDE function instead of the "/" operator because DIVIDE can handle division by 0 without throwing an exception.

I am not an expert on stocks, but for many other growth-type of calculations, I note that the divisor is not from the prior date, but from the "current" date:

Alternate for Increase=DIVIDE ( [StockPrice] – [PreviousDayStockPrice], [StockPrice] )

261. James Hinton says:

Hi Kasper,

Dumb question I’m sure, but please can you explain the purpose of IF in the following example: (from your blog: DAX now has variable support)

Future Investment = IF([Sum of SalesAmount], [Sum of SalesAmount] * [Rates calc]).

Thanks.

1. Thomas says:

James,

An excellent question!

Within the criteria section of an IF function, 0 can be used for false and a non-zero can be used as true. Also, when there is no calculation or value for when the “IF” criteria is false, the default is a blank.

So one way to read the formula:

When the “Sum of SalesAmount” is a non-zero value, then the “Future Investment” will be the result returned by the calculation “Sum of Sales Amount” * “Rates Calc”; otherwise (when the “Sum of SalesAmount” is zero) “Future Investment” will be blank.

Just to check things out, you could test the following measures in the Power Pivot window:

Zero is False:=IF ( 0, TRUE(), FALSE() )
One Is True:=IF ( 1, TRUE(), FALSE() )
Negative One Is True:=IF ( -1, TRUE(), FALSE() )
A Very Small Amount Is True:=IF ( 0.00001, TRUE(), FALSE() )

I commend you for your diligence in keeping up-to-date by reading Kasper’s blogs. I am now going to follow your good example and also read “DAX now has variable support”!

1. James Hinton says:

Fantastic – I get it.

Thanks Thomas.

262. Fredrik Nord says:

Does anybody got an ideas how to do a percentage calculation if you already have a page filter and you want the calculation to go against the prefiltered data.

I’m trying to do
=Sum(export[total])/Calculation(Sum(export[total];???)

But what filter should I use ?

263. umps17 says:

Hello,

I am having problems with a ‘sum only if’ problem. I have been browsing your helpful blog and saw you conquered a problem close to the one I have so I was hoping you can help me work through it!

I have three related tables. I am trying to sum units given 4 conditions:
1) If a person in table 1 has any of the SKUs in table 2–regardless of date
2) If a person in table 1 is listed as “Real” in table 3
3) If a person in table 1 has sales greater than 0 in table 3
4) Only sum of the units when the date of the record on table 1 matches the persons date on table 3

The only one I feel giving me problems is the first one in my DAX below. What is happening is I am filtering records out that have the date condition needed for the 4th condition. I feel like maybe I could be using the ALL() function but I am not sure where I would apply it. Any suggestions?

Measure1:=Calculate(SUM(Table1[Units]),
FILTER(VALUES(Table1[SKU]),COUNTROWS(FILTER(VALUES(‘Table2′[SKU]), ‘Table2′[SKU] = Table1[SKU])) >0),
FILTER(Table1,RELATED(Table3[Type])=”Real”),
FILTER(Table1,RELATED(Table3[Sales])>0),
FILTER(Table1,RELATED(Table3[Date])=Table1[Date])
)

1. Thomas says:

Hello Umps17:
I see the problem. If you expect to get an answer of 2 for person D, you should know that your multiple filters are “AND”ed and they rule out the two records you expected to have in your results (the two records you expected to have SKUs of 00006 and 00002 which were eliminated in the first FILTER).

1. umps17 says:

I see the problem as well. I have been stumped on a solution though. Any thoughts would be appreciated.

2. umps17 says:

Nevermind. Solved my own problem by adding a flag CalculatedColumn to Table3 referencing Table 1 which I then used in a filter function on my Measure in Table1.

264. Ben says:

Hello again, Thomas and Kasper,

I have another stumper that I thought would be easy to solve. I’m trying to create a measure that filters monthly totals based on aging accounts, aka Open Pool Date. So I have successfully created a model and measures to populate the monthly totals for all accounts that were opened 6 months prior. Hence the final total for 9-2015 should be a sum of all accounts that were opened on or after 4-2015. However, each monthly total goes back 6 months so my grand total of 12,778,323,97 goes back to accounts that were opened in 8/31/2014, but I only want it to total the accounts from 9-2015 forward. The catch-22 is that I need a way to go back one less month for each prior month so that I’m not including accounts that were opened before 4-2015. Ultimately, I need two filters: one to return a total for each month for accounts less than 6 months old (which I have in the first column); and a second to get the total for the previous six periods (which I have) BUT then removing the sum for accounts that were opened more than six months from the current month context.

As my screenshot indicates, I can achieve the result using slicers, but I want to create a DAX measure that will get the same result per month row.

Trailing6Month Conversion:=CALCULATE([TOTAL LTD Converted Amount] ,Filter(Settlement700,Settlement700[OpenDatePool]>=RELATED(DimDate[Trailing6MonthsEnd])))

Trailing6Month LTD Conversion Amount:=CALCULATE([Trailing6Month Conversion],DATESINPERIOD(DimDate[FullDate],LASTDATE(DimDate[FullDate]),-6,Month))

As always, any help would be much appreciated!

265. Dan Maycock says:

Hello, I have a calculation that’s being performed for a given month – but want to take that outcome by month (one value divided by another value for all sales in a given month) and then average those monthly amounts together over a number of months. I can’t do a rolling amount, as I need to keep my by-monthly totals in tact but want to average those amounts over several months. How can I do that?

266. Gusty says:

Hi everyone!
I’m building a model, where I’m facing 2 questions.

Firstly, similar sheets are generated separately, and time after time the number of rows are increasing. All these sheets has the exact same column names. I would like to have them linked in a way, that they become one in the data model. In other words I would like to have one “sheet” where all the various sheets are one after the other and when I refresh the new data gets into this sheet smoothly. I would like to resolve it within powerpivot if possible
I hope I descibed the issue appropriately, if not let me know. Looking forward to ideas! Cheers!

267. philipverspreeuwen says:

Hello all,

This is a simple, yet anoying problem I’m not able to solve. All help will be greatly appreciated.

I have a table of geocodes (longitude/latitude). For each geocode, I would like to calculate a column with the distance to the nearest geocode (in the same table). I have tried different things with MINX, but didn’t succeed.

PostalCodesBE.Table

BE-1000 Brussel 4,3515499 50,8427501
BE-1020 Laeken 4,3580002 50,884218
BE-1030Schaarbeek 4,3772981 50,8674164
BE-1040 Etterbeek 4,38899 50,83259
BE-1050 Elsene 4,3666294 50,8333432
BE-1060 Saint-Gilles 4,3402184 50,8301436

=MINX(PostalCodesBE;SQRT(POWER(row(“Long”;[Longitude])-[Longitude];2)+POWER(row(“Lat”;[Latitude])-[Latitude];2)))

Returns all ‘0’….

Any Idea is welcom!

268. Ted Murphy says:

Hi,

Lower Case Problem:

I need to get Lower Case characters into a Power Pivot table. Data is originating in an Excel Table (although I have also tried a Named Range).

Using Power Query to load … and when it arrives in Power Pivot Model it is all in Upper Case. Even when I try to force lower case within Power Pivot I have limited success. I can force Lower Case using the LOWER function, but when I subsequently use this result in a formula everything reverts to Upper Case.

Is there some global setting that is causing my problem?

Screen dump from Power Pivot attached below.

Would appreciate any help with this.

Ted.

1. Thomas says:

Hi Ted,
Have you tried accessing the Query Pane in Excel, selected your query, right-clicked, and then choose “Refresh”?

269. sqldude says:

Hi I really need some help modeling correct star schema for ssas tabular

I’m using ssas tabular (powerpivot) and need to design a data-model and write some DAX. I have 4 tables in my relational database-model:

Orders(order_id, order_name, order_type, spot_id)
Spots (spot_id, spot_name, spot_time, price)
SpotDiscount (spot_id, discount_id, discount_value)
Discounts (discount_id, discount_name)

One order can include multiple spots but one spot (spot_id 1) can only belong to one order.
One spot can include different discounts and every discount have one discount_value.
Ex:
Order_1 has spot_1 (spot_price 10), spot_2 (spot_price 20)
Spot_1 has discount_name_1(discount_value 10) and discount_name_2 (discount_value 20)
Spot_2 has discount_name_1(discount_value 15) and discount_name_3 (discount_value 30)

I need to write two measures: price(sum) and discount_value(average)

How do I correctly design a star schema with fact table (or maybe two fact tables) so that I in my powerpivot cube can get:

If i choose discount_name_1 I should get order_1 with spot_1 and spot_2 and price on order_1 level will have value 50 and discount_value = 12,5
If I choose discount_name_3 I should get order_1 with only spot_2 and price on order level = 20 and discount_value = 30

1. Thomas says:

For starters, since an order can have many spot_id’s but a single spot_id can only have one order, remove the spot_id from Orders and add order_id to the Spots table.
Before going any farther, please answer: Is it possible to have a record in the Spots table that does not reference an Order?
Let me know and I will continue from there.

1. Thomas says:

Assuming the answer will be a Spot must be associated with an Order, you would get a star schema by moving order_name, order_type and order_id to the Spots table. Also move the discount_name from the Discounts table into the SpotDiscount table. Drop the Orders and Discounts tables and you have a star schema where the relationship will go from SpotDiscount (the many) to Spots (the lookup). I will leave the measures for the next one to comment.

1. sqldude says:

Ok now I have two tables:
Spots(spot_id, spot_name, spot_time, spot_price, order_id, order_name, order_type) and
SpotDiscount(spot_id, discount_id, discount_name, discount_value)… And Spots is the lookup table….

1. Thomas says:

The next step, if you have not done so already, is to download the free data set and workbook from the link on the right side of this web page (follow “Download the dataset and workbook here”) and to get a copy of Kasper’s book, “Dashboarding and Reporting with Power Pivot and Excel”. The book will take you from wherever you are in Power Pivot and reporting skills and give you awesome insights and practical experience.

2. sqldude says:

I solved the problem with one fact table Fact(OrderKey, SpotKey, DiscountKey, DateKey, TimeKey Spot_Price, Discount_Value,…) and 3 dim tables: DimOrder, DimSpot, DimDiscount.

3. Thomas says:

Excellent! Much better than the first model.

What is the progress on your model’s measures?

4. sqldude says:

I’ve got some help from another forum and here it is:

Spot_Price:=
SUMX(
SUMMARIZE(
Fact
,Fact[OrderKey]
,Fact[SpotKey]
,Fact[Spot_Price]
)
,Fact[Spot_Price]
)

2. sqldude says:

Hi Thomas. Thanx for the answer. order_id is actually in my Spots table and Orders table doesn’t have any spot_id column – just as you suggested. I was actually wrong when I wrote a question. So long so good:)
No it is not possible to have a record in the Spots table that does not reference an Order. Every spot_id belongs to an order.

270. John McNally says:

I was watching your “Solving Complex Business Problems with DAX” video, and have a question about filter context. For this DAX formula:

=CALCULATE ([Sum of Revenue]
,ALL(DateTable)
,FILTER(ALL(DateTable[Date]),
DateTable[Date] <= MAX(DateTable[Date])
)

The FILTER function iterates the DateTable, and the DateTable[Date] expression is using the row context provided by the FILTER function.

But the MAX(Date Table[Date]) expression is accessing the "original" filter context from the pivot table. How does the DAX engine know this? Is it the use of the aggregate function, MAX, that directs the engine to use the filter context from the pivot table?

1. Thomas says:

Hi John,
I am not Kasper, but I would like to weigh in on this one. I agree with your insight that it is the MAX that directs the engine to apply criteria (use the filter context), and I also believe it is the un-aggregated left side of that comparison that directs the engine to iterate through a copy of the DateTable and receive that criteria.

271. Rick says:

Hi,

I’m looking for a solution for switching between union and intersect. The scenario is around selecting “features” to return either the number of models with feature1 or feature2 (union), or returning the number of models with feature1 and feature2. I thought to use a disconnected slicer for the ([And],[Or]) query type.
I am challenged about how a measure that would return the intersect result. Since this is being presented in Power BI I don’t have the luxury of using Excel formulas.
Is there a way in DAX to collect all the selected values on a slicer to use in the measure calculation?

272. Karen Schaefer says:

Kasper,

I am using Power Query – Excel 2013. I need to be able to replace the column header name to display the current date – 3 days and the remaining columns to add 1 day per column .

ie. today = 11/2/ 2015
Column4 =10/30/2015
column 5 = 10/31/2015
column6 – 11/1/2015
etc.
what is the function needed to replace the Column header to display the correct date

found function need help with syntax:

#”Renamed Columns” = Table.RenameColumns(US1_Sheet,{{“Column1”, “FC”}, {“Column2”, “FCfilter”}, {“Column3”, “DataLabel”}, {“Column4”,Date.AddDays(DateTime.FromText(datetime), -3)}}),

Promoting headers does not change the column headers to display the correct date range.

K

1. Thomas says:

Karen,

This will work (create 4 variables, convert three of them to text, then change column names):

#”Today” = Date.From ( DateTime.LocalNow() ),
#”Three Days Ago Label” = Date.ToText ( Date.AddDays ( #”Today”, -3 ) ),
#”Two Days Ago Label” = Date.ToText ( Date.AddDays ( #”Today”, -2 ) ),
#”One Day Ago Label” = Date.ToText ( Date.AddDays ( #”Today”, -1 ) ),
#”Renamed Columns” = Table.RenameColumns(US1_Sheet,{{“Column1”, “FC”}, {“Column2”, “FCFilter”}, {“Column3”, “DataLabel”}, {“Column4″, #”Three Days Ago Label”}, {“Column5″, #”Two Days Ago Label”}, {“Column6″, #”One Day Ago Label”} })

1. Karen Schaefer says:

ok Now How do I set the column header for the remaining columns of data (column count unknown) based on current date + remaining column count.

2. Karen Schaefer says:

Still looking for assistances with setting the remaining column headers to display the current date (column7), current date +1 (column8) or previous column.value +1 for each of the remaining columns

273. Karen Schaefer says:

new to this not sure of the syntax for using variables. where do I place this code at the beginning of the dax code???

274. Karen Schaefer says:

I need to either figure out how to retrieve a start date from the original data (cell C8) and be able to use that as the basis for the calculation of dates for the remaining columns.

Or get the dates to be set as headers. which currently fails. I will promote the non-date columns to the header but the date fields fail
19 KB
datasamplePivotData.xlsx

I found the GetVaule code on stackoverflow – not sure where to place the function code and the code string to my code.

=Excel.Workbook(File.Contents(GetValue(“SourceFile”)))
Here is my current code:
let GetValue=(DateUsed) =>
let
name = Excel.CurrentWorkbook(){[Name=DateUsed]}[Content],
value = name{0}[Column3]
in
value
in GetValue

let[b] ‘<<<<<<<<<<<<<<<<<<<<<< Dies Here[/b]
// #"Today" = Date.From ( DateTime.LocalNow() ),
// #"Three Days Ago Label" = Date.ToText ( Date.AddDays ( GetValue, -3 ) ),
// #"Two Days Ago Label" = Date.ToText ( Date.AddDays ( GetValue, -2 ) ),
// #"One Day Ago Label" = Date.ToText ( Date.AddDays ( GetValue, -1 ) ),
// #"CurrentDay" = Date.ToText ( Date.AddDays ( GetValue, 0 ) ),
#"Day1 Label" = Date.ToText ( Date.AddDays ( GetValue, 1 ) ),
#"Day2 Label" = Date.ToText ( Date.AddDays ( GetValue, 2 ) ),
#"Day3 Label" = Date.ToText ( Date.AddDays ( GetValue, 3 ) ),
#"Day4 Label" = Date.ToText ( Date.AddDays ( GetValue, 4 ) ),
#"Day5 Label" = Date.ToText ( Date.AddDays ( GetValue, 5 ) ),
#"Day6 Label" = Date.ToText ( Date.AddDays ( GetValue, 6 ) ),
#"Day7 Label" = Date.ToText ( Date.AddDays ( GetValue, 7 ) ),
#"Day9 Label" = Date.ToText ( Date.AddDays ( GetValue, 8 ) ),

Source = Excel.Workbook(File.Contents("antdeptsandopBIGGulpReportINBOUND_FORECAST_SUMMARYReport.xlsx"), null, true),
US_BIG_GULP_STRING_Sheet = Source{[Item="US_BIG_GULP_STRING",Kind="Sheet"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(US_BIG_GULP_STRING_Sheet,{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}, {"Column8", Int64.Type}, {"Column9", Int64.Type}, {"Column10", Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] “Network” and [Column1] “Non Sortable” and [Column1] “Pantry” and [Column1] “Small Sortable” and [Column1] “Sortable” and [Column1] “Specialty”) and ([Column2] = “New Vendor Freight ?”)),
#”Renamed Columns” = Table.RenameColumns(#”Filtered Rows”,{{“Column1”, “FC”}, {“Column2”, “FC Filter”}, {“Column3″, GetValue}}),
#”Renamed Columns1″ = Table.RenameColumns(#”Renamed Columns”,{{“Column4″, #”Day1 Label”}}),
#”Renamed Columns2″ = Table.RenameColumns(#”Renamed Columns1″,{{“Column5″, #”Day2 Label”}}),
#”Renamed Columns3″ = Table.RenameColumns(#”Renamed Columns2″,{{“Column6″, #”Day3 Label”}}),
#”Renamed Columns4″ = Table.RenameColumns(#”Renamed Columns3″,{{“Column7″, #”Day4 Label”}}),
#”Renamed Columns5″ = Table.RenameColumns(#”Renamed Columns4″,{{“Column8″, #”Day5 Label”}}),
#”Renamed Columns6″ = Table.RenameColumns(#”Renamed Columns5″,{{“Column9″, #”Day6 Label”}}),
#”Renamed Columns7″ = Table.RenameColumns(#”Renamed Columns6″,{{“Column10″, #”Day7 Label”}}),
#”Unpivoted Columns” = Table.UnpivotOtherColumns(#”Renamed Columns7″, {“FC”, “FC Filter”}, “Attribute”, “Value”)
in
#”Unpivoted Columns”

275. Pinaki Mitra says:

Hello Kasper, I am reading your “Dashboarding and Reporting with Power Pivot and Excel: How to Design and Create a Financial Dashboard with PowerPivot – End to End” and working on a project as well where I need your help. I have:

1) FactRevenue table where I have revenue by customer key by weekending date (always Saturday).
2) DimDate table where I have unique weekending date(always Saturday)

3) DimCustomer table where I have Customer name, address by customer key

These tables get updated weekly. I need to be able to create last 4 weeks (rolling monthly summary), last 12 weeks(rolling quarterly summary), last 24 weeks (rolling semiannual summary) of revenue dynamically from the latest weekending date in the FactRevenue table when data gets updated weekly.

If I do Last4WeeksRevenue:=CALCULATE(SUM(FactRevenue[RevenueAmt]), DATESBETWEEN(DimDate[WeekEndDate], DATE(2015,10,10), LASTDATE(DimDate[WeekEndDate]))),
I get the correct number. But if I do:

Last4WeeksRevenue:=CALCULATE(SUM(FactRevenue[RevenueAmt]), DATESBETWEEN(DimDate[WeekEndDate], LASTDATE(DimDate[WeekEndDate])-4, LASTDATE(DimDate[WeekEndDate])))
where I want to calculate this dynamically, it does not produce any number.
How can I do this dynamically so that every time data gets updated, this measure also gets updated? Thank you for your help.

1. Thomas says:

I think I see the issue here: it appears you have a “dates” table that is really a “weeks” table (your formula is subtracting 4 weeks and not 28 days) and DATESBETWEEN expects a “dates” table that conforms with the requirements Kasper lists on pages 37 & 38 of his book (look under “Working with Time Calculations” and review items 1 through 6).
On another page of this web site, Kasper gives the Power Query code for generating a dates table. After the dates table is created, you could add a calculated column in the Power Pivot window for the week ending date).

You would create a relationship between a date column in the FactRevenue table and the date column in the dates table; NOT on the week ending date in the dates table. In your pivots and charts, you would use the date or weekending date from the dates table as filter coordinates (zones for rows, column, filter or slicers) and not the week ending date in your fact table.

276. Donald Parker says:

Looking for Power Query help here. Or possibly some combination of Power Query and DAX once I’ve read the data in. Each row of the CSV input data represents a transaction and includes an ID and an amount. I want to split the data into two tables – one with IDs that only had one transaction and another that only contains records for IDs that had more than one transaction.

277. S Mohan says:

Hi, we have few tables in Oracle which I wanted to analyze but these tables are from different schemas. Each table has the same format but they belong to different client and hence different schema.Can we combine tables from multiple schemas?

278. Edoardo says:

Hi guys,
Ineed help solving this issue: I have a table with all my orders lines (customer , date of order, article, quantity price, etc), I need to identify for a specific customer which is the first order of a specific article (first date filtered for customer and article) and use this information to tag the order line for that article for the specific customer as “Newly Ordered article” if the order date = first order date or as “Reordered article” if the date on the specific order line is subsequent to the first order date.

I immagine I need to combine a Lookup date filtered for customer and article and use it with a IF formula.

Can anyone help?

Similar to this I would also like to define that if the first order for a product for the specific custumer is older then a certain date, then this would be defined as and historical customer for the specific article, if the first order on the article is more recent the a specific date, then this will be defined as “new customer for that article”.

Thanks to any expert that can give me an hand!

279. Rhett Smallwood says:

Hello – we have revenue reports that flow in periodically during the month. Until revenue in reported we use a 3 month forecast, once it is reported it is replaced with the actual amount. Currently we are using a formula such as =if(index(Rev Report),match(Customer Name),Match(Report Month)>0, return the value, average(A3:C3). We would like to use powerpivot to create as the report is manually updated. Is this possible?

280. Gjo says:

I would like get prior year sales ,current year and difference b/w CY-PY by Customer.

I used ==CALCULATE(sum(Table2[AMT]),PREVIOUSYEAR(Table1[DATE]))

It’s returning BLANK , IF i Used “SameperiodLastYear”, I’m getting the same value as current year

281. kcaob says:

I am trying to link identification numbers from two tables in power pivot. I have the following tables:

Table 1: id, date, status, intervention, consent, notes
Table 2: name, grade, year, id
I was trying to link id to id together in power pivot but I get the following error message (Excel 2013):

“The relationship cannot be created because each column contains duplicate values. Select at least one column that contains only unique values.”
Then I tried (just to figure it out) to link two distinct but related values that have no duplicates in the separate tables (id to name) but I got the same error message.

282. Bin Zhao says:

I encountered a problem and have no idea how to solve it in DAX:
I am making a Tabular model in SSAS where I need to calculate a MeanRate for each row(start from row 30). I have ObservedRate for each row . The MeanRate will be calculated based on 30 past ObservedRate including the current one, and the contribution from each past ObseredRate is weighted according to a half Hann function(or half hanning window). So there is a fixed 30-row table containing those 30 pre-calculated fractions “w”. The algorithm would look like: MeanRate = SUM( ObservedRate( current row number – i ) * w( i ) ); where 0 <= i < 30.
Could you please give me some hint how I can calculate it, with DAX?

283. ross says:

I need help to write dax for the below scenario.

Id Dept pay

1 M \$10

1 M \$15

2 E \$10

2 E \$10

3 M \$20

3 M \$25

4 E \$10

Output should be for dept M, Avg Pay = 35

I need to find average pay for each dept. Please give your suggestions. Thank you.

284. Chris says:

Trying to do my P&L. Works almost. For some reason, when cascading the subtotals, it also includes “empty” values in the first subtotal, (revenue) I dont have any empty values, what it does it actually sets te result as emty and adds this as a top row and then it is included in the cascading. I guess there is something wrong with my formula(which I must thank David Churchward for..) The whole point is the get the net result when cascading, no I have the netreuslt as EMTPY in my pivottable and then in adds tihs at the top…. Can send workbook if needed

3 step calculation:

Value =SUM([Value])

[Value])

CALCULATE([Value];
BLANK())

285. Here’s a question:

I’m using the following in native Excel to calculate the Saturday before an inventory date:
=[@[Inventory Date]]-WEEKDAY([@[Inventory Date]],16)+1

It would be better to do this as part of the query in Power Query. What would be an equivalent formula?

1. I found the StartOfWeek feature in the Transform menu. Now the question is how to start the week on Saturday instead of Sunday.

286. Dan Maycock says:

I am building customer cohorts according to the first month in which a customer has made a purchase. I then want to build relative year calculations into the table, so that when I display month numbers across the y axis (month 1 month 2, etc) – it shows the sales values for each cohort (june 2010 sales for cohort 1, july 2010 sales for cohort 2, etc).

I’m not sure how to build the relative year without doing a complicated matrix, and was hoping you can help.

287. Karen Schaefer says:

How do I handle the changing column Headings when data is refresh.

I have a workbook that contains a data sheet that is refreshed from a web query. since the date range may vary, so does the column names (date) and or number of columns needed. ie. the number of weeks in a month may vary and so would the need for 4 or 5 weeks worth of data. So the column count will vary. how do I modify the M Query to allow for variance, either in promoting headers or change column type?

288. Florent says:

Hello,

I just saw this video on how to use the excel data mining add-in with PowerPivot: https://www.youtube.com/watch?v=yYV7UhLc6-0

When I try to do the same thing, I get the following error message (screenshot attached): “A Connection to Analysis Services is required to perform the Classify task”

I use Power Pivot for self service BI and I don’t have access to Analysis Services (SSAS) but I have SharePoint online and Power BI. Can you think of an alternative solution to use the Excel data mining add-in?

Thanks,

Florent

1. You do need to have a SSAS instance installed to get this working.

289. jb007 says:

Hello Kasper,

First of all, though it might be somehow irrelevant, this is my first post in your community, which is due to the fact that I’ve only started to learn DAX last week. I consider myself fairly advanced in Excel, however I’m just starting to understand the capabilities of DAX and I’m here to learn.

I have 2 different tables, one DimensionsExchangeRate table and one FactsSales table.

The FactsSales table contains sales from 2 countries, USA (in USD) and UK (in GBP), while the DimensionsExchangeRate table contains by-monthly Dates and Exchange Rates (USD to GBP). What I want is to convert all the sales in GBP and also to have Running Total Measure for Sales GBP.

I have managed to accomplish both things, and I did it by creating two Calculated Columns in the FactsSales table (Exchange Rate column and Product Sales GBP column). After this, I managed to create the Running Total Product Sales GBP column.

Now you may be thinking “Alright, good for you, now stop bragging and tell us what you want”. I want to create Measures for everything, and not one single Calculated Column.

In fact, with some help from Marco Russo (God almighty), I have managed to write the Exchange Rate measure, and I have also managed to write the Product Sales GBP measure; so basically you’d think that I don’t need any of those columns. However, what I’m having trouble with is creating the Running Total Product Sales GBP measure unless my Product Sales GBP is a Calculated Column. If it is a measure, I can’t manage to get it to work.

———————————————————————————–

I will write below the formulas I’ve used, so you can understand better. First, this is how my DimensionsExchangeRate Table looks like.

DATE LOWER UPPER EXCHANGE RATE

12/15/15 11/18/15 1.523

11/18/15 10/25/15 11/18/15 1.512

10/25/15 9/8/15 10/25/15 1.554

9/8/15 9/8/15 1.593

Basically, the Exchange Rate is to be used for Sales between Lower and Upper.

———————————————————————————–

1) Exchange Rate Measure:=AVERAGEx(FactsSalesTable, 1 *
LOOKUPVALUE(DimensionsExchangeRate[Exchange Rate],DimensionsExchangeRate[Date],
CALCULATE(MAX(DimensionsExchangeRate[Date]),
FILTER(DimensionsExchangeRate, FactsSalesTable[Date]>=DimensionsExchangeRate[Lower])))) -> this measure works ok

———————————————————————————–

2) Exchange Rate Calculated Column =

=LOOKUPVALUE(DimensionsExchangeRate[Exchange Rate],DimensionsExchangeRate[Date],
CALCULATE(MAX(DimensionsExchangeRate[Date]),
FILTER(DimensionsExchangeRate, FactsSalesTable[Date]>=DimensionsExchangeRate[Lower]))) -> this calculated column works ok

———————————————————————————–

3) Product Sales GBP Measure:=SUMX(FactsSalesTable, IF(FactsSalesTable[Country]=”USA”, FactsSalesTable[Product Sales]/
AVERAGEx(AA5, 1 *
LOOKUPVALUE(DimensionsExchangeRate[Exchange Rate],DimensionsExchangeRate[Date],
CALCULATE(MAX(DimensionsExchangeRate[Date]),
FILTER(DimensionsExchangeRate, FactsSalesTable[Date]>=DimensionsExchangeRate[Lower])))),
FactsSalesTable[Product Sales])) -> this measure works ok

———————————————————————————–

However now, when I try to create the Product Sales To Date GBP Measure (aka “Running Total”), I can’t quite get it to work.

4) Product Sales To Date GBP:=Calculate(SUMX(FactsSalesTable, IF(FactsSalesTable[Marketplace]=”USA”, FactsSalesTable[Product Sales]/

AVERAGEx(FactsSalesTable, 1 *
LOOKUPVALUE(DimensionsExchangeRate[Exchange Rate],DimensionsExchangeRate[Date],
CALCULATE(MAX(DimensionsExchangeRate[Date]),
FILTER(DimensionsExchangeRate, FactsSalesTable[Date]>=DimensionsExchangeRate[Lower])))),

FactsSalesTable[Product Sales])),
FILTER(ALLEXCEPT(FactsSalesTable,DimensionsProducts[Product],DimensionsProducts[Country]), FactsSalesTable[Date] <=MAX (FactsSalesTable[Date] ) )).

The problem with this formula is that the “Calculate(Max())” function (from the “AverageX”) will return the first date that has an ExchangeRate from the DimensionExchangeRate table, and for the first dates from the FactsSalesTable (which are before the date that “Calculate(MAX)” retrieves ) I get a NUM! error, because of the outer “Filter” (of the first “Calculate” function).

For example, if my first (oldest) date in the DimensionsExchangeRate table is Apr/1/2015, and my FactsSalesTable contains dates starting from Jan/1/2015, I’d get errors for dates: Jan/1/2015 – Mar/31/2015.

———————————————————————————–

As I've said, in order to get the job done, I had to insert a Calculated Column for the Product Sales GBP =IF(FactsSalesTable[MarketPlace]="USA",FactsSalesTable[Product Sales]/FactsSalesTable[Exchange Rate],FactsSalesTable[Product Sales])

And now, because I have a Calculated Column, I can easily create a measure for the Running Total.

Total Product Sales GBP To Date:=CALCULATE (SUM (FactsSalesTable[Product Sales GBP] ),
FILTER(ALLEXCEPT(FactsSalesTable,DimensionsProducts[Product],DimensionsProducts[Country]), FactsSalesTable[Date] <= MAX ( FactsSalesTable[Date] ))).

Again, what I want is to make the Measure for the Running Total Product Sales GBP to work, without having the Calculated Column for the Product Sales GBP. I'm sorry for the long post, but it was so I can explain clearly the situation.

290. Grant says:

Hi Kasper, I have set my data type to decimal within Power Query, but I still get errors when I pull my data – those errors only come in when the numbers are decimals like 12.5 for eg. Is there something that I am not doing? Thanks

1. What types of values are failing? Maybe you need to set the right locale?

291. chtullu says:

I’ve been working through your book Dashboarding and Reporting with Power Pivot and Excel. I am currently running Excel 2016. On page 21 of your book when I view the existing connections, I don’t see the tables tab.

1. Did you actually import the data? I do still see it in Excel 2016.

292. Rasmus Laygardt Röck says:

Hi. I am having problems in PowerPivot, and was hoping someone could help me.

I have three tables:

– PROJTRANS (the transaction table)
– EXCHANGECODERATE (table with historic exchangerates)
– EXCHANGECODE (the link between the two other tables)

In PROJTRANS i have columns ‘QTY’ and ‘Salesprice’, and i would like to calculate the correct revenue. However, the salesprices are all in Danish currency, but some should be exchanged to the correct currency determined by a ‘exchangecode’ column in the same table. This column is either ‘USD’, ‘EUR’, or blank (which equals DKK). But the exchange rates are stored in another table.

My problem is that in the ‘EXCHANGECODERATE’ table, the exchangerate is given by two columns named ‘fromdate’ and ‘todate’. This means that the exchangerate for a given date is determined by which interval is falls within (example: The exchangerate for EUR is 7,4587 for dates between 30-10-2013 and 29-11-2013).

I have tried using a LOOKUPVALUE formula:

=LOOKUPVALUE(EXCHANGECODERATE[EXCHRATE];
EXCHANGECODERATE[FROMDATE];
PROJTRANS[INVOICEDATE];
EXCHANGECODERATE[EXCHANGECODE];
PROJTRANS[EXCHANGECODE]
)

This works just fine when the invoicedate matches either a ‘fromdate’ og a ‘todate’. But when the invoicedate is between the two dates, it does not return any value.

So what i am asking is:

– Is it possible to use the LOOKUPVALUE formula to solve this problem?
– Does anybody have any suggestions on how to solve it?

Thanks in advance, and hope somebody can help me!

293. wouter Kessener says:

Hello Kasper,

Hope you are willing to take a moment of your time to help me (and probably some other
sql monkey’s trying to figure out dax) out.

Please take look at the attached picture.

In SQL this would be a breeze, but in dax?? some kind of dax magic needs to happen.. unfortunately I lost my wand.
Hope to hear from you.
btw J schoenmakers says hi! 🙂

1. wouter Kessener says:

..fresh strat in the morning does wonders.. got it.

Column = calculate(values(Schedule[Schedule.hourspermonth]);

Filter (Schedule;Schedule[user]=’User per period'[Med.ID]

&&

‘User per period'[Startdate] >= Schedule[Schedule.StartDate]

&&

‘User per period'[Enddate] <= Schedule[Schedule.EndDate]))

294. Charles Wyman says:

I want to create a daily earnings at risk (De@R) function from portfolio data in powerpivot. The calculation is simple, but difficult for me to visualize in powerpivot. First you calculate the daily change in price of the security for each of the past 10-20 trading days (we’ll be happy with 10). You assume your current shares/position size was held for that entire period, so that you multiply your current shares by each price change to create a series of value changes of the position for the period. Finally you take the standard deviation of the series and multiply it by 2 (95% confidence interval) to calculate the De@R for the position. This seems to me to be a 3 dimensional table and I’m baffled about how to do it.

295. João Miranda says:

Hello
how we can calculate the useful time between two dates.
Example: consider only 8 hours per working day and not consider the days not useful?
Best regards,
João Miranda

296. Sergei Dumnov says:

In your webinar “Advanced Modeling and Calculations Using the Microsoft Power BI Designer” you demoed how to sort a chart axis by another field not bound directly to the axis. It seems to me this feature is not available in Power Bi Desktop. Am I right?

1. It is a available under the modelling tab with “Sort By”

1. Sergei Dumnov says:

It does not look like as it it was in your demo.. The Sort Dropdown shows only fields that used to build the chart

297. David Cox says:

Hi Kasper,

We have a table that shows expenses by month. Unfortunately December has bonuses for the whole year included.
I would like to create a DAX measure that takes an average for the first 11 months (jan to nov).

December then takes this average, the difference between the average and the original december value are then distributed over the 12 months.

My efforts at the DAX code

IF (
MAX ( ‘Finance2015′[Month] ) = 12,
CALCULATE ( [Avg11Mth] )
+ DIVIDE (
CALCULATE (
SUM ( ‘Finance2015′[Salaries] ),
FILTER ( ‘Finance2015’, ‘Finance2015′[Month] = 12 )
) + [Avg11Mth],
12
),

CALCULATE ( SUM ( Finance2015[Salaries] ) )

+ DIVIDE (

CALCULATE (

SUM ( ‘Finance2015′[Salaries] ),

FILTER ( ‘Finance2015’, ‘Finance2015′[Month] = 12 )

)

+ Finance2015[AverageSalaries],

12

)

)

This failed to calculate the december value. I believe due to the filter applied. I created a measure [Avg11Mth] to obtain the average of the first 11 months

298. David Cox says:

Hi Kasper,

We have a table that shows expenses by month. Unfortunately December has bonuses for the whole year included.

I would like to create a DAX measure that takes an average for the first 11 months (jan to nov).

December then takes this average, the difference between the average and the original december value are then distributed over the 12 months.

My efforts at the DAX code

IF (
MAX ( ‘Finance2015′[Month] ) = 12,
CALCULATE ( [Avg11Mth] )
+ DIVIDE (
CALCULATE (
SUM ( ‘Finance2015′[Salaries] ),
FILTER ( ‘Finance2015’, ‘Finance2015′[Month] = 12 )
) + [Avg11Mth],
12
),
CALCULATE ( SUM ( Finance2015[Salaries] ) )
+ DIVIDE (
CALCULATE (
SUM ( ‘Finance2015′[Salaries] ),
FILTER ( ‘Finance2015’, ‘Finance2015′[Month] = 12 )
)
+ Finance2015[AverageSalaries],
12
)
)

This failed to calculate the december value. I believe due to the filter applied. I created a measure [Avg11Mth] to obtain the average of the first 11 months

I accidentley submitted this before I was ready

David

299. KarenS says:

If starts with “Bonus” or contains * then strip the data and only display the correct name. See sample data.

1. David Cox says:

However the data is at a summary level, not that detail. The average figures for December are skewed, the reason I was given was bonuses were included. My calculation needs to average those bonuses over the 12 months of the year. My thought was to take the average for the first 11 months. The difference between December figure and average for 11 months would leave the approximate bonus level, then spread that across all 12 months.

300. Philip says:

I have just started getting the following type of error message with certain measures (CALCULATE, SUMX etc) which previously worked fine:

“The expression is not valid or appears to be incomplete. Please review and correct the expression.
The syntax for ‘(‘ is incorrect. (CALCULATE([Sum of BUDGET],[BUDGET STAGE]=0)).”
I’m using PowerPivot with Excel 2010. Any ideas?

1. Philip says:

The entire expression is just:

=(CALCULATE([Sum of BUDGET],[BUDGET STAGE]=0))

“[BUDGET]” and “[BUDGET STAGE]” are valid fields within the table and [Sum of BUDGET] works.

The full error message is:

The expression is not valid or appears to be incomplete. Please review and correct the expression. The syntax for ‘(‘ is incorrect. (CALCULATE([Sum of BUDGET],TableHoursBudgets[BUDGET STAGE]=0)).
Thank you.

1. Philip says:

The entire expression is just:

=(CALCULATE([Sum of BUDGET],[BUDGET STAGE]=0))

“[BUDGET]” and “[BUDGET STAGE]” are valid fields within the table and [Sum of BUDGET] works.

The full error message is:

The expression is not valid or appears to be incomplete. Please review and correct the expression. The syntax for ‘(‘ is incorrect. (CALCULATE([Sum of BUDGET],TableHoursBudgets[BUDGET STAGE]=0)).
Thank you.

301. SullenRaven says:

Question: How to Join two tables on multiple fields where one part of join is on date fields that require a calculation to determine the appropriate record to select from the lookup table. I have two tables exhibited below. The lookup table is a date-ordered span table with a location code or what you might term a member-span record set (this is common in eligibility records where member parameters may change over time); the member span records to not overlap but may not be contiguous either. Does Powerpivot (or even Power Query) have the ability to manage this relationship structure?

[Fact].[MemberID ; Date]
99999, 1/11/2015

[Lookup].[MemberID; Start_Date; End_Date; LocationCode]
99999, 1/1/2014, 12/31/2014, Denver
99999, 1/1/2015, 6/30/2015, Pueblo
99999, 7/1/2015, 12/31/2099, Denver

Given the data above, the member was located in Pueblo at the Date in the Fact Table. Following is how the join would function.

Join On [Fact.MemberID]=[Lookup].[MemberID]
and [Fact].[Date] >= [Lookup].[Start_Date]
and [Fact].[Date] <= [Lookup].[End_Date]

Thank You.

302. Ellie says:

I have 2 years data and it is been shown by month. I need to show the previous year by month but she it me to the next years data I only need to show the first month. Can this be done and if so how

1. Can you please elaborate a bit more? I think this is doable but without more information I can’t help too much

303. Favio Del Castillo López says:

I need to calculate the variation of sales volume of the current month vs the same period of the previous year.

At a product level the formula is working fine, but the subtotal and total DO NOT ADD UP.

I have tried many approaches and techniques available on the web, but I couldn’t find anything related to calculating a measure using data from different periods.

The formula basically is:
[ (Sales Qty 2016) – (Sales Qty 2015) ] * [ (Sales Amount 2015) / (Sales Qty 2015) ]

My DAX formula attempt is:
=(CALCULATE(sumx(Sales_Data,Sales_Data[Qty]))- ****Sales Qty 2016******

CALCULATE(sumx(Sales_Data,Sales_Data[Qty]),SAMEPERIODLASTYEAR(Date_Table[DateKey])))* ****Sales Qty 2015******

(CALCULATE(sumx(Sales_Data,Sales_Data[Net Sales Inc.Rebate]),SAMEPERIODLASTYEAR(Date_Table[DateKey])) / ******Sales Amount 2015******

CALCULATE(sumx(Sales_Data,Sales_Data[Qty]),SAMEPERIODLASTYEAR(Date_Table[DateKey]))) ****Sales Qty 2015******

Any way that I can calculate the subtotal and totals a per the snapshot attached.

Many thanks.
Favio

304. Herb D'Argenio says:

In the measures area of my Powerpivot model, PPVT shows the title but not the results of the calculation.
Example: “Backlog Jobs Total’ but no totals. If I go to the Pivot table I can display the results of the calculation I just can’t see it in the measure.

305. Ben Bek says:

Hi There – Is there an easy or latest way to to combine 2 data tables with the exact same headings into one powerpivot table
Txs Ben

306. Richard Wheeler says:

When I connect my calendar table to my sales table I get the following message “Failed to set uniqueness in column date”
I can force the relationship but then my datesinperiod doesn’t work.

307. Roberta Casey says:

I am using Power BI (desktop) and am looking at daily time sheet data. If someone did not enter any time there is no data for that date for them. I want to identify those gaps at the end of month and just show the exceptions. So for all the people who entered time I only want to show those who did not enter time on every weekday. I have a table with employee on one axis and weekdays on the other and hours on their time sheet in the table. Now I just want to exclude everyone who has hours in every cell. I need to at least get it into a table and then I can play around with making it pretty. Any suggestions?

308. Sean IPS says:

Hi, I want to calculate the balance outstanding as shown. I was trying to make it work with EARLIER() function.

CALCULATE(
SUM(Query[Debit]) – SUM(Query[Credit]) ,
FILTER( ALLEXCEPT( Query, Query[InvoiceId],
Query[TransactionDate] <= EARLIER(Query[TransactionDate])
)))

I get an error when I use the EARLIER().

309. Shawn Egan says:

cannot refresh power bi with a power pivot data model source. Continue to get Expression Error column ‘Time Period’ not found. Have tried editing the query but this creates more errors. Not very familiar with power query and hoping to find a solution to easily update PBI work through power pivot models

310. Warren says:

I have 4 powerpivot tables. I created relationships. I want to build a pivot table with fields from all of the tables, but am having issues. How can I show the tables, so i can get assistance?

311. Fábio Coatis says:

Parent Child using DAX.

I am using AdventureWorks DW 2014 Table Organization in PowerPivot of Excel 365 ProPlus

I created a Column named Hpath =PATH([OrganizationKey],[ParentOrganizationKey])

I created another column named Level 1 =LOOKUPVALUE([Organization Name],[OrganizationKey],PATHITEM([HPath],1,1))

I get an error in this formula:
Function ‘LOOKUPVALUE’ does not support comparing values of type Integer with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.
The example I am following (video) is from Excel 2013. Is 2016 less compliant with this type mismatch?

312. Andrew says:

I have to built a DAX measure in a tabular model which should change its value depending on certain attribute value. But I’m finding difficulty to incoporate a scenario in the formula when there is multiple selection. This is the formula I’m using Forecast:=IF(DISTINCT(‘Forecast Version'[ForecastVersionId])=4,[October Forecast],IF(DISTINCT(‘Forecast Version'[ForecastVersionId])=7,[January Forecast],BLANK())) . I want to show sum of both amounts if I select 4 and 7 at same time. Can you please provide some inputs on this ?

313. lrmmf says:

Hi,

i would like to ask you if there is a way to emulate CROSSJOIN in DAX, in a measure like this

measureForceMaxPostingDate:=CALCULATE(LASTDATE(‘Dim_Posting_Date'[Date]);ALLSELECTED(‘dim_posting_date'[Date]))

the objective is to filter factual rows <= a MAX(Date) selected on pivottable, without having context problems because factual is connected to that dim posting date.

CROSS JOIN is documented…

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

… but it will only appear in SSAS/Excel 2016…

Thank you and regards

314. Elizabeth says:

Hello,

I am really stuck. I have a data model in PowerPivot 2013 with clients and entry dates and exit dates in specific programs. I need to find the overall length of time between entry and exit assuming that a client cannot physically be in more than one program concurrently. The problem is that they can be enrolled in multiple programs concurrently. The calculations I’ve come up with are artificially inflating the length of overall time in any programs because of multiple enrollments. here is an example:

Client | Program | Entry Date | Exit Date | Length of Stay

1 | A |1/1/2014 | 2/1/2016 | 761
1 | B | 1/1/2015 | 1/1/2016 | 365
1 | B | 2/1/2016 | 3/25/2016 | 53
1 | C | 3/25/2016 | Null | 31
2 | A | 2/1/2010 | 2/9/2016 | 2199
2 | B | 2/20/2016 | Null | 65

The null values in the Exit column are substituted with the date of the report build which is 4/25/2016 to calculate the Length of Stay (LOS)

The cumulative LOS for each client should reflect only the LOS for the entire system rather than a sum of the individual programs.

Client 1 should have an LOS of 845 days in the system. The correct LOS would be from Program A and the second program B and program C, but ignore the overlapping enrollment in the first Program B enrollment.

Client 2 should have an LOS of 2264 in the system because they were not in the system for a few days between the end of their time in program A and beginning of their time in program B.

Some clients have hundreds of individual enrollments of a day or so, also.

Any help would be greatly appreciated.

Thanks!

Elizabeth

315. Reports_Dev says:

Whats the best practise for publishing and consuming data stored in Power BI?
My thought was to develop model in Excel (datasource in azure) and publish to OneDrive
in PowerBi GetData from OneDrive
Create content pack from PowerBi and distribute it to the organisation
“Analyze in Excel” to create the connections to the Dataset with Cubemembers to create excel reports
and also
use powerBi Desktop to create dashboards and reports and publish?

when adding new measures / columns / refreshing data we do that in Excel and publish again to one drive
is there a better way?
will it break?

316. We ordered your book “DASHBOARDING with Power BI Desktop & Excel” through Amazon back in JAn 16 for delivery Feb 16 yet still not received it. WejJust got a reminder from Amazon so When is it due out or should i cancel our order ?

317. malcolm ipony says:

I have bought the book “Dashboarding and Reporting with Power Pivot and Excel”,but where can I get the sample data——the Access file. Thank you!

318. Akshay Sharma says:

I was working on sales data of a product and i grouped it by year. Now I took two parameters in my report i.e. Current Year And a year from previous years to compare my data b/w two years. The problem that arised is that it shows only current year sales and in another year it is showing the same sales as of current year.

319. Andrea Bacs says:

Hi

I think I have a very little problem, but I can not solve it myself. I have daily basis sales data, from 2015.

I would like to see the SALES NSV for last year but only for relevant day. So now it 05.22, so I would like to see the NSV LY but till 05.22, and if I filter for may I would like to see the MTD date etc…

What is the function I need to use?

320. Femi Fapojuwo says:

Hi, I have a report that uses StartDate and EndDate to make calculations. I have to make versions of the report by adding 30 days lag in the first report and 60 days lag in the second report. I am trying to merge both reports into one and select to use either 30 days, 60 days or nothing as my lag. My issue is I can’t figure out how to use the slicer to dynamically select which lag to use. This dates need to be on the Calculated column has each Start and End dates are unique to a particular item. Can you help please?

321. Sreeja Gupta says:

Hi, my problem seems pretty simple compared to the ones below. I have a data model in powerpivot 2013 connected to an excel workbook. I have to add two more columns to the data model. So, I added them to the linked excel workbook and also created two columns of the same names in the powerpivot data model. Next I refreshed the link to this data model from “existing connections”. While it is getting refreshed the powerpivot data model is not picking up the values of the newly added columns from the excel workbook and they are not getting reflected in the front end pivot. Where am I going wrong?

322. Joren Reynders says:

Hi,

Probably a very basic question, but:

I’m connecting to a MySQL instance.
For the sake of the example let’s say I have 2 tables: CustomerType and Customer.
When looking in the PowerPivot datamodel the relations look fine, and each Customer is linked to a CustomerType

However, when adding both as rows in a PowerPivot table as such:
– CustomerType
– Customer

Each CustomerType shows all Customers, whereas I would expect to see only those Customers related to a given CustomerType.

If I only add CustomerType, I can also see the line item (blank) even though the table itself does not contain any empty rows. So it looks like Excel is adding this blank row automatically as a place holder when one would add a related table and one would have items without a value match.

So is this expected? Should one not be able to group Customers per CustomerType?

Thanks!

323. Damir says:

Hello to everyone
Im struggling with Profit and loss statement ( 1st coulmn:postitons of P&L, 2nd coulmn;values of P&L for 2015., 3rd column values of P&L for 2014.)

Im trying to insert 3rd column where will be indexes 2015./2014.

324. Damir says:

Hello to everyone

Im struggling with Profit and loss statement ( 1st coulmn:postitons of P&L, 2nd coulmn;values of P&L for 2015., 3rd column values of P&L for 2014.)

Im trying to insert 3rd column where will be indexes 2015./2014.

325. Yule MGlez says:

how to fit content to a table row

326. Hemant Singh says:

HI There,

I am trying to create a measure in which i am trying to find the MAX date from a docdate column of a sql table i.e sales-transaction between 2 specific date ranges. Basically the table has sales transaction of all the distributors & their corresponding retailers. I need to find the distinct distributor & retailer codes based on the last transaction date or highest transaction date between specified dates. I tried creating measure but got lost while applying filters for date.

Here is what i tried.
Maxdocdate= calculate(max(docdate),docdate>=01-04-2016,docdate<=31-05-2016)

can anybody enlighten me with some fruitful solution.