How do we go about refreshing a Tabular Data model via an XMLA query? I get an XMLA query created from the SSMS UI but when it runs it does not refresh the tables or partitions.
I have used Powerpivot for excel for a customer who has only Sharepoint Standard edition.
My findings are that this is extremely powerful and interesting but I have doubts about keeping track of spreadsheet versions scattered from computer to computer.
This seems to make Powerpivot for Excel quite difficult to manage without Sharepoint Enterprise.
I feel like Powerpivot should be re-branded “BI for the masses as long as they have Sharepoint Enterprise”…
I am searching for a tool that can automatically create the ERD of an SQL database.
I’m working on a large SQL database with around 300+ tables and have problems to create the correct relationships. Does such a program exist?
Hi Kasper
I left a comment on one of you blog posts on slicers (18th of May 2011) hoever I am rather going to ask the question here in the hope of getting a quicker response .
I have linked and Excel Workbook to a SSAS datasource with a cube(MOLAP storage)
I have a date per your post on the 18th of May 2011 ,however when I choose the month for the source of a slicer ,the slicer does not display a distinct list of months ,it displays and repeats the months ( Jan ,feb,…..,Jan Feb,Mar) in that sequence repeatedly for how ever many years I have in my date dimension ,any ideas on what the reason could be ?
I would say that you have a different key value pare for you months (year / month ?), thus resulting in multiple rows for each month. What happens when you drag month onto a pivottable row ? Probably the same.
Would you happen to have any idea regarding my question or not at all ?
Question: I have imported data from a SQL database and I’m playing with them as already discussed. However I have added a field to one table (actually it’s a view) but when I refresh the table in PowerPivot the new field doesn’t show up. Is this a bug or expected behavior ?
I think you are confusing a few things. XMLA is the interface to talk to the AS engine, it has nothing to do with Querying.
To Query you can use two query languages, DAX and MDX.
for Multidimensional you can use MDX
for Tabular you can use MDX and DAX
Right now it depends on the implementation of your BISM how you can query it. if you have a multi. dim BISM you can only query it with MDX. We are working on allowing DAX for this but that will not ship with SQL 2012.
@Kasper de Jonge
OK OK my bad! I specified each column name instead of using * and the column appeared!
Not sure it’s really intuitive thought.
Thanks a lot!
>> Is PowerPivot really for the”BI for the masses”?
I was proof reading the next book from Marco Russo and I think he answered my question so I thought I’d share it with you.
He presents PowerPivot for Excel as a prototyping tool with PowerPivot for Sharepoint being the next step while Tabular in SQL 2012 would be the ultimate platform.
That’s more in line with my experience rather than “for the masses”.
Well that is kind of a philosophical question. With PowerPivot more users will have access to analytics. It depends on what scenario you are working with, in Marco’s case he is talking about Enterprise BI where it is easy for a BI pro to prototype in PowerPivot before moving to Tabular.
There are many scenario’s where I, as an IW, do want to do Personal BI. I need an answer that is only suited for me and I don’t want to share with other users. PowerPivot gives you the ability to do analytics without having to start a complete BI project.
Recently I saw a DAX solution on your blog which i am currently using and want to extend it. I am having a spread sheet ‘booking’, which records the Person name, start and end date of the person on the project schedule. I also have another spread sheet ‘Dates’ with one of its column as date.
Requirement: I need to have power pivot solution where I need to see employee names in rows and months in columns and the detail data (potentially calculated measure using DAX) should be the numbers of days in each month.
Booking
Person start end
ABC 1-Jan-11 3-Feb-11
EFG 15-Jan-11 2-Feb-11
Dates
Date Year Month WeekDay
1-Jan-08 2008 Jan Tue
2-Jan-08 2008 Jan Wed
3-Jan-08 2008 Jan Thu
4-Jan-08 2008 Jan Fri
5-Jan-08 2008 Jan Sat
6-Jan-08 2008 Jan Sun
7-Jan-08 2008 Jan Mon
Desired Output
Measure 1 Column Labels
Row Labels Jan Feb Grand Total
ABC 29 3 32
EFG 14 2 16
Grand Total 43 5 48
So far I have wrote DAX to create a calculate measure in Pivot Fields and using it as Pivot ∑ values, which gives the total number of days but does not split days total per months.
DAX:
=Calculate( sum(booking[days]),
FILTER(booking,
COUNTROWS(FILTER(VALUES(Dates[Date]),
booking[start] = Dates[Date] ))
> 0)
)
DAX current output
Measure 1 Column Labels
Row Labels Jan Feb Grand Total
ABC 32 32 32
EFG 16 16 16
Grand Total 48 48 48
Appreciate any suggesting improving the DAX function to get the desired output.
Thanks in advance
Mohammed
Problem: DAX function “Related” seems not to work when a relationship is not active.
Scenario: I want to denormalize my ledger table by adding a description field from another table to each record. The unique key between the two tables consists of two fields – a JobNo and a JobTaskNo. As you cannot join many-to-many I do created a JobTaskKey-coloumn in each table and make a relationship between the two.
Problem: If the relationship between the two tables is not “active” then the Dax function “Related” does not work. I would expect is otherwise in PowerPivot v2.
Question: is this intended or an error?
Workaround so far is to retrieve the foreign field using the Dax formula: “Lookupvalue”. However, I would imagine this solution not to be as efficient in terms of performance.
That is correct. The related function doesn’t work on multiple relationships. for now you can do:
=CalculateTable(Summarize(table2, table1[Name]), AllExcept(table2, table2[AltKey]), UseRelationship(table2[AltKey], table1[Key]))
I am facing issues in creating correct relationship. I have two tables (T1) with a single column Candidate ID (with values say C3, C4, C6). Another table (T2)also has a column as Candidate ID (with values say C1 to C10), HR Screening date and Skill Screening date. Now I would like to see the screening date values against Candidate ID mentioned in table T1.
I have successfully created the relationship between Candidate ID T1 and Candidate ID T2. However in Pivottable if I put Candidate ID T1 and HR screening date (from T2) as row labels – all the values of HR screening date becomes visible against every Candidate ID. That is the relationship is not working
Kindly help as I am sure I am missing a trick here
Regards
Hi Kasper, I’m struggling to create a running balance (closing balance per period) for Balance Sheet purposes. My PowerPivot table (‘Fin_Period x GL_Account’) includes, amongst other columns, Fin_Year, Fin_Period, Fin_Period_ID, Account_Number, Account_ID and Total_Movement (which represents the aggregate value of the movement for each Account_ID for each period). I would prefer to create a calculated column. I’ve tried a number of approaches but I just can’t get it right – the closest I got was:
Balance = CALCULATE(SUM(‘Fin_Period x GL_Account’[Total_Movement]),FILTER(ALL(‘Fin_Period x GL_Account’),’Fin_Period x GL_Account’[Financial_Period_ID]<=MAX('Fin_Period x GL_Account'[Financial_Period_ID])&&'Fin_Period x GL_Account'[Account_ID]=259),VALUES('Fin_Period x GL_Account'[Financial_Year]))
which correctly returns the Total Movement for all Period_ID's for Account_ID 259. I've now managed to confuse myself and have got stuck! Please can you give me a hand.
I have a question regarding YTD. My spreadsheet has 2 pivot tables. The 1st shows the current month activity based on a slicer selection. The 2nd shows the same activity YTD. But it always shows all the data loaded for the current year. I want to show the YTD as of the month selected for the 1st pivot table. I’d prefer not to ask the user to select using another slicer but rather to filter in DAX. Is this possible?
Problem:
I create a measure to calculate the sales value of a foreign ledger table. The problem is that the DAX formula “calculate” does not respect a current row-context field value.
Scenario:
I got the following 3 tables, table fields and measures:
Project
- Project No.
- Project Name
- Cust No. (relation to “Customer.Cust No.”)
Project Ledger
- Project No. (relation to “Project.Project No.”)
- Posting Date
- Project Cost
- Hours
- Measure: Sum of Project Cost
Scenario 1: I want to create two measures that calculates the “sales amount” for the current project (in the row context).
The Pivot structure is:
Project.Project Name
Project Ledger.Sum of Project Cost
Project Ledger.Project Sales Amount = calculate(sum(Customer Ledger.Sales Amount);Customer Ledger.Project No. = [Project No.])
The problem is related to the measure “Project Ledger.Project Sales Amount” and the notation of “[Project No.]” in the filter context. The “[Project No.] is acknowledged by the editor but evaluating the formula I get the following error (error translated from Danish): “calculation error in measure ‘Project Ledger’[c95e5aeb-e491-4e09-b1bd-32270b668e0f]: The value of ‘Project No.’ unidentified. Either the ‘Project No.’ is missing or there is no actual coloumn with the name ‘Project No.’.
If I add “Project Ledger.Project No.” to the filter context I do get an error that the expression include multiple coloumns.
Question
1) Is it intentional that the DAX Formula CALCULATE does not respect row context and thereby the record in hand?
2) How do I retrieve the the current “Project No.” in order to use it in the measure.
3) In PowerPivot v2 secondary keys can be defined. Does the definition of a secondary key change/extend the scope of filter context DAX formulas?
Question on performance for row-level calculations vs. measures.
Let’s say we have the following simple table structure:
Customer
- Cust No.
- Customer Name
Customer Ledger
- Entry No.
- Cust No. (relation to “Customer.Cust No.”)
- Posting Date
- Sales Amount
We want to calculate the “Sales Amount” for each customer. With the table structure we can easily calculate the Sales Amount follwing the row level structure or create a measure (calculate(sum(customer ledger.sales amount);Datefilter etc….)
Question 1: Will measures be calculated faster using measures opposed to following the row-level structure?
Question 2: If you apply simple filters – e.g. datefilter – will this change the precondition for question 1?
Questioni 3: when you create a measure this will be stores within an entity container – in this example Customer or Customer Ledger. Does the location of the measure impact performance – that is, will performance be faster if the measure is location at the highest level (in this example at the customer level) in the row-level hiarakie?
Question 3: In general – should all calculations be done using measures or only where Filter-context calculations apply?
Do you know if it’s possible to create a Reporting Services report using PowerPivot Data, without running a SharePoint server? I was following this article: http://technet.microsoft.com/en-us/library/gg413489.aspx, but keep failing to connect to the PowerPivot workbook. So the SharePoint server might be necessary after all, like with Power View?
Kasper, I have been using Powerpivot for a long time and also done fairly heavy SQL querying. I have setup fairly complex Powerpivot models but was stumped by the question at http://bit.ly/HmJodJ
In this example two very simple tables are linked. The Measure gets calculated correctly when the Dimension from Table2 is chosen; but not when the same (linked) Dimension is chosen from Table1. The fix suggested in the thread is easy (chose the Table2 dimension), but I fail to fathom why one works and not the other.
I am used to SQL Joins where once joined you can essentially navigate both ways. But in the Powerpivot example above the relationship seems to work one way, but not the other. Would you mind giving me a few pointers as to how the joins/relationships in PowerPivot differ than regular SQL Joins and help me understand the above example?
I am currently working on a powerpivot-prototype to use powerpivot as the bi-frontend for Dynamics NAV. Right now I am fighting a problem that arises from NAV’s data structure and my weak knowlege of powerpivot.
To explain quickly: in order to store facts and the dimensions of each fact NAV uses four tables:
– facts (columns: entry no, amount, date etc.)
– dimension codes to store all possible groupings(columns: dimension code – entries could be customer group, cost object)
- dimension values to store the possible manifestations of each grouping(column: dimension code, dimension value – entries could be customer group; europe – customer group; usa
– dimension entry (combination of dimension value and fact – colums: entry no., dimension code, dimension value)
Theoretically unlimited dimension values could be assigned to each fact.
Could you tell me how to create a simple powerpivot that would allow me to slice te data based on multiple dimensions, e.g. I would like to see the totals related to customer group = Europe and Cost Object = 10. I tried to duplicate the dimension tables in order to add two pairs of slicers but it seems that this only shows EITHER the sum for Customer Group = Europe OR the sum of Cost object = 10. What I really want is the intersection of both. Is this somehow possible or would I have to change my data structure?
Hi I am looking for guidance on how to access a cube via powerpivot through a sharepoint site and what is involved in configuring this in terms of sharepoint installs?
Basically I have a datawarehouse box with the SSIS packages and cubes. I have new build server on which I will install sharepoint 2010 so that I can access the cube from a sharepoint site from the sharepoint server.
Questions
Do I need to install analysis services and a database engine on the sharepoint box when it is installed on the datwarehouse box?
Do I need to install sharepoint 2010 on the datawarehouse box too?
How do I get the two boxes to talk to each other?
Is there any documentation/videos on what needs to be installed and configured for two server sharepoint and SSAS cube, power pivot install?
1. I have combined several tables from an SQL database with the union all statement into one table in Powerpivot. (=Table 1)
2. I have created a pivot in excel based on Table1
3. In the same excelsheet, I have created a separat exceltable with suggested corrections for Table1 (=Table2)
4. I want to add this table2 to the existing table1 in Powerpivot with the UNION ALL statement.
Is it possible to add table2 (in the same excel sheet) into table1?
I keep going over it and over it but cant seem to wrap my head around it…
Scenario: Retrieving the salary from a previous contract period:
Ian Smith; 1-jan-2009 until 10-oct-2009; 4000 euro
Ian Smith; 11-oct-2009 until 31-dec-2009; 4100 euro
I know this formula will retrieve the previous salray for each row:
=CALCULATE(MAX(‘Resource contract’[Fulltime salary]), (FILTER(‘Resource contract’, EARLIER(‘Resource contract’[ResourceKey])=’Resource contract’[ResourceKey] && EARLIER(‘Resource contract’[Contract start date]) > ‘Resource contract’[Contract start date])))
Since I dont realy understand the formula I cant validate it.. because it seems it will only work when slaries keep increasing (seeing a MAX function).
Step 2: Get the paths
=PATH(‘Resource contract’[EmploymentKey], ‘Resource contract’[ParentEmploymentkey])
Step 3: Get the the previous path value for each row
=IF(ISBLANK(LOOKUPVALUE(‘Resource contract’[Fulltime salary], ‘Resource contract’[EmploymentKey],PATHITEM([Path],PATHLENGTH([Path])-1))), 0, LOOKUPVALUE(‘Resource contract’[Fulltime salary], ‘Resource contract’[EmploymentKey],PATHITEM([Path],PATHLENGTH([Path])-1)))
This does the job for me .. I will keep on playing with EARLIER to see if I can understand how it works…
The CustomerLedger.SalesPerson holds information on the salesperson for the specific sales.
Let’s say that we have registered a Key Account Manager (KAM) on the customer record and want to calculate the personal sale for each KAM organized by customer.name.
The current row-context is therefore:
Customer.Name
CustomerLedger.Amount
Question: how can I create a filter that only includes CustomerLedger records where CustomerLedger.SalesPerson = “KAM” – that is, the KAM registered on the customer record?
.
Hi @Pongsak , any chance that one of your columns contains a binary field ? Try not selecting that column in the import wizard.
Thanks,
Kasper
Hi @Daryl Wenman-Bateson ,
Thanks for the comments. Check out this blog post by Rob collie:
http://www.powerpivotpro.com/2010/03/writing-a-rank-measure-and-living-to-tell-the-tale/
Kasper
Hi Kasper,
Waiting for your reply!
How do we go about refreshing a Tabular Data model via an XMLA query? I get an XMLA query created from the SSMS UI but when it runs it does not refresh the tables or partitions.
Is PowerPivot really for the”BI for the masses”?
I have used Powerpivot for excel for a customer who has only Sharepoint Standard edition.
My findings are that this is extremely powerful and interesting but I have doubts about keeping track of spreadsheet versions scattered from computer to computer.
This seems to make Powerpivot for Excel quite difficult to manage without Sharepoint Enterprise.
I feel like Powerpivot should be re-branded “BI for the masses as long as they have Sharepoint Enterprise”…
Or am I missing the point?
Kind Regards
Eric
Hi Kasper,
Bas told me to post the question to you.
I am searching for a tool that can automatically create the ERD of an SQL database.
I’m working on a large SQL database with around 300+ tables and have problems to create the correct relationships. Does such a program exist?
Hi Kasper
I left a comment on one of you blog posts on slicers (18th of May 2011) hoever I am rather going to ask the question here in the hope of getting a quicker response .
I have linked and Excel Workbook to a SSAS datasource with a cube(MOLAP storage)
I have a date per your post on the 18th of May 2011 ,however when I choose the month for the source of a slicer ,the slicer does not display a distinct list of months ,it displays and repeats the months ( Jan ,feb,…..,Jan Feb,Mar) in that sequence repeatedly for how ever many years I have in my date dimension ,any ideas on what the reason could be ?
Thanks for the great blog
Cedric
I would use Visio for that
Kasper
I would say that you have a different key value pare for you months (year / month ?), thus resulting in multiple rows for each month. What happens when you drag month onto a pivottable row ? Probably the same.
Kasper
you could share it to sharepoint and reuse the workbook there. see this blog by Rob: http://www.powerpivotpro.com/2011/02/powerpivot-scheduled-refresh-pt-3-thin-workbooks/
What kind of XMLA did you create ? If you do a process database you should update everything. What is the processing option you use ? ProcessFull ?
Hi..I am trying to convert the following MDX into DAX query. Any hints towards where to start or any functions to use..is helpful..
CREATE MEMBER CURRENTCUBE.[Measures].[Running QTY Issued From Store]
AS sum(
generate
(
ascendants([Fulfillment Date].[Calendar].currentmember),
iif(
[Fulfillment Date].[Calendar].currentmember IS
[Fulfillment Date].[Calendar].firstsibling,
{},
[Fulfillment Date].[Calendar].firstsibling:
[Fulfillment Date].[Calendar].prevmember
)
) + [Fulfillment Date].[Calendar].currentmember
,
[Measures].[Quantity Issued From Store]
)
Thank you,
Sandeep
Hi Kasper,
To have it clear for me:
- SSAS 2012 Traditional mode can be queried with MDX by XMLA
- SSAS 2012 Tabular mode can be queried with DAX by XMLA
- In-Memory Tabular BI Semantic Model can be queries by SQL, MDX or DAX ?
I am asking while we like to know if a SQL or OLAP Report should be used to connect to the new SSAS 2012 Tabular mode.
Marco
Hello Kasper.
Would you happen to have any idea regarding my question or not at all ?
Question: I have imported data from a SQL database and I’m playing with them as already discussed. However I have added a field to one table (actually it’s a view) but when I refresh the table in PowerPivot the new field doesn’t show up. Is this a bug or expected behavior ?
Thank you
Hi @dynek ,
I missed that. This is by design you need to go to edit table properties and add the column.
thanks,
Kasper
Hi @Marco ,
I think you are confusing a few things. XMLA is the interface to talk to the AS engine, it has nothing to do with Querying.
To Query you can use two query languages, DAX and MDX.
for Multidimensional you can use MDX
for Tabular you can use MDX and DAX
Right now it depends on the implementation of your BISM how you can query it. if you have a multi. dim BISM you can only query it with MDX. We are working on allowing DAX for this but that will not ship with SQL 2012.
Hope this helps,
Kasper
@Kasper de Jonge
I’m not sure to understand what do you mean with add the column ?
I am using a query which says:
SELECT * FROM v_myView
@Kasper de Jonge
OK OK my bad! I specified each column name instead of using * and the column appeared!
Not sure it’s really intuitive thought.
Thanks a lot!
Hi Kasper,
>> Is PowerPivot really for the”BI for the masses”?
I was proof reading the next book from Marco Russo and I think he answered my question so I thought I’d share it with you.
He presents PowerPivot for Excel as a prototyping tool with PowerPivot for Sharepoint being the next step while Tabular in SQL 2012 would be the ultimate platform.
That’s more in line with my experience rather than “for the masses”.
Cheers
Eric
Hi @dynek ,
how can any tool know there are more columns in your source if you don’t specify it ?
Thanks,
Kasper
@Kasper de Jonge
Well if I tell someone “all those gift are for you” (= select *). Should I be mentioning each of them ?
Hi @dynek ,
What I am saying is that there is nothing PowerPivot can do .. this is default SQL behavior..
Hi @Eric Mamet ,
Well that is kind of a philosophical question. With PowerPivot more users will have access to analytics. It depends on what scenario you are working with, in Marco’s case he is talking about Enterprise BI where it is easy for a BI pro to prototype in PowerPivot before moving to Tabular.
There are many scenario’s where I, as an IW, do want to do Personal BI. I need an answer that is only suited for me and I don’t want to share with other users. PowerPivot gives you the ability to do analytics without having to start a complete BI project.
Kasper
Hi Kasper
Recently I saw a DAX solution on your blog which i am currently using and want to extend it. I am having a spread sheet ‘booking’, which records the Person name, start and end date of the person on the project schedule. I also have another spread sheet ‘Dates’ with one of its column as date.
Requirement: I need to have power pivot solution where I need to see employee names in rows and months in columns and the detail data (potentially calculated measure using DAX) should be the numbers of days in each month.
Booking
Person start end
ABC 1-Jan-11 3-Feb-11
EFG 15-Jan-11 2-Feb-11
Dates
Date Year Month WeekDay
1-Jan-08 2008 Jan Tue
2-Jan-08 2008 Jan Wed
3-Jan-08 2008 Jan Thu
4-Jan-08 2008 Jan Fri
5-Jan-08 2008 Jan Sat
6-Jan-08 2008 Jan Sun
7-Jan-08 2008 Jan Mon
Desired Output
Measure 1 Column Labels
Row Labels Jan Feb Grand Total
ABC 29 3 32
EFG 14 2 16
Grand Total 43 5 48
So far I have wrote DAX to create a calculate measure in Pivot Fields and using it as Pivot ∑ values, which gives the total number of days but does not split days total per months.
DAX:
=Calculate( sum(booking[days]),
FILTER(booking,
COUNTROWS(FILTER(VALUES(Dates[Date]),
booking[start] = Dates[Date] ))
> 0)
)
DAX current output
Measure 1 Column Labels
Row Labels Jan Feb Grand Total
ABC 32 32 32
EFG 16 16 16
Grand Total 48 48 48
Appreciate any suggesting improving the DAX function to get the desired output.
Thanks in advance
Mohammed
@Kasper de Jonge
Yes, I guess I had wrong expectations initially.
But don’t get me wrong, I am a big fan of PowerPivot, I love it!
Thanks
Eric
PowerPivot v2 and the DAX method “RELATIONS”.
Problem: DAX function “Related” seems not to work when a relationship is not active.
Scenario: I want to denormalize my ledger table by adding a description field from another table to each record. The unique key between the two tables consists of two fields – a JobNo and a JobTaskNo. As you cannot join many-to-many I do created a JobTaskKey-coloumn in each table and make a relationship between the two.
Problem: If the relationship between the two tables is not “active” then the Dax function “Related” does not work. I would expect is otherwise in PowerPivot v2.
Question: is this intended or an error?
Workaround so far is to retrieve the foreign field using the Dax formula: “Lookupvalue”. However, I would imagine this solution not to be as efficient in terms of performance.
Hi @Jens Ole Taisbak ,
That is correct. The related function doesn’t work on multiple relationships. for now you can do:
=CalculateTable(Summarize(table2, table1[Name]), AllExcept(table2, table2[AltKey]), UseRelationship(table2[AltKey], table1[Key]))
Please file a bug at http://connect.microsoft.com/sqlserver to have this added to DAX
Kasper
Hi Kasper,
I am facing issues in creating correct relationship. I have two tables (T1) with a single column Candidate ID (with values say C3, C4, C6). Another table (T2)also has a column as Candidate ID (with values say C1 to C10), HR Screening date and Skill Screening date. Now I would like to see the screening date values against Candidate ID mentioned in table T1.
I have successfully created the relationship between Candidate ID T1 and Candidate ID T2. However in Pivottable if I put Candidate ID T1 and HR screening date (from T2) as row labels – all the values of HR screening date becomes visible against every Candidate ID. That is the relationship is not working
Kindly help as I am sure I am missing a trick here
Regards
Hi @Ribhu , Please make sure you also have added a measure to pivottable. Excel will not use the relationships if no measure is used.
Hope this helps.
Kasper
Hi Kasper, I’m struggling to create a running balance (closing balance per period) for Balance Sheet purposes. My PowerPivot table (‘Fin_Period x GL_Account’) includes, amongst other columns, Fin_Year, Fin_Period, Fin_Period_ID, Account_Number, Account_ID and Total_Movement (which represents the aggregate value of the movement for each Account_ID for each period). I would prefer to create a calculated column. I’ve tried a number of approaches but I just can’t get it right – the closest I got was:
Balance = CALCULATE(SUM(‘Fin_Period x GL_Account’[Total_Movement]),FILTER(ALL(‘Fin_Period x GL_Account’),’Fin_Period x GL_Account’[Financial_Period_ID]<=MAX('Fin_Period x GL_Account'[Financial_Period_ID])&&'Fin_Period x GL_Account'[Account_ID]=259),VALUES('Fin_Period x GL_Account'[Financial_Year]))
which correctly returns the Total Movement for all Period_ID's for Account_ID 259. I've now managed to confuse myself and have got stuck! Please can you give me a hand.
Thanks,
James
I have a question regarding YTD. My spreadsheet has 2 pivot tables. The 1st shows the current month activity based on a slicer selection. The 2nd shows the same activity YTD. But it always shows all the data loaded for the current year. I want to show the YTD as of the month selected for the 1st pivot table. I’d prefer not to ask the user to select using another slicer but rather to filter in DAX. Is this possible?
Problem:
I create a measure to calculate the sales value of a foreign ledger table. The problem is that the DAX formula “calculate” does not respect a current row-context field value.
Scenario:
I got the following 3 tables, table fields and measures:
Customer
- Cust No.
- Customer Name
Customer Ledger
- Entry No.
- Cust No. (relation to “Customer.Cust No.”)
- Posting Date
- Project No.
- Sales Amount
Project
- Project No.
- Project Name
- Cust No. (relation to “Customer.Cust No.”)
Project Ledger
- Project No. (relation to “Project.Project No.”)
- Posting Date
- Project Cost
- Hours
- Measure: Sum of Project Cost
Scenario 1: I want to create two measures that calculates the “sales amount” for the current project (in the row context).
The Pivot structure is:
Project.Project Name
Project Ledger.Sum of Project Cost
Project Ledger.Project Sales Amount = calculate(sum(Customer Ledger.Sales Amount);Customer Ledger.Project No. = [Project No.])
The problem is related to the measure “Project Ledger.Project Sales Amount” and the notation of “[Project No.]” in the filter context. The “[Project No.] is acknowledged by the editor but evaluating the formula I get the following error (error translated from Danish): “calculation error in measure ‘Project Ledger’[c95e5aeb-e491-4e09-b1bd-32270b668e0f]: The value of ‘Project No.’ unidentified. Either the ‘Project No.’ is missing or there is no actual coloumn with the name ‘Project No.’.
If I add “Project Ledger.Project No.” to the filter context I do get an error that the expression include multiple coloumns.
Question
1) Is it intentional that the DAX Formula CALCULATE does not respect row context and thereby the record in hand?
2) How do I retrieve the the current “Project No.” in order to use it in the measure.
3) In PowerPivot v2 secondary keys can be defined. Does the definition of a secondary key change/extend the scope of filter context DAX formulas?
Thanks for your support.
Jens Ole
Question on performance for row-level calculations vs. measures.
Let’s say we have the following simple table structure:
Customer
- Cust No.
- Customer Name
Customer Ledger
- Entry No.
- Cust No. (relation to “Customer.Cust No.”)
- Posting Date
- Sales Amount
We want to calculate the “Sales Amount” for each customer. With the table structure we can easily calculate the Sales Amount follwing the row level structure or create a measure (calculate(sum(customer ledger.sales amount);Datefilter etc….)
Question 1: Will measures be calculated faster using measures opposed to following the row-level structure?
Question 2: If you apply simple filters – e.g. datefilter – will this change the precondition for question 1?
Questioni 3: when you create a measure this will be stores within an entity container – in this example Customer or Customer Ledger. Does the location of the measure impact performance – that is, will performance be faster if the measure is location at the highest level (in this example at the customer level) in the row-level hiarakie?
Question 3: In general – should all calculations be done using measures or only where Filter-context calculations apply?
Thanks for your support.
Jens Ole
Hi Kasper,
Do you know if it’s possible to create a Reporting Services report using PowerPivot Data, without running a SharePoint server? I was following this article: http://technet.microsoft.com/en-us/library/gg413489.aspx, but keep failing to connect to the PowerPivot workbook. So the SharePoint server might be necessary after all, like with Power View?
Cheers
Ruben
Kasper, I have been using Powerpivot for a long time and also done fairly heavy SQL querying. I have setup fairly complex Powerpivot models but was stumped by the question at http://bit.ly/HmJodJ
In this example two very simple tables are linked. The Measure gets calculated correctly when the Dimension from Table2 is chosen; but not when the same (linked) Dimension is chosen from Table1. The fix suggested in the thread is easy (chose the Table2 dimension), but I fail to fathom why one works and not the other.
I am used to SQL Joins where once joined you can essentially navigate both ways. But in the Powerpivot example above the relationship seems to work one way, but not the other. Would you mind giving me a few pointers as to how the joins/relationships in PowerPivot differ than regular SQL Joins and help me understand the above example?
Thanks
-ASingh
Hi Kasper,
I am currently working on a powerpivot-prototype to use powerpivot as the bi-frontend for Dynamics NAV. Right now I am fighting a problem that arises from NAV’s data structure and my weak knowlege of powerpivot.
To explain quickly: in order to store facts and the dimensions of each fact NAV uses four tables:
– facts (columns: entry no, amount, date etc.)
– dimension codes to store all possible groupings(columns: dimension code – entries could be customer group, cost object)
- dimension values to store the possible manifestations of each grouping(column: dimension code, dimension value – entries could be customer group; europe – customer group; usa
– dimension entry (combination of dimension value and fact – colums: entry no., dimension code, dimension value)
Theoretically unlimited dimension values could be assigned to each fact.
The image here (http://social.msdn.microsoft.com/Forums/getfile/90886) shows the relationships – dim key is just the combination of dimension code and dimension value.
Could you tell me how to create a simple powerpivot that would allow me to slice te data based on multiple dimensions, e.g. I would like to see the totals related to customer group = Europe and Cost Object = 10. I tried to duplicate the dimension tables in order to add two pairs of slicers but it seems that this only shows EITHER the sum for Customer Group = Europe OR the sum of Cost object = 10. What I really want is the intersection of both. Is this somehow possible or would I have to change my data structure?
The starting point of my pivot-table looks like this: http://social.msdn.microsoft.com/Forums/getfile/90898
Thank you very much in advance!
Julian
Hi I am looking for guidance on how to access a cube via powerpivot through a sharepoint site and what is involved in configuring this in terms of sharepoint installs?
Basically I have a datawarehouse box with the SSIS packages and cubes. I have new build server on which I will install sharepoint 2010 so that I can access the cube from a sharepoint site from the sharepoint server.
Questions
Do I need to install analysis services and a database engine on the sharepoint box when it is installed on the datwarehouse box?
Do I need to install sharepoint 2010 on the datawarehouse box too?
How do I get the two boxes to talk to each other?
Is there any documentation/videos on what needs to be installed and configured for two server sharepoint and SSAS cube, power pivot install?
Thanks
Question on performance for active and inactive indexes.
This question relates to the design of the data model. Will the following 2 measure perform equally:
Sales:=sum(SalesLedger[value])
- where there is an active key from sales master tabel to the ledger table
vs.
Sales:=
calculate(
sum(SalesLedger[value]);
userelationship(SalesLedger[CustomerNo];Sale[CustomerNo))
- where the key in use is inactive.
Thanks for your support.
Jens Ole
UNION ALL
1. I have combined several tables from an SQL database with the union all statement into one table in Powerpivot. (=Table 1)
2. I have created a pivot in excel based on Table1
3. In the same excelsheet, I have created a separat exceltable with suggested corrections for Table1 (=Table2)
4. I want to add this table2 to the existing table1 in Powerpivot with the UNION ALL statement.
Is it possible to add table2 (in the same excel sheet) into table1?
How does EARLIER work?
I keep going over it and over it but cant seem to wrap my head around it…
Scenario: Retrieving the salary from a previous contract period:
Ian Smith; 1-jan-2009 until 10-oct-2009; 4000 euro
Ian Smith; 11-oct-2009 until 31-dec-2009; 4100 euro
I know this formula will retrieve the previous salray for each row:
=CALCULATE(MAX(‘Resource contract’[Fulltime salary]), (FILTER(‘Resource contract’, EARLIER(‘Resource contract’[ResourceKey])=’Resource contract’[ResourceKey] && EARLIER(‘Resource contract’[Contract start date]) > ‘Resource contract’[Contract start date])))
Since I dont realy understand the formula I cant validate it.. because it seems it will only work when slaries keep increasing (seeing a MAX function).
How does EARLIER work Kasper ?
Thank you for your time!
Kind regards Ian Smith
@Ian Smith
I have solved the problem .. without realy understanding EARLIER in combination with FITLER
Step one: Get the parent key
=CALCULATE(MAX(‘Resource contract’[EmploymentKey]),
FILTER(‘Resource contract’,
EARLIER(‘Resource contract’[ResourceKey])=’Resource contract’[ResourceKey] &&
EARLIER(‘Resource contract’[EmploymentKey]) > ‘Resource contract’[EmploymentKey]
)
)
Step 2: Get the paths
=PATH(‘Resource contract’[EmploymentKey], ‘Resource contract’[ParentEmploymentkey])
Step 3: Get the the previous path value for each row
=IF(ISBLANK(LOOKUPVALUE(‘Resource contract’[Fulltime salary], ‘Resource contract’[EmploymentKey],PATHITEM([Path],PATHLENGTH([Path])-1))), 0, LOOKUPVALUE(‘Resource contract’[Fulltime salary], ‘Resource contract’[EmploymentKey],PATHITEM([Path],PATHLENGTH([Path])-1)))
This does the job for me .. I will keep on playing with EARLIER to see if I can understand how it works…
Hi there.
In a filter context you can only work with one column in the filter expression like:
=calculate(sum(CustomerLedger.Amount);CustomerLedger.SalesPerson = “Sarah”)
The CustomerLedger.SalesPerson holds information on the salesperson for the specific sales.
Let’s say that we have registered a Key Account Manager (KAM) on the customer record and want to calculate the personal sale for each KAM organized by customer.name.
The current row-context is therefore:
Customer.Name
CustomerLedger.Amount
Question: how can I create a filter that only includes CustomerLedger records where CustomerLedger.SalesPerson = “KAM” – that is, the KAM registered on the customer record?
Thanks for your attention and support.