Kasper, I read your blog on Dutch company hosting at Amazon cloud and it does sound great. Can you give me some more points in regard to hosting asp.net, SQl server at Amazon cloud? Thanks in advance!
Hosting SQL and .net on an amazon cloud server is really easy. You just boot a server on Amazon and install SQL Server and IIS with ASP .Net. You’re good to go Are there any specifics you would like to know?
Hi Kasper, I watched your video and read some of your information.
Power Pivot will be very useful for me.
I tried the “time intelligent functions” of PowerPivot, but I have one question:
I have a source where I have the Revenues of a lot of products by Month and Year (Actual and Budget).
In the PT the row labels are “Year”, “Month” and “Product”
I was able to use your function to compute the accumulated (DATESYTD) Revenue for each month, but not by each product. So in the accumulated revenue of each product there is a summary of all products.
Of course I can make for each product a separate column (measure), but this cannot be the solution.
I think the problem you have here is that in the CTP you never should use the datetime column from the fact table in time functions. You should create a seperate date time table.
The All() function you use in the YTD makes it impossible to use slicer or row for somthing else then year and month. The All “Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied.” .
hope you can give me a good advice / solution for following problem.
I created in one Excel sheet two pivot tables that have different sources (SQL databased).
I tried to connect the 2 PT with one slicer (Year, Month) but I failed several time.
@Sascha
Hi sasha make sure you got the relationship right, also at the year / month level. Then make sure you use the year /month slicers that are from the lookup table not the fact table.
Great material on your blog – got me up to speed on PowerPivot quickly. Do you know of any examples showing how to build a cash flow statement? Something that shows by month: beginning account balance, cash flows in/out (by category/subcategory), ending balance? Is this possible within one pivot table, using the traditional formatting of a cash flow statement?
I was wondering if PowerPivot could be used in a custom Planning tool where the users require the functionality to have certain values changed in the front end and then written back to the source data?
I know this is not very common in a BI solution to allow users to change the data but for demand planning this is a requirement as the users want to see how their changes will roll up when they plan at a higher level.
Do you know if its possible to write a plug in for Excel that will wrap around PowerPivot and which could then use PowerPivot to display the data and then the custom plug in could facilitate the updating of certain records?
I’m not really sure what you can do with a custom add-in, i think it would be pretty hard in the current version of PowerPivot. According to Amir Netz we will get a API in the future. Then it will become more easy. But still, PowerPivot has no direct relation to the datasource, all data is moved inside PowerPivot so where do you want to write back to ? i guess to the original source.. A lot of things you need to solve when you want to take this approach.
Access still has custom forms and reports, you can easily create small applications without development skills.. although the last access i used was in 2000 i think a lot of users still use this.
after MS has published Office 2010 on TechNet, we changed from Beta to this version.
I can create my PT in connection with Powerpivot and it works while designing. When I open the Excle-file a second time, the PT cannot find the Powerpivot connection anymore.
Following message comes up:
Either the user, ……., does not have access to the MS_72f8a90-7f48-……. database, or the database does not exist.
When I reinstall the Beta version once again, I don’t have this problem.
@Sascha Hi Sasha,
What version of PowerPivot add-in have you got? The PowerPivot.com version is still the CTP version. You need to get the one from MSDN (and maybe technet?). I think that will solve your problem.
I’m a marketing manager (ok, we sell BI tools, but I still see myself as part of the target audience for PowerPivot) and I’m using PT to combine data from our CRM system and Google Ad Words. So far it is working great, but there’s one thing I’m struggling with.
I’m reading in daily data on number of clicks, conversions and costs. I can calculate the costs per click and conversion with a very simple DAX formula. But when my data is in a pivottable showing higher level data (e.g. per month) I can’t get the figures right. I can use the pivottable settings to either SUM or Average when aggregating, but I want it to use the DAX formula instead. Is there a way to do that in PowerPivot?
When i look at it, it should work ok out of the box. I’m not really sure what is going wrong with your calculation, looks like something else is going wrong.
A few things i can think of:
- Make sure you get the aggregated values in your formula, you could do
calculate(sum(table1[field1])) / calculate(sum(table1[field2]))
in your dax to make sure you have the right aggregations.
- Make sure you have the parts of the calculation in your pivottable as well so you can debug what part is going wrong when aggregating.
- your month aggregation isn’t what you think it is. Drag the month in and use the date fields as a level beneath the month to see if all the values you except are in the aggregation. I have seen that a date you think is under one month it really is under a different month when working with text files and localization (like dutch).
Is it going wrong every time (for all the aggregation levels) or only for Dates ?
Thanks for the quick reply. It is calculating wrong all the time. I created a very simple test with the data below (I hope it doesn’t get scrambled when I press submit).
The forumla for Costs per click is: Calculate(SUM(Example[Costs]))/Calculate(SUM(Example[Clicks]))
This is the result in the pivottable. If I remove the dates from the pivottable, I get the same totals. It looks like it is just calculating based on what is set for the measure in the pivottable. Is there a setting I need to be aware of?
Row Labels Sum of Costs Sum of Clicks Sum of Costs Per Click
1-1-2010 12 4 3
2-1-2010 15 3 5
3-1-2010 20 8 2,5
Grand Total 47 15 10,5
This is indeed very strange. Maybe you could send me your workbook? I can take a look at it looks like something strange is going on. Mail me at kasper at powerpivotblog.nl.
can I transfer a result from a slicer selection to a new column in a powerpivot table.
E.g. Selecting [Year] & [Month] & [Day] of slicers, put this in a powerpivot table column.
Backround:
I have a table of accounting records with a column [PostingDate] and a column [ClearingDate]. I want to select the open item on a date selected by slicers.
Hi @Sascha
This is somewhat possible using the Values function in DAX. This way you can use the values
in a calculated measure with DATESBETWEEN and DATESINPERIOD to get only the rows that are between the date range you want.
To recap: You want rows where Postingdate = selected [Year] & [Month] & [Day] and ClearingDate is not empty ?
I have a question not related to PowerPivot, but rather PerformancePoint and was hoping that if you don’t have the answer maybe you know somebody who does.
When setting up a PerformancePoint KPI you can specify a dimension or time intelligence filter… however, when I select more than one member to filter by in a specific dimension, or write a time intelligence formula that returns more than one member (i.e. a range of dates), the KPI simply displays “Error”. Strangely it does work if you put the time intelligence in a filter, and then connect the dashboard to it instead. I can’t work around the problem in that manner because I need to create a balanced scorecard with data from all areas of business.
Am I doing something wrong? Why can’t you create a KPI and specifiy 3 different members of a time Dimension in the dimension filter and not have it give an error?
I have created a pivot table to see only the data of selected and previous year by month.
Problem:
I have actual date till 06-2010. When creating my T-I-F with “previous year”, I can see the data of last year’s monthes 01 to 06, but not 07 to 12 because actual monthes 07 to 12 are not yet filled, I guess.
I checked your post about open items with slicer collection.
I try to rebuild your examplbe 1 by 1. Unfortunatelly I cannot create the Measure 1 in the pivot table. I always get errors like wrong “;” or wrong “=”.
I would like come back to your great post of “open items”
I made now 3 excel-tables for year, month and day and insert them as linked tables to powerpivot. Then I made a measurement of each and connected them via DATE(). This date was then linked once again to a powerpivot table. I transfered the date then to my target powerpivot table as calculation basis.
All works fine. Problem: When I’m making changes in my slicers, my powerpivot tables updating the data of the linked tables only when I go into them.
Is there any possiblility to update such data, comming from linked excel sheets automatically?
I have tried to import data from firebird database to PT. I have used ODBC driver (version 2.00.00.148). I am getting the following error :
ERROR [HY000] [ODBC Firebird Driver][Firebird]Dynamic SQL Error
SQL error code = -104
Token unknown – line 1, column 10
The import wizard lists all the tables in database but no success importing data.
I have to emphasize that I can import data using standard procedure for Excel (i.e. importing data from external source using above mentioned driver).
I will appreciate any advise how to solve the problem.
If I use PowerPivot to create a report using a data not from some external source but from within excel, and then give this file to somebody else WITHOUT PowerPivot installed, will it work and be displayed properly?
In other words, will files created using PowerPivot work on users without PowerPivot installed OR do they need to install it as well (I dont want that)?
Thanks!
Hi @Sascha
Why do you have 3 tables for year, month and day? I should recommend one table for all dates like in the contoso sample database. What do you mean update the linked table ? data coming from the linked tables is not updated ?
Hi Kasper,
3 tables because I want to create the date easy by selecting the year, month and day separatly. A table with date would be very large!
I have formed the the date by picking up the result of year, month and day selection, linked this result to a new powerpivot table “date”.
For my calculation in my “open items” table, I put in a new column (=sum(DaySlicer[DaySlicer]) to compare with the clearingdate and the postingdate of each line-item.
All this works fine. Only the update of new slicer selection (means new result) does not work.
I’m currently writing an article about PowerPivot and I have used the text function SEARCH() in my sample. While doing this I noticed that wildcards seems to be allowed. For example if I do something like this: SEARCH(“google.*/search”,[Referer],1), then google.de/search or google.com.au/search are found. The problem now is that I haved searche the documentation of the DAX functions (TechNet, …) for getting sure that wildcards are allowed. Unfortunately I did not find clear official statements about this. Do you perhaps know if wildcards are really supported or perhaps know some links where I can get more infos. Or am I completely wrong?
I am trying to calculate a covariance in PowerPivot. Normally I would simply use the Covariance.P-function in Excel but since I’m currently working with large data sets, it would be much more convinient to calculate this directly in PowerPivot. Unfortunately, PowerPivot (DAX) doesn’t seem to have any built-in covariance calculation options.
I have two questions and I would very much appreciate it if you could answer them.
1) Do you know any easy way to calculate covariance directly in PowerPivot?
2) If not, I could use some formulas to calculate the covariance manually in PowerPivot. But then I would have to filter a great deal of data in order to only count the rows I am interested in. The FILTER function seems to be a good way to do this. What I can’t figure out though is how the FILTER-syntax works. The Microsoft help page examples are insufficient, at least for me.
For example, if I have a table named “Process_value” and a column named “UBAS” and some numbers there, say “10″, “16″ and “200″, and I wish to use the “SUM”-function to sum only the “200″ rows, what is then the correct syntax to do this?
If I try things like:
=SUM(Process_value[UBAS], FILTER([UBAS]>199))
or similar syntaxes, I get only errors.
I would very much appreciate any help you can give me.
1) I don’t know of a way to calculate a covariance in PowerPivot, sorry.
2) I would use the calculate function, this would work:
=CALCULATE(sum(Process_value[UBAS), Process_value[UBAS > 199)
Calculate is your friend in many situations
For our group company needs, Pivot is not at its limits regarding reading Records (you can handle it within prequeries), but regarding showing them: after ca. 4000 – 8000 Rows it’s finished. No of Page Fields and Column Fields are also no problem for us. But : The more RowHeader you use in Pivot the less Rows you can manage in a PivotTable.
-> Do we also have still the same restrictions regarding “number of Rowheader” if we like to show more than 4000 Rows?
Hi Bernhard @Bernhard
Working with data in PowerPivot is different than in Excel. The data is actually stored inside PowerPivot and not excel. The rowcount limit that is inside Excel is still there if you want to show them all. Although it should be more than 4000 – 8000 rows.
I don’t know what you would like this huge table inside excel for? PowerPivot is meant to do aggregations over data so you don’t need these huge tables to be showable to the end user. Only the aggregations are shown inside the powerpivottable.
I am trying to figure out how to pull the value/parameters from the slicers in a powerpivot report. The App I am building displays a report from SharePoint. One of the requirements is that the user has the ability to save his Preferences. The preferences are the values selected in the slicers. The next time the report opens for that user we need to read the prefernces and load the report the way he saved it. I can’t figure out how to get the values. I know I can use SetParameters to load them. Do you have any suggestions?
I am trying to use Dashboard Designer using SharePoint 2010. I went through your blog on “Creating a PerformancePoint 2010 dashboard on a published SharePoint PowerPivot app”. After I’ve clicked “start using PerformancePoint Services”, on the page where I need to click “Run DashBoard Designer” I get the error that the Application can’t be retrieved. Can you please help?
Based on what I know, I believe what you are trying to do is nearly impossible. You would need to reverse-engineer and probably modify a great deal of Excel Web Access’s client-side script, which is a monumental task.
This is a VERY common request however, and I wish we at Pivotstream had this capability. Let me know if you stumble on something
.
Kasper, I read your blog on Dutch company hosting at Amazon cloud and it does sound great. Can you give me some more points in regard to hosting asp.net, SQl server at Amazon cloud? Thanks in advance!
Hi Michael,
Hosting SQL and .net on an amazon cloud server is really easy. You just boot a server on Amazon and install SQL Server and IIS with ASP .Net. You’re good to go
Are there any specifics you would like to know?
Hi Kasper, I watched your video and read some of your information.
Power Pivot will be very useful for me.
I tried the “time intelligent functions” of PowerPivot, but I have one question:
I have a source where I have the Revenues of a lot of products by Month and Year (Actual and Budget).
In the PT the row labels are “Year”, “Month” and “Product”
I was able to use your function to compute the accumulated (DATESYTD) Revenue for each month, but not by each product. So in the accumulated revenue of each product there is a summary of all products.
Of course I can make for each product a separate column (measure), but this cannot be the solution.
Could you let me know how this is solved?
Regards Sascha
Hi @Sascha ,
I think the problem you have here is that in the CTP you never should use the datetime column from the fact table in time functions. You should create a seperate date time table.
The All() function you use in the YTD makes it impossible to use slicer or row for somthing else then year and month. The All “Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied.” .
I hope this helps.
Kasper
@Kasper de Jonge
Hi Kasper,
thanks for your quick response!
I found out that the function ALLEXCEPT works for me perfekt!
What an incredible tool!!! No longer programming table in table views via SQL
Regards Sascha
Hi Kasper,
hope you can give me a good advice / solution for following problem.
I created in one Excel sheet two pivot tables that have different sources (SQL databased).
I tried to connect the 2 PT with one slicer (Year, Month) but I failed several time.
What could be the reason?
Rg. Sascha
@Sascha
Hi Sasha,
Check out: http://www.powerpivotblog.nl/add-a-new-pivottable-or-pivotchart-and-reuse-the-slicers-of-an-existing-powerpivot-workbook I think this will work.
Kasper
Hi Kasper,
thanks for quick response!
Especially this procedure does not work!
Is this problem related to different sources?
Rg. Sascha
@Sascha
Hi sasha make sure you got the relationship right, also at the year / month level. Then make sure you use the year /month slicers that are from the lookup table not the fact table.
Kasper,
Great material on your blog – got me up to speed on PowerPivot quickly. Do you know of any examples showing how to build a cash flow statement? Something that shows by month: beginning account balance, cash flows in/out (by category/subcategory), ending balance? Is this possible within one pivot table, using the traditional formatting of a cash flow statement?
Thanks,
Greg
@Greg
Hi Greg,
Thanks
I don’t know of an existing sample. But i have an idea how to fix this.
Is this what you would like:
http://www.fieldstonealliance.org/client/client_images/cash_flow.jpg ?
I can make a sample in a blogpost
Kasper
Hi Kasper
I was wondering if PowerPivot could be used in a custom Planning tool where the users require the functionality to have certain values changed in the front end and then written back to the source data?
I know this is not very common in a BI solution to allow users to change the data but for demand planning this is a requirement as the users want to see how their changes will roll up when they plan at a higher level.
Do you know if its possible to write a plug in for Excel that will wrap around PowerPivot and which could then use PowerPivot to display the data and then the custom plug in could facilitate the updating of certain records?
You advice is as always very much appreciated.
Kind Regards,
Christo
@Greg
Hi Greg,
I hope this is what you mean:
http://www.powerpivotblog.nl/building-a-cash-flow-statement-in-powerpivot-using-dynamic-measures-in-dax
Kasper
remind me, kind of a tangent, but what is is the use case now for access?…with powerpivot & sql express available
Hi @Christo Olivier ,
I’m not really sure what you can do with a custom add-in, i think it would be pretty hard in the current version of PowerPivot. According to Amir Netz we will get a API in the future. Then it will become more easy. But still, PowerPivot has no direct relation to the datasource, all data is moved inside PowerPivot so where do you want to write back to ? i guess to the original source.. A lot of things you need to solve when you want to take this approach.
Kasper
Hi @greg kramer ,
Access still has custom forms and reports, you can easily create small applications without development skills.. although the last access i used was in 2000 i think a lot of users still use this.
Kasper
Hi Kasper,
after MS has published Office 2010 on TechNet, we changed from Beta to this version.
I can create my PT in connection with Powerpivot and it works while designing. When I open the Excle-file a second time, the PT cannot find the Powerpivot connection anymore.
Following message comes up:
Either the user, ……., does not have access to the MS_72f8a90-7f48-……. database, or the database does not exist.
When I reinstall the Beta version once again, I don’t have this problem.
Have you heard from such a problem?
Regards Sascha
@Sascha Hi Sasha,
What version of PowerPivot add-in have you got? The PowerPivot.com version is still the CTP version. You need to get the one from MSDN (and maybe technet?). I think that will solve your problem.
Kasper
Hi Kasper,
thanks for your quick respons.
We tried to find different release on MSDN and Technet, but the only thing we found is the same version as published on PowerPivot.com!
Nevertheless we installed the version of MSDN, but failed again.
Regards Sascha
Hi Kasper,
I’m a marketing manager (ok, we sell BI tools, but I still see myself as part of the target audience for PowerPivot) and I’m using PT to combine data from our CRM system and Google Ad Words. So far it is working great, but there’s one thing I’m struggling with.
I’m reading in daily data on number of clicks, conversions and costs. I can calculate the costs per click and conversion with a very simple DAX formula. But when my data is in a pivottable showing higher level data (e.g. per month) I can’t get the figures right. I can use the pivottable settings to either SUM or Average when aggregating, but I want it to use the DAX formula instead. Is there a way to do that in PowerPivot?
Thanks,
Jan
Hi @Jan van Dijk ,
When i look at it, it should work ok out of the box. I’m not really sure what is going wrong with your calculation, looks like something else is going wrong.
A few things i can think of:
- Make sure you get the aggregated values in your formula, you could do
calculate(sum(table1[field1])) / calculate(sum(table1[field2]))
in your dax to make sure you have the right aggregations.
- Make sure you have the parts of the calculation in your pivottable as well so you can debug what part is going wrong when aggregating.
- your month aggregation isn’t what you think it is. Drag the month in and use the date fields as a level beneath the month to see if all the values you except are in the aggregation. I have seen that a date you think is under one month it really is under a different month when working with text files and localization (like dutch).
Is it going wrong every time (for all the aggregation levels) or only for Dates ?
I hope i gave you a hint in the right way.
Kasper
Hi Kasper,
Thanks for the quick reply. It is calculating wrong all the time. I created a very simple test with the data below (I hope it doesn’t get scrambled when I press submit).
The forumla for Costs per click is: Calculate(SUM(Example[Costs]))/Calculate(SUM(Example[Clicks]))
This is the result in the pivottable. If I remove the dates from the pivottable, I get the same totals. It looks like it is just calculating based on what is set for the measure in the pivottable. Is there a setting I need to be aware of?
Row Labels Sum of Costs Sum of Clicks Sum of Costs Per Click
1-1-2010 12 4 3
2-1-2010 15 3 5
3-1-2010 20 8 2,5
Grand Total 47 15 10,5
Thanks,
Jan
Hi @Jan van Dijk ,
This is indeed very strange. Maybe you could send me your workbook? I can take a look at it looks like something strange is going on. Mail me at kasper at powerpivotblog.nl.
Kasper
Hi Kasper,
can I transfer a result from a slicer selection to a new column in a powerpivot table.
E.g. Selecting [Year] & [Month] & [Day] of slicers, put this in a powerpivot table column.
Backround:
I have a table of accounting records with a column [PostingDate] and a column [ClearingDate]. I want to select the open item on a date selected by slicers.
Regards Sascha
Hi @Sascha
This is somewhat possible using the Values function in DAX. This way you can use the values
in a calculated measure with DATESBETWEEN and DATESINPERIOD to get only the rows that are between the date range you want.
To recap: You want rows where Postingdate = selected [Year] & [Month] & [Day] and ClearingDate is not empty ?
Hi Kasper,
thanks for the answer, but how to define the date I want to see as “open items”.
E.g. Open Items by 25.05.2010
Item 1: Posting Date: 20.05.2010 Clearingdate: 28.05.2010 Value 100 EUR
Item 2: Posting Date: 22.05.2010 Clearingdate: 27.05.2010 Value 200 EUR
Item 3: Posting Date: 23.05.2010 Clearingdate: 24.05.2010 Value 300 EUR
Result will be 300 EUR (Item 1 + 2) Postingdate 25.05.2010 OR Clearingdate = 00.00.0000).
But how to select the 25.05.2010 for this calculation. I have not a date like 25.05.2010 in my Pivottable?
Rg. Sascha
Hi @Sascha ,
Ok now i understand your question. I have an idea how to fix this but i am definitely not sure, i’ll try tonight and make a blog post about it when it works
Check: http://www.powerpivotblog.nl/use-slicer-values-in-a-calculation-with-powerpivot-dax
Hi Kasper,
I have a question not related to PowerPivot, but rather PerformancePoint and was hoping that if you don’t have the answer maybe you know somebody who does.
When setting up a PerformancePoint KPI you can specify a dimension or time intelligence filter… however, when I select more than one member to filter by in a specific dimension, or write a time intelligence formula that returns more than one member (i.e. a range of dates), the KPI simply displays “Error”. Strangely it does work if you put the time intelligence in a filter, and then connect the dashboard to it instead. I can’t work around the problem in that manner because I need to create a balanced scorecard with data from all areas of business.
Am I doing something wrong? Why can’t you create a KPI and specifiy 3 different members of a time Dimension in the dimension filter and not have it give an error?
I’d really appreciate any input.
Hi Kasper,
regarding Time-Intelligence-Function: PreviousYear
I have created a pivot table to see only the data of selected and previous year by month.
Problem:
I have actual date till 06-2010. When creating my T-I-F with “previous year”, I can see the data of last year’s monthes 01 to 06, but not 07 to 12 because actual monthes 07 to 12 are not yet filled, I guess.
Do you have any idea how to solve?
Regards
Sascha
Hi Kasper,
I checked your post about open items with slicer collection.
I try to rebuild your examplbe 1 by 1. Unfortunatelly I cannot create the Measure 1 in the pivot table. I always get errors like wrong “;” or wrong “=”.
What do I wrong?
Regards Sascha
Hi Sasha, Check the blog post again. WordPress made my < signs look all strange so the functions is now ok.
Hi Kasper,
I would like come back to your great post of “open items”
I made now 3 excel-tables for year, month and day and insert them as linked tables to powerpivot. Then I made a measurement of each and connected them via DATE(). This date was then linked once again to a powerpivot table. I transfered the date then to my target powerpivot table as calculation basis.
All works fine. Problem: When I’m making changes in my slicers, my powerpivot tables updating the data of the linked tables only when I go into them.
Is there any possiblility to update such data, comming from linked excel sheets automatically?
Regards Sascha
Hello Kasper,
I have tried to import data from firebird database to PT. I have used ODBC driver (version 2.00.00.148). I am getting the following error :
ERROR [HY000] [ODBC Firebird Driver][Firebird]Dynamic SQL Error
SQL error code = -104
Token unknown – line 1, column 10
The import wizard lists all the tables in database but no success importing data.
I have to emphasize that I can import data using standard procedure for Excel (i.e. importing data from external source using above mentioned driver).
I will appreciate any advise how to solve the problem.
best regards,
Andrzej
If I use PowerPivot to create a report using a data not from some external source but from within excel, and then give this file to somebody else WITHOUT PowerPivot installed, will it work and be displayed properly?
In other words, will files created using PowerPivot work on users without PowerPivot installed OR do they need to install it as well (I dont want that)?
Thanks!
Hi @adamas , when you do not have installed the plug in you can see the data but not work with the slicers or anything else dynamic.
Hi @Sascha
Why do you have 3 tables for year, month and day? I should recommend one table for all dates like in the contoso sample database. What do you mean update the linked table ? data coming from the linked tables is not updated ?
Hi Kasper,
3 tables because I want to create the date easy by selecting the year, month and day separatly. A table with date would be very large!
I have formed the the date by picking up the result of year, month and day selection, linked this result to a new powerpivot table “date”.
For my calculation in my “open items” table, I put in a new column (=sum(DaySlicer[DaySlicer]) to compare with the clearingdate and the postingdate of each line-item.
All this works fine. Only the update of new slicer selection (means new result) does not work.
If helpfull I can send you the file.
Regards Sascha
Hi Kasper,
I’m currently writing an article about PowerPivot and I have used the text function SEARCH() in my sample. While doing this I noticed that wildcards seems to be allowed. For example if I do something like this: SEARCH(“google.*/search”,[Referer],1), then google.de/search or google.com.au/search are found. The problem now is that I haved searche the documentation of the DAX functions (TechNet, …) for getting sure that wildcards are allowed. Unfortunately I did not find clear official statements about this. Do you perhaps know if wildcards are really supported or perhaps know some links where I can get more infos. Or am I completely wrong?
Thanks in advance for any help.
Regards
Hi @Sascha,
I still would recommend using a single date table, check out this blog post for a easy way to create a single table:
http://www.powerpivotblog.nl/calling-a-stored-procedure-in-powerpivot-to-populate-your-date-table
you can send me you file at kasper at powerpivotblog.nl and ill take a look at it.
Hi Mourad, im currently working on a blog post that will explain your question, keep posted !
Greetings Kasper!
I am trying to calculate a covariance in PowerPivot. Normally I would simply use the Covariance.P-function in Excel but since I’m currently working with large data sets, it would be much more convinient to calculate this directly in PowerPivot. Unfortunately, PowerPivot (DAX) doesn’t seem to have any built-in covariance calculation options.
I have two questions and I would very much appreciate it if you could answer them.
1) Do you know any easy way to calculate covariance directly in PowerPivot?
2) If not, I could use some formulas to calculate the covariance manually in PowerPivot. But then I would have to filter a great deal of data in order to only count the rows I am interested in. The FILTER function seems to be a good way to do this. What I can’t figure out though is how the FILTER-syntax works. The Microsoft help page examples are insufficient, at least for me.
For example, if I have a table named “Process_value” and a column named “UBAS” and some numbers there, say “10″, “16″ and “200″, and I wish to use the “SUM”-function to sum only the “200″ rows, what is then the correct syntax to do this?
If I try things like:
=SUM(Process_value[UBAS], FILTER([UBAS]>199))
or similar syntaxes, I get only errors.
I would very much appreciate any help you can give me.
Thanks in advance,
Daniel
Hi @Daniel ,
1) I don’t know of a way to calculate a covariance in PowerPivot, sorry.
2) I would use the calculate function, this would work:
=CALCULATE(sum(Process_value[UBAS), Process_value[UBAS > 199)
Calculate is your friend in many situations
Kasper
Hi @Kasper: thanks in advance, I’m curious
It’s there check it out at: http://www.powerpivotblog.nl/using-the-excel-function-search-in-powerpivot-dax
For our group company needs, Pivot is not at its limits regarding reading Records (you can handle it within prequeries), but regarding showing them: after ca. 4000 – 8000 Rows it’s finished. No of Page Fields and Column Fields are also no problem for us. But : The more RowHeader you use in Pivot the less Rows you can manage in a PivotTable.
-> Do we also have still the same restrictions regarding “number of Rowheader” if we like to show more than 4000 Rows?
Hi Bernhard @Bernhard
Working with data in PowerPivot is different than in Excel. The data is actually stored inside PowerPivot and not excel. The rowcount limit that is inside Excel is still there if you want to show them all. Although it should be more than 4000 – 8000 rows.
I don’t know what you would like this huge table inside excel for? PowerPivot is meant to do aggregations over data so you don’t need these huge tables to be showable to the end user. Only the aggregations are shown inside the powerpivottable.
Hi,
I am trying to figure out how to pull the value/parameters from the slicers in a powerpivot report. The App I am building displays a report from SharePoint. One of the requirements is that the user has the ability to save his Preferences. The preferences are the values selected in the slicers. The next time the report opens for that user we need to read the prefernces and load the report the way he saved it. I can’t figure out how to get the values. I know I can use SetParameters to load them. Do you have any suggestions?
Thanks,
Rhonda
Hi Kasper,
I am trying to use Dashboard Designer using SharePoint 2010. I went through your blog on “Creating a PerformancePoint 2010 dashboard on a published SharePoint PowerPivot app”. After I’ve clicked “start using PerformancePoint Services”, on the page where I need to click “Run DashBoard Designer” I get the error that the Application can’t be retrieved. Can you please help?
Thanks,
Petunia
@Rhonda
Based on what I know, I believe what you are trying to do is nearly impossible. You would need to reverse-engineer and probably modify a great deal of Excel Web Access’s client-side script, which is a monumental task.
This is a VERY common request however, and I wish we at Pivotstream had this capability. Let me know if you stumble on something
-rob
Hi @Petunia,
Your error looks like a misconfiguration in the PerformancePoint web application or even setup. Check out your configuration using this blog post:
http://blogs.msdn.com/b/performancepoint/archive/2009/11/24/deploying-performancepoint-2010-soup-to-nuts.aspx
Also try out to read the log files:
http://social.msdn.microsoft.com/Forums/en/sharepoint2010general/thread/d42b11cb-54c1-4c45-bfba-acf3fd486717
Kasper