Power Pivot :: Aggregating Time Periods In Cube-member / Cube-value Formulas?
Aug 23, 2015
I am just starting out using CUBEMEMBER/CUBEVALUE formulas in excel linked into a sql olap db - using this method for some custom reports where pivot tables are not suitable.
The time dimension values include Months, Quarters and Years and the CUBEMEMBER formulas like
=CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[1].&[1]") work fine - 1st quarter 1st month etc.
Is there a straightforward notation to aggregate months or do I need to use a plus sign to add a number of CUBEMEMBER formulas together.In other words - Is there an easier way of for say jan to july 2015 totals than
=CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[1]") + (CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[2]")) + (CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[3].&[7]"))
I haven't tested this but have assumed it works but a bit long and clumsy.
View 5 Replies
ADVERTISEMENT
Oct 30, 2015
I've built a cube which has all the measures and dimensions I want, unfortunately the numbers are off: the product prize is aggregated then rather simply being fetched from the underlying oledb destination file in the data warehouse. So if product x costs 10 USD and has been purchased 3 times in a certain month by a customer, the cube shows a product prize of 30 USD. I've switched the measure's usage from SUM to no aggregations but I still don't get the simple value list without aggregation. Why is this happening?
View 4 Replies
View Related
Aug 12, 2015
I have a [Fin Period] column which outputs a number between 1 and 12 depending on the financial period. For numbers 0-9 this is output as a single digit. How can I format this so it places a 0 in front of these numbers but not in front of periods 10-12?
View 2 Replies
View Related
Feb 23, 2005
I have an employee, who received an initial starting bonus of $50k.
This value will be static from day 1 to day n and will never change. I
want to see the intial starting bonus, but any sets higher than the
employee dimension will need to aggregate the starting bonus. Is there
an easy way to do this?
If I just look at the data from an employee's perspective, I can do
this by making the measure a Min, Max, or Avg Aggregate function. But,
if for instance I want to view the data from the perspective of
departments, it would need to sum() the data instead (which min/max/avg
don't do).
If I make the starting bonus a member property of the employee, and a calculated measure off the member property, it aggregates the data when it shouldn't.
View 4 Replies
View Related
May 21, 2015
I have created data model where I'm taking several sources of Point of Sale data (multiple retailers) and combing them using Power Pivot and a custom calendar. We get data retailer direct, which is mostly in weeks, and data from IRI which is in four week buckets. This does not allow me to use the date intelligence DAX functions. I'm brand new to DAX and my experience starts and ends with Excel. (Diagram view and link to file to come after account verification) .
The DAX code for calculating LY Sales is:
=
CALCULATE (
[Sales $],
FILTER (
ALL ( dCalendar ),
dCalendar[IRIYearNumber]
= VALUES ( dCalendar[IRIYearNumber] ) - 1
[Code] ....
The filters are to prevent items not on the item table from showing on the report, and the customer filter is to prevent all the sales being rolled together as and extra line (with blank customer) on the report.
The error happens when I select two periods that are in different years. When I select the 13 periods on 2014 all is well. But when I add a period from 2015 it throws the error below;
ERROR - CALCULATION ABORTED: Calculation error in measure 'dProducts'[LY Sales $]: A table of multiple values was supplied where a single value was expected.
View 5 Replies
View Related
Aug 29, 2007
I have a cube that I read in a data flow section using OLE DB. To get this to work I had to do an ad hoc query through a sql server database ( this is problem that is described in post 219068).
So I now have the data and I want to derive new columns based on that data using an if statement:
if ( ISNULL(" [Facility].[REGION].[NATCODE].[MEMBER_CAPTION] " ) comptype = "NAT" else comptype = "REG"
comptype is the new derived column I am creating.
But when I try to save this I get an error: The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a requred element such as parenthesis
I selected the cube element from the columns list in derived element, when it initially put it in it looked like:
[[Facility].[REGION].[NATCODE].[MEMBER_CAPTION] ]
and gave error:
The token "[" was not recognized. The expression cannot be parsed because it contains invalid elements at the location specified.
Below is the statement I use to get data back from AS
select * from openrowset('MSOLAP', 'DATASOURCE=local; Initial Catalog=Patient Demographics 2005;',
'with member [Measures].[TimeDisplayName] as [Calendar].CurrentMember.Name
SELECT NON EMPTY { [Measures].[TimeDisplayName],[Measures].[Case Count],
[Measures].[A100 Bathing],[Measures].[A100 Bed Chair Wheelchair],
[Measures].[A100 Bladder],[Measures].[A100 Bowel],
[Measures].[A100 Dressing Lower],[Measures].[A100 Dressing Upper],
[Measures].[A100 Eating],[Measures].[A100 Grooming],
[Measures].[A100 Stairs],[Measures].[A100 Toilet],
[Measures].[A100 Toileting],[Measures].[A100 Tub Shower],
[Measures].[A100 Walk Wheelchair],[Measures].[A200 Comprehension],
[Measures].[A200 Expression],[Measures].[A200 Interaction],
[Measures].[A200 Memory],[Measures].[A200 Problem Solving],
[Measures].[D100 Bathing],[Measures].[D100 Bed Chair Wheelchair],
[Measures].[D100 Bladder],[Measures].[D100 Bowel],
[Measures].[D100 Dressing Lower],[Measures].[D100 Dressing Upper],
[Measures].[D100 Eating],[Measures].[D100 Grooming],
[Measures].[D100 Stairs],[Measures].[D100 Toilet],
[Measures].[D100 Toileting],[Measures].[D100 Tub Shower],
[Measures].[D100 Walk Wheelchair],[Measures].[D200 Comprehension],
[Measures].[D200 Expression],[Measures].[D200 Interaction],
[Measures].[D200 Memory],[Measures].[D200 Problem Solving] } ON COLUMNS,
({[Facility].[REGION].[NATCODE], [Facility].[REGION].[REGCODE]} *
[RIC].[CMGGRPCD].ALLMEMBERS ) ON ROWS FROM [CMG Demographics]
WHERE [Calendar].[Quarter 2 (2007)]')
So can some one help me on how I can do this derived colum.
View 7 Replies
View Related
May 28, 2015
In an Excel workbook I'm building a report using a PowerPivot data model.
I've a Calendar filter. If I select an year from this filter I need to show in a cell the total number of working days, present in the Calendar table as a column.
View 9 Replies
View Related
Aug 10, 2015
i have a model contains fact of account revenue , a time dimension and scd of account.
the scd (type 2) is changing when an account get a new color.
when i queries the model by PowerPivot i sometimes get wrong color for an account in a date priod , for example , I would expect to see color 3 for account 1 at 04-06-2010 , But instead I see color 1 - as you can see in the picture bellow.
FACT , SCD :
Result
i process all , deploy , mark dim_time as date dim , and i still cant find the error.
View 3 Replies
View Related
Jan 17, 2004
Please see the attached file
View 5 Replies
View Related
May 19, 2015
I have problems creating a cube with AMO.
I can add the cube to the database object and fill it with dimensions and a measuregroup (see code below).
If I call cube.Update() it says something like "Error in meta data manager. Cube has no measuregroups." (getting the message in german language)
The error in Microsoft.AnalysisServices.OperationException.Results.Messages is -1055653629
I can't find any documentation about this (or any other) error code in Microsoft documentation.
Here's my Code:
Cube newCube = database.Cubes.Add("MyCube","MyCube");
newCube.Language = 1031;
newCube.Collation = "Latin1_General_CI_AS";
CubeDimension dim = newCube.Dimensions.Add("dim1","dim1","dim1");
CubeAttribute attrib = dim.Attributes.Find("dim1Attr1");
[code]....
View 2 Replies
View Related
May 28, 2015
I am using excel 2010 and SQL server as a data source. When I create a report with the User name "demo_user" which has db_writer access, and mail it to the colleague , he could not refresh the data with the "demo_user" credentials and apparently its throwing user name or password invalid. The one who created only could be able to refresh the data.
What should I do If other people want to refresh the data? How could I fix this issue? We are using SQL server 2012 and excel 2010 powerpivot.
View 2 Replies
View Related
May 27, 2008
When I make a call to GetSchemaDataset with a restriction of a cube name with a space in the name of the cube the call fails. Following is a sample of the code: adoRestriction = new AdomdRestriction("CATALOG_NAME", "Contoso Telecom_Contoso"); adoRestrictions.Add(adoRestriction); dataSet = conn.GetSchemaDataSet("MDSCHEMA_CUBES", adoRestrictions); I am running SQL Server 2005 Analysis Services SP2. Is there some way to qualify the cube name in the restriction or is this just a bug? Thanks.
View 1 Replies
View Related
Jan 31, 2007
Hi,
cube processing is taking more time in a new server while same cubes takes less time in another server.
the cubes are processed through DTS package
can anybody help finding out the possible reasons for this.
Regards
Naseem
View 5 Replies
View Related
Apr 11, 2008
Hi guys,
I made a cube with time dimension with hieracly year/month/date/hour
the problem is that dimension is growin to fast. In older version of MSSQL (2000) the same dimension doesn't grew so much.
Any ideas? The table is big (may be around 1 500 000 rows per month) now it contains around 4 500 000 rows.
View 19 Replies
View Related
Feb 28, 2007
Hello,
I have an Analysis Services Cube that I would like to report on. However, the Time Dimension currently only has four columns, Day of Month, Month(name) , Year, and DateKey (DateTime representation at midnight for every day). Thus when I drag the month attribute onto the report, it is sorted April - August - December - etc. instead of Jan - Feb - Mar. How do I fix this? I remember reading something in the MSDN Library about it but I can't find it again now.
Thomas
View 5 Replies
View Related
Nov 21, 2006
We have a set of cubes and dimensions, and we're experimenting with data mining against the cubes (primarily for forecasting applications). We have a custom time dimension (which we call calendar), not generated by the BIStudio wizard. The dimension has year/month/day/hour/... attributes. But when I try to add this Calendar dimension to the mining structure as a nested table using BI studio, it only shows the Year attribute, not the others. Other dimensions seem to show all the attributes.
Is there something we've done wrong in defining our time dimension? What determines which attributes show up as available for selection in BI studio?
View 5 Replies
View Related
Jun 18, 2015
We have hidden few measure groups in cube for time being, where Users can browse the cube with Excel pivot. But, All these measures can be seen from Excel pivot in 'Show fields related to' drop down.
Need to remove the hidden measure groups from showing in Excel pivot and to remove 'All' option in 'Show Fields related to', So that users may not get confused by seeing all the measures. Can we achieve this.
View 3 Replies
View Related
Dec 28, 2006
I broke up my cube into 24 partitions. There are about 630M total fact rows in that cube.
When I open the cube to browse in BIDS or SQL Management Studio it takes very long time to open (I think 30 minutes).
Profiler does not show that it's running a query, but messages like this keep appearing throughout the time it's opening to browse:
Progress Report Begin, 14- Query, Started reading data from the 'p0' partition.
Progress Report End, 14- Query, Finished reading data from the 'p0' partition.
Progress Report Begin, 14- Query, Started reading data from the 'p10' partition.
Progress Report End, 14- Query, Finished reading data from the 'p10' partition.
and goes on like that....
View 13 Replies
View Related
Oct 27, 2015
I am importing data from Dynamics CRM online using ODATA in Power Query. All datetimes in the database is stored in UTC and I need to convert these into CET. But it's not as simple as just to add 1 hour to the datetime from the database because due to Daylight. Savings time on half the dates I need to add 2 hours - how can I do this?
View 3 Replies
View Related
Aug 2, 2015
I 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
View 9 Replies
View Related
Jul 14, 2015
I've been able to find plenty of info online in relation to calculating moving averages for a given value. I am having a specific problem however.
So because I need to see the average on a daily basis, my forumla (below) works fine:
However, my Calendar dimension (TIME) also has a discreet, non-continuous attribute called DateMonth (which looks like 1-Jan, 2-Jan etc.) which has no reference to the year.
I was hoping this would allow me then to take my year attribute and compare the 28 Day Average for the 1st of Jan across multiple years.
So you could have a line chart where the X axis is 1-Jan to 31-Dec (with no reference to the year) and the series category is by year, so a line for each year.
But when I replace DATE with DateMonth in my pivot, the calc gives some strange numbers.
Rolling28DayAvg :=
CALCULATE (
[Revenue],
DATESINPERIOD (
TIME[DATE],
LASTDATE ( TIME[DATE] ),
-28,
DAY
)
)
/ 28
View 15 Replies
View Related
May 22, 2015
I created a PowerPivot measure which is a ratio Ratio = Number of Events/Time calculated on 12 months. I would like the grand total to be this Rate Sum(Number Of Events)/Sum(Time calculated).
In my Pivot I have one measure which I called Value and this value can have different types depending on one attribute.For instance one attribute is number of events, an other one is time and the third one is ratio.I want to display a custom grand total simple sum for events and time and a calculation of my ratio for ratio.
For instance
201501 201502 201503 TOTAL
Number of events 8 10 10 28
Time 5 5
4 14
Ratio 8/5 10/5 10/4 28/14
View 3 Replies
View Related
Jul 29, 2015
Say that I have employee data for 2010-2015. I have a pivot table that displays employee data for all the different months in 2015 I want each month to show all people who have been employed since the start of time until and including that month. So
for January 2015 it should show all people employed from January 2010 to January 2015.
for February 2015 it should show all people employed from January 2010 to February 2015.
for March 2015 it should show all people employed from January 2010 to March 2015.
How can I write a measure for this? I have a date table set up.
View 2 Replies
View Related
Jul 9, 2015
I know how to do this in Excel, but not sure about Power Pivot. Our fire department works on a 24 hour shift that starts at 0700 each morning. I want to look up what shift is working depending on the date and time of dispatch. In Excel, I would do a look up table, but instead of using false() as the last parameter, I would choose true().
I am not sure if this would be best created as a calculated column in the table that holds the incident date and time values, or have a separate table and create a relationship to use the shift in the rows/columns of my pivot table.
View 6 Replies
View Related
Sep 10, 2015
As part of analyzing our fire department's response times, we measure the time it takes to get a certain amount of people on a fire scene. I have two table that contains incident data. The incident table contains one row per incident. The apparatus table contains multiple rows per incident with each row contain the information for each unit. They are connected by the incident key with a one to many relationship.
The column apparatus.num_personnel contains the count of personnel for each fire truck. The goal is to capture the response time of the unit that is carrying the 10th person to the scene. In the example below, E78 was the unit that the sum of apparatus.num_personnel was => 10. There response time of 0:06:18 is returned to the incident table.
I realize I will need to create a logical test (calculated column on the incident table) that sums the number of personnel for each incident and then when that number => 10, then return the MIN Response Time of that row. From past projects, I understand I can create a temporary table (ADD COLUMNS) which will iterate over each incident row, comparing the units for that incident. I"m just not sure how to put it all together.
Overview of data model. The incident and apparatus are my data tables and the rest are lookup tables. The parameter table is used for a percentile measure, but doesn't play a role with this project.
View 9 Replies
View Related
Nov 3, 2015
I 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 Related
Jul 20, 2015
I'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.
View 2 Replies
View Related
Nov 18, 2015
Slow 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?
View 9 Replies
View Related
Apr 10, 2015
I 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?
View 3 Replies
View Related
Jun 11, 2015
When 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.
View 5 Replies
View Related
Sep 9, 2015
I 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?
View 3 Replies
View Related
Jul 25, 2015
I'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 Related
Jul 16, 2015
I'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] ....
View 8 Replies
View Related
