InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 21:37:50 GMT20http://www.sqlservercentral.com/Forums/Topic1463257-3053-1.aspxHI ,I have one column in pivot table Col1, in that column I have the data like Open, Cancle and Close, I want to display that column col1 with only Open, CancelLike Col1 NOT IN (Close)Is there any DAX function, Can you please let me know How I can get that using DAX functionThank You,GraceThu, 13 Jun 2013 12:37:31 GMTgracebitehttp://www.sqlservercentral.com/Forums/Topic1460495-3053-1.aspxHow to Count a column with conditionExample: I have to count a column A with condition value "Yes" (A="Yes") in that columnThank You,GraceWed, 05 Jun 2013 17:16:36 GMTgracebitehttp://www.sqlservercentral.com/Forums/Topic1457257-3053-1.aspxHi there,I have 2 fact table & 2 period dimension table FACT_SALES_ACTUAL : PERIOD_DAY_KEY SALES_AMOUNT FACT_SALES_BUDGET : PERIOD_MONTH_YEAR_KEY SALES_BUDGET DIM_PERIOD : PERIOD_DAY_KEY DAY_DESC MONTH_YEAR_DESC PERIOD_MONTH_YEAR_KEY YEAR_DESC DIM_MONTH_PERIOD : PERIOD_MONTH_YEAR_KEY MONTH_YEAR_DESC YEAR_DESC Where there are relation ship between FACT_SALES_ACTUAL --> DIM_PERIOD --> DIM_MONTH_PERIOD FACT_SALES_BUDGET --> DIM_MONTH_PERIODquestions :how to make a DAX query of measurement of Sales Budget so it can be displayed side by side with Sales Actual measurement, in daily basis with correct relationship of month-year ?I've tried once, bu it always show the sales Budget in every single period of day,including with unmatch period of month-year ..please helpThanksTue, 28 May 2013 04:37:53 GMToctoni 76975http://www.sqlservercentral.com/Forums/Topic1454963-3053-1.aspxGood morning to all members of this very useful site.I work with Excel 2013 and i added add-in PowerpIvot and i ma studying it.I am also interest to install PowerView add in.My goal is to use PowerView with Excel 2013 [b]connecting to Multidimensional Cubes [/b]and NOT with Tabular Models.At the moment our company where i work does not want to spend more money to have Business Intelligence or Enterprise Versione of SQL SERVER 2012...What can i do ???I read there is a new CTP for PowerView but i can use PowerView only with Exccel 2013 ???Is it possible ??? What are the steps i need to do ???I think i need to have also Sharepoint 2013 and another instance of SQL SERVER 2012..Is it not possible to use PowerView simply as PowerPivot ????I installed also SQL SERVER 2012 Enterprise Trial Edition to prepare to test something about it..Regards and many thanks to all...Tue, 21 May 2013 06:24:13 GMTmaretixhttp://www.sqlservercentral.com/Forums/Topic1454508-3053-1.aspxHi Everyone,When I try to point my PowerPivot report to the source file (by browsing to the excel file) and say - Save. Check attached Image 1 and Image 2.I get attached Error ( Image 2).ERROR : The file you have chosen is in use. Please close the file before you import.Please help.Mon, 20 May 2013 06:51:03 GMTsyedathariqbalhttp://www.sqlservercentral.com/Forums/Topic1452370-3053-1.aspxHi, Is there any function to avoid the blanks from a particular column using DAXThank You,GraceMon, 13 May 2013 18:01:38 GMTgracebitehttp://www.sqlservercentral.com/Forums/Topic1445707-3053-1.aspxCan any one please help me on deployment of Power Pivot and Power View reports How to deploy Power Pivot Data model and Power View reports from Dev system to production systemPlease let me know the stepsThanks in AdvanceThank You,GraceTue, 23 Apr 2013 15:48:36 GMTgracebitehttp://www.sqlservercentral.com/Forums/Topic1443426-3053-1.aspxPlease help me I have Dates table with 12 months in Month field, when I create relationship with my data table it showing in Month field only those months which have in my data table, but I want to display Month filed in View filter with all the 12 months, is there any DAX formula for that Can any one please let me know ThanksGraceWed, 17 Apr 2013 12:15:39 GMTgracebitehttp://www.sqlservercentral.com/Forums/Topic1443540-3053-1.aspxHi, Is any one know how to make a field to hyperlink in Card Visualization in Power View 2013.Please let me know the process.ThanksGraceWed, 17 Apr 2013 16:38:03 GMTgracebitehttp://www.sqlservercentral.com/Forums/Topic1443058-3053-1.aspxCan some one please help about Power Pivot Refresh FailureI Have created Power Pivot, I can able to create a Power View report, when I am trying to schedule a Refresh Power Pivot I am getting like "Call to Excel Services returned error" error. I have given trusted file location address and most of the needed configurationsCan you please help me outThank You,GraceTue, 16 Apr 2013 18:12:15 GMTgracebitehttp://www.sqlservercentral.com/Forums/Topic1432756-3053-1.aspxWe currently have a number of users using power pivot 2010 for self analysis however whilst power pivot 2010 is a free add-on and there are no licensing requirements (Currently don't use share point as yet), do we still need to ensure an adequate level of cal's for each of the users connecting to the server to update their pivots?Thanks,Tue, 19 Mar 2013 10:03:59 GMTT_R_Mhttp://www.sqlservercentral.com/Forums/Topic1425913-3053-1.aspxJust trying to get my head around something :What is the advantage, or reason why someone would use an SSAS datasource for PowerPivot?Is seems like PowerPivot just flattens out the data and creates a 2-D (tabular) cube.Why not always use the Datawarehouse for PowerPivot then? It seems like since the Pivot table in PowerPivot will apply it's own aggregations (sum, count, etc) to the calculated measures anyways.ThanksSat, 02 Mar 2013 19:22:13 GMThurrican3phttp://www.sqlservercentral.com/Forums/Topic1413390-3053-1.aspxI have multiple relationships, in total about 8-9 tabs of tables of data - but Im getting an issue related to text function. One tab has a column with 70% of data by name and rest blank, and I want to compare it to current HC to see which names are the same. Similar to how VLOOKUP used to or an IF, but for some reason I can't link between these tables, any ideas why?Tue, 29 Jan 2013 16:44:20 GMTikondrarhttp://www.sqlservercentral.com/Forums/Topic1407821-3053-1.aspxHi,I'm starting the course about PowerPivot Workbook in msdn, and when i'm trying to switch to Diagram View, i can't find the icon in the ruban of PowerPivot, even the measures icon doesn't exist !Plz, is it just something that i should add somewhere ? or the problem is with my PowerPivot add-in ? ( i download it from : http://www.microsoft.com/fr-fr/download/details.aspx?id=7609 )Thank you !Wed, 16 Jan 2013 07:01:21 GMTsalmitahttp://www.sqlservercentral.com/Forums/Topic1388908-3053-1.aspxCan PowerPivot 2010 Co-exist with PowerPivot 2012 for Excel 2010 ? have one 2010 installed but need to work with 2012 as well to support clients with different versions.Is there a way to install both and switch between them ?Mon, 26 Nov 2012 18:51:48 GMTimran 60091http://www.sqlservercentral.com/Forums/Topic1368643-3053-1.aspxI am using: * Windows 7 * Microsoft Office Professional Plus 2010, v14.0.6112 (32-bit) * MS SQL Server 2012 PowerPivot for Excel 32-bit, v 11.0.2100.60 * MySQL Connector/ODBC 5.2I can create an ODBC connection to MySQL with no problems. And I can even use this ODBC connection from the Data tab in Excel (not PowerPivot) to get data from Other Source, either by pulling a single table from MySQL database or editing the connection properties/definition to provide a query (command text) that joins multiple tables.However, in PowerPivot...1) If I try to get data from a SQL Server connection, I get an error: Cannot connect to the data source because the SqlServer provider is not installed2) If try to get data from a text file, I get an error: Details: Failed to connect to the server. Reason: Provider information is missing from the connection string3) If I try to create a connection From Other Source/ODBC, I get an error:ERROR [HY010] [MySQL][ODBC 5.2(w) Driver][mysqld-5.5.25a-27.1-log] [i]or[/i]Catastrophic failure. A connection could not be made.**When I click for the dropdown list of databases in the Data Link Properties, the connection is made and list is shown.On a different machine with the same versions of Office and PowerPivot and MySQL ODBC 5.1 instead of 5.2, I am able to get PowerPivot to work just fine. Note that in both the ODBC connection and Data Link in PowerPivot, the Initial Catalog must be left blank for the MySQL data pull to work. Not sure what is different on that machine to enable PowerPivot to work.Any advice appreciated!-Katherine @sqlsassyThu, 04 Oct 2012 12:48:00 GMTKatherine Fraserhttp://www.sqlservercentral.com/Forums/Topic1376059-3053-1.aspxHi all,I have been using power pivot for excel for a while now and it's working great. I wanted to publish these on sharepoint and our infrastructure guys have recently made an environment available to me. Thing is when I upload the files and view them via the Excel Web App in sharepoint I get the all the slicers and filters and whatever data i was showing but the power pivot field list has disappered and I can't find it. If I click on 'open in excel' the file opens locally and I can see the fiedl list as normal.Can somebody let me know if this is normal? i.e. you have to open it in a local copy of excel with power pivot to see the field listmany thanks..Tue, 23 Oct 2012 07:53:48 GMTk.g.mcveighhttp://www.sqlservercentral.com/Forums/Topic1343893-3053-1.aspxSuppose an organization wants to implement "self-service BI" on a large data set for a wide variety of users, e.g., financial, research, and marketing professionals in, say, the healthcare space (i.e., where the users are not techies, but use Excel, probably on minimal laptops). Each user wants to see a specific set of data, and have regular groups of dimensions (like, custom-built hospital or procedure groups) saved and accessible, against which they may do reporting. Are PowerPivot and PowerView really going to provide that self-service BI solution for them? Anyone with experience in that problem space want to comment? Thanks.Sun, 12 Aug 2012 07:45:56 GMTzintphttp://www.sqlservercentral.com/Forums/Topic1249529-3053-1.aspxHi I'm wondering what I'm missing.I'm working through a text book example of RELATED function usage. The tables across which I am implementing have a relationship, yet within the table where I've created the related column, no data is coming over.Sorry to be so general, but it there doesn't seem like there should be anything to this problem, yet the data won't populate the column. What should I be checking for besides an existing relationship between the two tables that pertain to the function?Thu, 09 Feb 2012 01:40:06 GMTxenophiliahttp://www.sqlservercentral.com/Forums/Topic1306050-3053-1.aspxI'm still trying to wrap my brain around some Power Pivot issues. If I have a Power Pivot set up against a SHARED odc connection (in the shared data sources folder in Sharepoint & configured properly, as far as I can tell) that points to a multi-dimensional SSAS cube, what is the expected behavior when I refresh (or even re-build) that cube? My understanding is the ODC is merely a pointer. Provided the changes to the cube don't affect security, it shouldn't need to be re-built, should it? What should happen if I'm re-building the cube at the moment someone runs/refreshes a Power Pivot workbook using that cube as a back-end? I'm trying to troubleshoot some nasty errors.Thu, 24 May 2012 12:20:39 GMTlduvallhttp://www.sqlservercentral.com/Forums/Topic1295737-3053-1.aspxScenario:I have a working sql query based excel report AND I have new data that I need to bring into the query which resides exclusively on an excel spreadsheet. I cannot (due to permission restrictions) create a new table in SQL Server and I can't store the excel datasource on the same server as the SQL Server instance (therefore, linked server option isn't viable) and I do not have bulkload permissions on SQL server so can't bulkload date from .csv file and I do not have permissions to reconfigure the instance for ad hoc queries (therefore Select * from OPENDATASOURCE is also not viable).Does anyone know if it is possible for a query based excel report to join on a table defined exclusively in PowerPivot Excel workspace?Sun, 06 May 2012 16:14:38 GMTpolkadothttp://www.sqlservercentral.com/Forums/Topic1208528-3053-1.aspxHow do I get rid of the automaticly showing measure in the excel/powerpivot pivottable fieldlist?"Values"___Count of ModelsMy customer doesn't want it there.Edit: Oh, forgot to mention, it's 2012/Denali CTP3.Fri, 18 Nov 2011 10:27:01 GMTJonas Bergströmhttp://www.sqlservercentral.com/Forums/Topic1206171-3053-1.aspxI was looking forward to play with this (new) toy but can't get anything working...I have installed - Office 2010 with Excel 32 bits- Sql Server 2008 R2- PowerPivot 10.50.2500.0 (the one coming with 2008R2 I think)- Sql Denali ctp3When I try to open the "PowerPivot Window" on a brand new spreadsheet, I get various error messages:- Embedded Analysis Services Engine: Could not open embedded PowerPivot data. (twice)- Then an option to "Do Nothing" or "remove the PowerPivot data"Finally, I get an error message "Unable to load the Vertipaq Engine".Any idea what I could do to fix this?Should I install PowerPivot from Denali CTP3 instead of 2008R2?I have already tried - uninstal PowerPivot- reboot- re-install PowerPivotBut no joy so far...Any idea welcome! :-)Tue, 15 Nov 2011 10:25:56 GMTEric Mamethttp://www.sqlservercentral.com/Forums/Topic1206520-3053-1.aspxNormal excel pivot tables allow the user to double-click a datum to extract the underlying data into a new worksheet. Is this available in Powerpivot at all?Wed, 16 Nov 2011 03:46:53 GMTUlysses_Infinity