Power/Factorial Not Working
May 4, 2006I am trying to get the value, 3 to the 2.2 power (3^2.222).
Can someone tell me how to do it? The literature says to use:
POWER(3,2.222)
however ths is rounding my values for some reason.
ADVERTISEMENT
Power Pivot :: Slicers And Timeline Not Working Together
Sep 28, 2015I have a boolean calculated column on my date table called 'Is Weekend?' that, unfortunately, doesn't work at all when sliced in combination with a Timeline slicer that's applied to the same date table.
For example: selecting an item from my 'Is Weekend?' slicer will reset any filters on the Timeline slicer. Similarly, selecting an item from my Timeline slicer will reset any filters on my 'Is Weekend?' slicer.
This appears to be the case for all filters applied to the date table, i.e. the Timeline slicer will reset them and they will reset the Timeline slicer.
Factorial Permutations, Without A Cursor?
May 15, 2006You folks always tell me to do as much as I can with set based operations and get rid of those damn cursors, so that is what I would like to do, but I can't really see any way to get around it.
What I have to do is generate all the permutations for 6 values without having any values repeated in each combination (6 factorial, 6x5x4x3x2x1=720 permutations).
Currently I have this job that takes 1-3 seconds to run.
ALTER PROCEDURE [dbo].[proc_SAHSGenComb2] AS
DECLARE @pos1 INT
DECLARE @pos2 INT
DECLARE @pos3 INT
DECLARE @pos4 INT
DECLARE @pos5 INT
DECLARE @pos6 INT
DECLARE @currID INT
DECLARE @currSort SMALLINT
DECLARE @combID INT
DECLARE curSeq CURSOR FAST_FORWARD FOR
SELECT WKLD_ID FROM TSA_HS_WKLD
ORDER BY HUMP_CUT_ORD
OPEN curSeq
FETCH NEXT FROM curSeq INTO @currID
SET @currSort = 1
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO TSA_HS_SEQ (WKLD_ID, CURR_SEQ) VALUES (@currID, @currSort)
SET @currSort = @currSort + 1
FETCH NEXT FROM curSeq INTO @currID
END
CLOSE curSeq
DEALLOCATE curSeq
SET @combID = 1
DECLARE curPos1 CURSOR FAST_FORWARD FOR
SELECT WKLD_ID FROM TSA_HS_SEQ
WHERE CURR_SEQ <= 6 ORDER BY CURR_SEQ
OPEN curPos1
FETCH NEXT FROM curPos1 INTO @pos1
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE curPos2 CURSOR FAST_FORWARD FOR
SELECT WKLD_ID FROM TSA_HS_SEQ WHERE WKLD_ID <> @pos1 AND CURR_SEQ <= 6 ORDER BY CURR_SEQ
OPEN curPos2
FETCH NEXT FROM curPos2 INTO @pos2
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE curPos3 CURSOR FAST_FORWARD FOR
SELECT WKLD_ID FROM TSA_HS_SEQ WHERE WKLD_ID NOT IN (@pos1,@pos2) AND CURR_SEQ <= 6 ORDER BY CURR_SEQ
OPEN curPos3
FETCH NEXT FROM curPos3 INTO @pos3
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE curPos4 CURSOR FAST_FORWARD FOR
SELECT WKLD_ID FROM TSA_HS_SEQ WHERE WKLD_ID NOT IN (@pos1,@pos2,@pos3) AND CURR_SEQ <= 6 ORDER BY CURR_SEQ
OPEN curPos4
FETCH NEXT FROM curPos4 INTO @pos4
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE curPos5 CURSOR FAST_FORWARD FOR
SELECT WKLD_ID FROM TSA_HS_SEQ WHERE WKLD_ID NOT IN (@pos1,@pos2,@pos3,@pos4) AND CURR_SEQ <= 6 ORDER BY CURR_SEQ
OPEN curPos5
FETCH NEXT FROM curPos5 INTO @pos5
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE curPos6 CURSOR FAST_FORWARD FOR
SELECT WKLD_ID FROM TSA_HS_SEQ WHERE WKLD_ID NOT IN (@pos1,@pos2,@pos3,@pos4,@pos5) AND CURR_SEQ <= 6 ORDER BY CURR_SEQ
OPEN curPos6
FETCH NEXT FROM curPos6 INTO @pos6
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRAN t1
INSERT INTO TSA_HS_COMB2 (COMB_ID, WKLD_ID, WKLD_SEQ) VALUES (@combID, @pos1, 1)
INSERT INTO TSA_HS_COMB2 (COMB_ID, WKLD_ID, WKLD_SEQ) VALUES (@combID, @pos2, 2)
INSERT INTO TSA_HS_COMB2 (COMB_ID, WKLD_ID, WKLD_SEQ) VALUES (@combID, @pos3, 3)
INSERT INTO TSA_HS_COMB2 (COMB_ID, WKLD_ID, WKLD_SEQ) VALUES (@combID, @pos4, 4)
INSERT INTO TSA_HS_COMB2 (COMB_ID, WKLD_ID, WKLD_SEQ) VALUES (@combID, @pos5, 5)
INSERT INTO TSA_HS_COMB2 (COMB_ID, WKLD_ID, WKLD_SEQ) VALUES (@combID, @pos6, 6)
COMMIT TRAN t1
SET @combID = @combID + 1
FETCH NEXT FROM curPos6 INTO @pos6
END
CLOSE curPos6
DEALLOCATE curPos6
FETCH NEXT FROM curPos5 INTO @pos5
END
CLOSE curPos5
DEALLOCATE curPos5
FETCH NEXT FROM curPos4 INTO @pos4
END
CLOSE curPos4
DEALLOCATE curPos4
FETCH NEXT FROM curPos3 INTO @pos3
END
CLOSE curPos3
DEALLOCATE curPos3
FETCH NEXT FROM curPos2 INTO @pos2
END
CLOSE curPos2
DEALLOCATE curPos2
FETCH NEXT FROM curPos1 INTO @pos1
END
CLOSE curPos1
DEALLOCATE curPos1
If anybuddy's got any bright ideas on how to retire these cursors, I would love to hear it. Oh ya, AFAIK, the explicit transactions I added don't do a damn thing as far as imrpoving performance.
Thanks again,
Carl
Power Pivot :: Show Images In Power View With Power Query On Excel Desktop Version / Office 365
Aug 2, 2015I am trying to show images in a product listing in power view.I work with an excel 2013 desktop version based on an office 365 pro account.I did the following steps:
import of an excel file with an article list via power query and loading the data to the data model import jpg images from a folder via power query, setting content as binary type and loading the data to the data modellinking both tables in power pivot--> manage via the image namesetting the table behavior for the images table under power pivot --> manage --> Advanced (e.g. Default Image: Content)opening power view and building article cards with article number and imageProblem: only a camera icon shows up in power view
Is there a solution with a desktop version?Can I use my Office 365 Pro account to make it work? How?Why is there no solution showing images in a pivot table?Link to Dropbox with power pivot files
Power Pivot :: Create Power View Report - Button Does Not Show Up For Some Reports
Nov 3, 2015I have several reports in a Power View Gallery. In Gallery view, most of the reports show the "Open New Excel Workbook", the "Create Power View Report", and the "Manage Data Refresh" buttons on the right side of the report list. Why would some reports not have these buttons available? In the attached image you can see one report with the buttons and one without the buttons.
View 5 Replies View RelatedPower Pivot :: Power Query Error Expression / Cannot Convert The Value To Type Text
Jul 20, 2015I've imported a number of excel sheets into a Power Query Table. All seems to appear ok until I load the data. Of the 15k rows around 2k have a similar error where it cannot convert an integer to type string as below example
Expression.Error: We cannot convert the value 40 to type Text.
Details:
Value=40
Type=Type
The columns in question are all of integer type, I've looked through the M query and there is no conversion to string taking placeThe values where we don't get the error are also integers hence the intriguing question is why does the error occur on a subset and not the others. I suspect there is a limit to the number of errors also somewhere internally M query is converting the column to text for some reason.
Power Pivot :: IF In Power Query Statement - Converting Text To Numbers
Nov 18, 2015Slow loading issue with an if statement. In the raw data the field [Location] is a text field e.g. 0010. I have a parameterised query that get a Location_Value from Excel and passes it to the PQ query using:
#"Filtered Rows1" = Table.SelectRows(#"Removed Other Columns", each ([SalesMode] = 0) and ([SalesType] = 0) and ([Location] = Location_Value))
This works fine if you chose a single location. However I wanted to be able to select all locations and text is horrible to work with so in PQ I used the change type function to change the location column into whole numbers. I changed excel to also pass a number as Location_Value. I was therefore surprised when the same query took 2.5 times longer to refresh????
My PQ now looks like this
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Location", Int64.Type}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([SalesMode] = 0) and ([SalesType] = 0) and ([Location] = Location_Value))
I'm wondering if I need to do something to the ([Location] = Location_Value) bit as maybe it still thinks [Loation] is text and it is trying to compare it to a number. I st assumed the step above meant that [Location] was now a number, but maybe you still have wrap it with some kind of VALUES or TEXT function?
Power Point :: How To Generate Rank Based On Some Group In Power Pivot
Apr 10, 2015I have data like below
Country State Rank
India Kerala 1
India Kerala 2
India Kerala 3
India Tamil Nadu 1
India Tamil Nadu 2
India Orissa 1
India Orissa 2
US Florida 1
US Florida 2
US NewYork 1
I have to generate rank like this in power pivot. How can I achieve it?
Power Pivot :: Error When Updating From Power Query Source
Jun 11, 2015When updating a Power Query Source in Power Pivot, I'm getting the following error message:
Basically saying that OLE DB or ODBC-error occured when:
- Connections have been imported from a different workbook or
- the workbook has been created in a newer Version of Excel
None of which is the case here. What can cause this?
System: Virtual machine (VMware) on Windows 2008 R2, Office 2010, 64bit, using temporary profile.
Power Pivot :: How To Set Refresh For Excel File Uploaded In Power Bi
Sep 9, 2015I have opened an account in [URL] and taken the 60 days trial for power bi pro. I've developed power pivot and generated power views in share point 2013. But, I'm new to Power BI desktop. I have created a report in power BI desktop and published to [URL]. Also, I have uploaded an excel file directly to [URL] and created the report from the workspace available there itself and pinned the report to dashboard also. Everything is fine till this. But, I need to refresh the file which I have uploaded. I have some dummy data in excel sheet.
ZipCode State ZipName
2345 AA AA
456 BB BB
6787 CC CC
This has been created as a table and then added to data model. So, power pivot has been created for the same. Then I have uploaded this file to [URL] site. But, I'm getting an error message while trying to schedule refresh for the same.
"You cannot schedule refresh for this dataset because it does not contain data model connections. You cannot schedule refresh on worksheet connections or linked tables. To schedule refresh the data must be loaded into the data model."
How can I create a data model connection? How can I schedule refresh for an excel file like this?
Power Pivot :: Power View - Mix Series Types On Same Chart
Jul 25, 2015I'm a relative newcomer to Power View. I've been playing with charts and have been struggling to combine both line and bar on the same chart. It would appear this functionality is not available. Considering this is basic functionality when it comes to charting, how to achieve this....
View 3 Replies View RelatedPower Pivot :: Power Query Conditional Text Replacement
Jul 16, 2015I'm looking to replace text in a given column given a set of conditions in the other columns. Please see below the M query in the advance editor and in particular the bold text. Here I've created a new entry that would appear in the query applied steps window in the power query editor that I have called "Replace Values". The logic is if Data.Column4 column equals "London" then replace null values in Data.Column5 with London. However when I save the query below I get the error
Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?
I plan to change the expression to test for multiple conditions however I need to get the basic expression working first. The other frustration i had with the "if" statement is it had to have an else even though I didn't require it, am i doing something wrong here?
let
FullFilePath = "C:PermanentDwellings.xlsx",
Source = Excel.Workbook(File.Contents(FullFilePath)),
#"Expanded Data" = Table.ExpandTableColumn(Source, "Data", {"Column1", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16",
[Code] ....
Power Pivot :: How To Customize X-axis Of Power BI Dashboard
Aug 26, 2015I want to show on Power BI Dashboard a moving average - for example, I want to always show the last 30 measurement of body temperature but it looks like Power BI dashboard shows all measurements I have and compress them - which makes the dashboard ugly.
I tried to customize the X-axis properties but I dont know what I should change the default start/stop properties to (where the default property value is automatic).
[URL]
Power Pivot :: Power BI Desktop As Backend
Sep 29, 2015We are trying to build dashboards using Power BI desktop version with Power Pivot data model as a back end. To import the data to Power BI,we used Import ->Excel workbook contents option and successfully imported the model to Power BI .When I tried to refresh the Power BI,It's hitting the backend tables available in the power Pivot model. But my requirement is I need to refresh the Power BI from the power Pivot Datamodel(I'm expecting an option like Import from Power Pivot in Tableau).
View 10 Replies View RelatedPower Pivot :: Slicers Won't Print In Power BI
Apr 18, 2015One big reason why we have turned to Power BI for Office 365 is to share Power Pivot models with our senior executives who all use Macs.
While they have been able to successfully view the Power Pivot models and interact with the Slicers in Power BI, they have not been able to print the reports. We thought it was a Mac issue but it's happening on PCs too. There doesn’t seem to be a way to resize the report so that all columns print. So we then tried to click on the tiny button located in the bottom right corner that says “View Full-Size Workbook.” The file then opens up in Excel Online. Then when we click on the printer icon, we see the report but the slicer becomes invisible. When printing, all columns do show up however the slicer is invisible.
How to successfully print Power Pivot reports with slicers in Power BI?
Some Things Not Working In 2005 And Working In 2000
Mar 3, 2006hi
I had a view in which I did something like this
isnull(fld,val) as 'alias'
when I assign a value to this in the client (vb 6.0) it works ok in sql2000 but fails in 2005.
When I change the query to fld as 'alias' then it works ok in sql 2005 .
why ?? I still have sql 2000 (8.0) compatability.
Also some queries which are pretty badly written run on sql 2000 but dont run at all in sql 2005 ???
any clues or answers ?? it is some configuration issue ?
Thanks in advance.
ExecuteNonQuery - Add Working/Update Not Working
Jan 7, 2004I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...
This was my test:
Dim cmd As New SqlCommand("pContact_Update", cn)
'Dim cmd As New SqlCommand("pContact_Add", cn)
Try
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@UserId", SqlDbType.VarChar).Value = UserId
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = TextBox1.Text
[...etc more parameters...]
cmd.Parameters.Add("@Id", SqlDbType.VarChar).Value = ContactId
cn.Open()
cmd.ExecuteNonQuery()
Label1.Text = "done"
cn.Close()
Catch ex As Exception
Label1.Text = ex.Message
End Try
When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.
I have looked at the stored procedures and the syntax is correct according to SQL Server.
Please I would appreciate any advice...
POWER Function
Dec 1, 2006Hello,
I'm trying to follow a specification here:
W1 = (W0^0.333 + G * D/1000)^3 (the ^ symbol means 'to the power')
In my stored procedure I have used the following test data with unexpected results. Can anyone tell me if I'm using POWER properly?
@Value = POWER(((POWER(150,(1/3)) + 2) * 10/1000),3 )
Thanks
Power Outage
Aug 31, 2000I have a SQL server that's partly managed by another team for their vendored application. Anyway, they're having some weird issues with performance. Last weekend a guy tripped over the power cord (duh!). I looked at the logs and there were no recovery errors on restart and it seems to be running fine..
I'm looking for help on any diagnostics I can run to check anything else out?
Thanks,
ann
Power Point
Oct 26, 2005Hi there.
Is it possible to convert the Power Point file to SQL Server Version 7 or 2000? My PowerPoint files have no images or pictures, only lyrics for songs that are used in the church during sunday services.
I was appointed to make a system in VB6 but my first job is to convert the PowerPoint files to SQL Server. There are 2000 songs in the powerpoint right now.
Please help.
Thanks.
Problem With POWER(x,y) In Sql
May 8, 2006hi,
i can't see why the following field is not working correctly, ie: it's always coming up with 0:
((POWER((1+((((((POWER((1+(dbo_table1.APR/100)),(1/12)))-1))*12))/12)), dbo_table1.FREQUENCY)-1)*(12/dbo_table1.FREQUENCY))*100 AS YIELD
breaking the code down to a smaller formula it does work.
it looks like i can't have more calculations/references inside a POWER(x,y) function.
any comments would be appreciated.
thank you
Power Regression
May 30, 2006I need to write some SQL to do a power regression for a trendline. I have 2 columns of data which represent my X, Y data and all I'm after is the a and the b for the function y=ax^b. Has anyone ran into this before?? I know SSAS has a linear regression function but my data really only fits the power model.
View 4 Replies View RelatedSQL POWER And Decimal Pricision Help
Apr 8, 2008Hello all, I am having a lot of trouble maintaining decimal precision in the script below. Specifically, the statement: SELECT @SKIPPROB = 1-(POWER(.999,@CURRENT - @LASTHIT)). This should be returning a large decimal value, but some of the values that are being updated into the Straights table keep showing up incorrectly rounded - sometimes as whole numbers. In one particular example, the statement above would become... 1-(POWER(.999,10201)). Note: 10201 is the result of @CURRENT - @LASTHIT. This should then equate to 0.99996306 as the answer to the formula. For some reason, when I open the Straights table, the result shown is 1.00000000. Please note that the result is loaded into the variable @SKIPPROB which is declared to be a Decimal(10,8). Also, the column in the straights table that this value is updated into is also set as Decimal(10,8). I am still very new to this, so am I overlooking something. I don't understand why the result is being rounded up to 1.00000000. Any help is appreciated.
DECLARE @EXACT INTEGER, @CURRENT INTEGER, @LASTHIT DECIMAL(10,5), @AVGSKIP decimal(10,2), @MAXSKIP INTEGER, @1000AGO DECIMAL(10,4), @HITS1000 DECIMAL(10,4), @HITSLIFE DECIMAL(10,4), @SKIPPROB Decimal(10,8)
SET @EXACT = 1000
WHILE @EXACT > 0
BEGIN
SET @EXACT = @EXACT -1
SELECT @CURRENT = (SELECT MAX(GAME) FROM History)
SELECT @LASTHIT = (SELECT MAX(GAME) FROM History Where EXACT = @EXACT)
SELECT @AVGSKIP = (SELECT AVG(cast(exactskip as decimal(10,2))) FROM HISTORY WHERE EXACT = @EXACT)
SELECT @MAXSKIP = (SELECT MAX(EXACTSKIP) FROM History WHERE EXACT = @EXACT)
SELECT @1000AGO = (SELECT MAX(GAME) FROM History)-1000
SELECT @HITS1000 = (SELECT COUNT(EXACT) FROM History WHERE EXACT = @EXACT AND GAME > @1000AGO)
SELECT @HITSLIFE = (SELECT COUNT(EXACT) FROM History WHERE EXACT = @EXACT)
SELECT @SKIPPROB = 1-(POWER(.999,@CURRENT - @LASTHIT))
UPDATE Straights
SET [CURRENT SKIP] = @CURRENT - @LASTHIT,
[AVG SKIP] = @AVGSKIP,
[MAX SKIP] = @MAXSKIP,
[TIMES DUE] = (@CURRENT - @LASTHIT)/1000,
[FLIPS] = (@CURRENT - @LASTHIT)/692.8005,
[HITS 1000] = @HITS1000,
[PERCENT 1000] = (@HITS1000 / 1000)*100,
[TOTAL HITS] = @HITSLIFE,
[PERCENT LIFETIME] = (@HITSLIFE / @CURRENT)*100,
[SKIP PROB] = @SKIPPROB
WHERE EXACT = @EXACT;
END
Power Pivot :: Sum With Or Conditional
Jul 2, 2015I have 2 table below:
Table 1:
Product No Quantity
A
Table 2:
Product No Receiving Quantity Type
A 10 NR
A 5 CN
A 6 NR
A 7 DC
A 6 CN
I want to sum Receiving Quantity with Type "CN" OR "DC". The result of Quantity in Table 1 should be like this:
Product No Quantity
A 12
Script Problem With POWER(x,y)
May 8, 2006hi,
i can't see why the following field is not working correctly, ie: it's always coming up with 0:
((POWER((1+((((((POWER((1+(dbo_table1.APR/100)),(1/12)))-1))*12))/12)), dbo_table1.FREQUENCY)-1)*(12/dbo_table1.FREQUENCY))*100 AS YIELD
breaking the code down to a smaller formula it does work.
it looks like i can't have more calculations/references inside a POWER(x,y) function.
any comments would be appreciated.
thank you
Exporting To Power Point
Feb 28, 2007How to Export SQL Report to Power Point.
View 1 Replies View RelatedIn Need Of Power Builder Driver To Connect To SQL 2K???
Mar 14, 2002In need of Power Builder Driver to Connect to SQL 2K???
Any suggestions?
Power Pivot :: Difference Between Two Dates
Jun 2, 2015Is it possible to get difference between two dates - first one and last one per every group in table (records are sorted by ID)?
ID Group Date
739 Group 1 2015-04-17 14:27
740 Group 1 2015-04-19 06:51
760 Group 1 2015-04-19 11:51
762 Group 1 2015-04-21 09:30
763 Group 1 2015-04-20 09:20
[Code] ....
Power Pivot :: Running Product Using DAX
Aug 14, 2015I tried below measure to get the YTD running product and its working fine for me. but I found one issue that its give me slow performance while I see running product by different dimension level.
YTD Return:= IF(HASONEVALUE(Calender[Year])),
POWER(10,SUMX(
FILTER(ALL(Calendar), Calender[Year] = VALUES(Calender[Year]) && Calendar[Date] <= MAX(Calendar[Date])),
IF([Return (%)] > -1, LOG(1+[Return (%)])
), -1
Power Pivot :: Dynamic Row Numbering
Nov 12, 2015I am having trouble making this DAX formula work. This is my test data including the column "rank" which is a row_number with partition by generated using T-SQL. (The nature of the data is that the "Measure" will always increase cumulatively per month and I want the maximum "measure" to equal a rank of 1).What I need is a second RANK column which updates dynamically so that if I select just some of the Months, they will rank from 1 +. E.g:
View 6 Replies View RelatedPower Pivot :: Comparing 2 Cells
May 20, 2015I've been working with powerpivot and I'm trying to compare 2 text values that are in 2 different tables.For example in TableA I have the column Value and I have the values G and C. What I'm trying to do in TableB is compare the values from TableB with TableA.BOTH values are text but I keep getting an error
=IF(TableB[Value]=TableA[Value], 1,0)
For some reason the TableB doesn't recognize TableB[Value] as text...
Power Point :: Data Refresh More Than Once A Day
Jun 23, 2010Is it possible to parameterize SharePoint so that the PowerPoint data refresh is done more than daily ? There is a daily, weekly and monthly entry in the configuration panel, but it seems there is no way to do it more frequently. Sounds rather odd, as there are lots of businesses where the refresh must be done (and can be done) during lunchtime...
I am open to any non-standard solution : SharePoint web service, capturing the HTTP frame when changing the parameter with the ASAP checkbox activated and replaying it later.
Rendering Extension For PPT (Power Point)
Jul 24, 2006Does someone of you know a rendering extension for ppt. Any integrated or third party componens ?
The management wants to have the reports as powerpoint presentation
Best regards
HANNES