Power Pivot :: DAX Function ALLSELECTED Doesn't Refresh When Using A Date Hierarchy For Rows

Apr 3, 2015

I've encountered an issue where the ALLSELECTED function works fine unless I use a date hierarchy, i.e. year, quarter, month, date, for the rows in a pivot and then use a year slicer, select one or more years individually, and the clear the filter on the slicer.  The year(s) that I selected in the slicer remain at 100% in the pivot instead of returning to the subtotals for the unfiltered slicer.

This only occurs when I use a calendar hierarchy for rows and a date field for the slicer, either from the hierarchy or a regular date field.Below are images of the normal behavior and then the result after following the steps above. Can't figure out why the calendar hierarchy is causing the issue since it works for all other time functions, etc., and follows best practices such as contiguous dates, etc. 

View 3 Replies


ADVERTISEMENT

Power Pivot :: Refresh From Worksheet Doesn't Work

Dec 4, 2015

I have a PowerPivot table setup based of a SQL Server stored procedure. I am kind of surprise that when I click the Refresh button on the PivotTable Tools ribbon bar above the report the report does not refresh.  I have to open the PowerPivot window, click refresh and then go back to the worksheet with the PivotTable and click Refresh.  Is that by design?  It seems like the Refresh on the worksheet just gets the existing data from the table.

View 2 Replies View Related

Power Pivot :: How To Set Refresh For Excel File Uploaded In Power Bi

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

Power Pivot :: KPI Showing Number On Refresh

Jun 11, 2015

I have powerpivot which is pulling data from sharepoint list.

When I add new row to the list, on refresh the new row shows 1,0,.... instead of KPI icons(green yellow)

I have to open the excel, deselct and select one measure then it starts reflecting.

The KPI should get refreshed automatically, right?

View 2 Replies View Related

Power Pivot :: Field List Does Not Refresh

May 31, 2015

I am having issues where the PowerPivot Field List does not refresh once I add a new table/field then select Refresh All under Data. I have to refresh every single PowerPivot Field List so they are all refreshed. ("PowerPivot data was modified" message). This doesn't happen at first but once the model is built on it tends to happen.I'm on Excel 2010 PowerPivot 2012 SP2 32bit. I have Windows 7 64bit and I'm wondering if the bitness inconsistency is an issue.

View 3 Replies View Related

Power Pivot :: New Table Created On Every Refresh?

Dec 21, 2014

My PowerPivot workbook, is generating new tables on each refresh.

When opened up the workbook I find a dozen tables named, "Query1", "Query2", "Query3" etc.

It's pulling data from SQL Server.

View 4 Replies View Related

Power Pivot :: Auto Refresh Excel Table (Not Pivot Table) Using Data Source

Jul 8, 2015

Is it possible to generate automatic refresh of excel 2013 table which displays some table of a power pivot model on file open?? I dont want to use pivottable (which supports this ...)

View 2 Replies View Related

Power Pivot :: Pivot Table Loses Text Wrapping For Text Data Upon Refresh

Apr 29, 2015

I have a pivot table that connects to our data warehouse via a PowerPivot connection.  The data contains a bunch of comment fields that are each between 250 and 500 characters.  I've set the columns in this pivot table to have the 'Wrap Text' set to true so that the user experience is better, and they can view these comment fields more clearly.

However, whenever I refresh the data, the text wrapping un-sets itself.  Interestingly, the 'Wrap Text' setting is still enabled, but I have to go and click it, then click it again to actually wrap the text.  This is very burdensome on the user, and degrading the experience.

Any way to make this text wrapping stick so that I don't have to re-set it every time I refresh the data?

View 2 Replies View Related

Power Pivot :: How To Get Back To Traditional Hierarchy Dimensional View

Jun 17, 2015

When ever I create a new Powerpivot from an AS cube, PowerPivot shows the data only in flattened PowerPivot format. How can I get back to traditional hierarchy dimensional view in Pivot table.

View 5 Replies View Related

Power Pivot :: Insert Hierarchy Slicer In Excel 2010?

Sep 23, 2015

We want to implement cascading slicers in PowerPivot 2010. Does Excel 2010 have 'Insert Slicer Hierarchy' feature in PowerPivot?

View 5 Replies View Related

Power Pivot :: Report Refresh In Office 365 Share Point?

Nov 20, 2015

I have a situation where I have created a report using Power Pivot data Model Manage and linked to a SQL View using external sources from SQL Server. Once the View was linked from SQL server, I created the report in Power View from Inserted  --> Power View.  Since the business user requested the same data to be replicated in the excel worksheet in the form of excel, I created a version of excel report by selecting Data tab--> From other sources-->SQL Server --> connected to View (Same view that I connected in Power Pivot Data model for Power View report).

Once I created all the reports in my local excel and proceeded to upload in Office 365 share point. I followed the below steps:

1. Create a data source using the admin power bi website 

 using the connection string..Excel sheet Data tab --> Properties --> Connection String

2. Once the Data Source was created, Uploaded the excel report to the Power BI website.

3. Scheduled for the daily refresh updates.

Expected result:Since the connection set up was all successful and no issues with the refresh schedules, I was expecting to see my reports in both Worksheet reports in excel version and Power View reports to be refreshed on a daily basis. And daily transactions to appear on the reports on both Power view and excel worksheet reports.

Actual Result:I now only see the data refreshed in Power view reports and not the reports linked through Data--> sql server--> view ( i.e., excel worksheet reports). What surprises to me eve more is my refresh schedule was always successful. I do not see the refresh failed message. 

I wanted both the power view and excel worksheet reports to get refreshed on a daily basis.

View 6 Replies View Related

Power Pivot :: Unable To Refresh Report Data When Send To Other Member In Team

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

Power Pivot :: ALL DAX Function Not Overriding Filter On Pivot Table

Oct 14, 2015

I have a simple pivot table (screenshot below) that has two variables on it: one for entry year and another for 6 month time intervals. I have very simple DAX functions that count rows to determine the population N (denominator), the number of records in the time intervals (numerator) and the simple percent of those two numbers.

The problem that I am having is that the function for the population N is not overriding the time interval on the pivot table when I use an ALL function to do so. I use ALL in other very simple pivot tables to do the same thing and it works fine.

The formula for all three are below, but the one that is the issue is the population N formula. Why ALL would not work, any other way to override the time period variable on the pivot table.

Population N (denominator):
=CALCULATE(COUNTROWS(analyticJudConsist),ALL(analyticJudConsist[CurrentTimeInCare1]))
Records in time interval (numerator):
=COUNTROWS(analyticJudConsist)
Percent:
=[countrows]/[denominatorCare]

View 13 Replies View Related

Power Pivot :: Force Measure To Be Visible For All Rows In Pivot Table Even When There Is No Data?

Oct 13, 2015

Can I force the following measure to be visible for all rows in a pivot table?

Sales Special Visibility:=IF(
    HASONEVALUE(dimSalesCompanies[SalesCompany])
    ;IF(
        VALUES(dimSalesCompanies[SalesCompany]) = "Sales"
        ;CALCULATE([Sales];ALL(dimSalesCompanies[SalesCompany]))
        ;[Sales]
    )
    ;BLANK()
)

FYI, I also have other measures as well in the pivot table that I don't want to affect.

View 3 Replies View Related

Power Pivot :: SSAS Tabular Live Connection Doesn't Work If Visual Studio Project Is Not Open

Sep 29, 2015

I have a ssas tabular model as a data source in a .pbix-file. I have uploaded the .pbix-file to Power BI Services and it works fine as long as my visual studio project is open. If I close my visual studio project, the objects in Power BI Services will say "The data recievement for this visual information was not successful."

View 2 Replies View Related

Power Pivot :: How Does EVALUATE Function Work

Oct 24, 2015

playing with the Power pivot , DAX. While analyzing the DAX ,I came across a function EVALUATE , but when I tried this function in excel Power Pivot workbook - =EVALUATE 'Date' where 'Date' is my one of the Power pivot table , I was writing this function within the Calculation area of the Power Pivot model.  I get the below error when I hit enter after writing the function ."The expression is not valid or appears to be incomplete..An MDX expression was expected while a full statement was specified."But in many forums I find the syntax is correct.

View 4 Replies View Related

Power Pivot :: Dates MTD Function And No Row Data

Oct 20, 2015

I have a DATESMTD function which is not working.  This is what is happening, if there is no row data for the month it creates a month to date total similar to the year to date total instead of zero.  See below my formula:

MTDSUM:=CALCULATE(SUM('Combined Years Dataset'[Net]),DATESMTD('Combined Years Dataset'[Period2]),'Date'[date])

Period 2 is a column with dates (end of monthdates) in a table called Combined Years Dataset.

So, if I have $200.00 data for Aug and no data for Sept, the system puts in 200.00 as the month to date  for Sept instead of zero.  What can I do to make the system insert zero in the month to date column instead of the $200.00.  What am I doing wrong in the formula.

View 8 Replies View Related

Power Pivot :: Using CUBEMEMBER Excel Function To Get A Member Value

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

Power Pivot :: Switch Statement And Function Calls

Nov 24, 2015

I created a Switch statement below that surprisingly doesn't throw any errors and some of it actually works. The problem is that the function calls in the 3rd and 4th sections of it below (in bold) are not working.

=switch(HASONEVALUE('s1JudgeIds'[JudgeName]),
values('s1JudgeIds'[JudgeName])<>"1 - All Judges" && values('s1Perm1'[Exit])<>"Still in Out-of-Home Care",CALCULATE(count(s1Perm1[entity_id]),FILTER(ALL(Time[ExitMonthCategory]),Time[ExitMonthCategory] <= MAX(Time[ExitMonthCategory]))),
values('s1JudgeIds'[JudgeName])="1 - All Judges" && values('s1Perm1'[Exit])<>"Still in Out-of-Home Care",calculate([Numerator],all('s1JudgeIds'[JudgeName])),
values('s1JudgeIds'[JudgeName])="1 - All Judges" && values('s1Perm1'[Exit])="Still in Out-of-Home Care",calculate([Numerator-stillincare],all('s1JudgeIds'[JudgeName])),
values('s1JudgeIds'[JudgeName])<>"1 - All Judges" && values('s1Perm1'[Exit])="Still in Out-of-Home Care",calculate([Numerator-stillincare])

View 24 Replies View Related

Power Pivot :: Show Images In Power View With Power Query On Excel Desktop Version / Office 365

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

Power Pivot :: RELATED Function Shows Empty Column?

Aug 16, 2015

experimenting with powerpivot and I use an simple example of the AdventureworksDW in Powerpivot.

If i use the RELATEd function with Product and ProductSubCategory the column is empty and I expect values.

Relationships are rightfully defined. What am i doing wrong?

View 3 Replies View Related

Power Pivot :: Update Specific Rows In Table

May 21, 2015

I have a text file which I used as an initial load of data, containing a little over a million rows. I would like to produce a daily "update" file, which contains changes to existing rows, and creation of new rows. What is the best method of updating the existing table in power pivot?

View 2 Replies View Related

Power Pivot :: How To Count Rows Where A Specific Value Exist

May 20, 2015

This is primarily  a question for DAX/Power BI. I'm trying to figure out how to count rows for a particular column where only a specific value exist. For instance, I'm trying to count how many times a certain field contains a true value or false value across multiple columns. 

I found this formula here: [URL] ....

=CALCULATE(COUNTROWS('table'), ALLEXCEPT('table', 'table'[n_country]))

However, I'm not quite sure how to adapt it to count only rows where a value is equal to True, False, 17, "foobar", etc...

View 5 Replies View Related

Power Pivot :: Date Since Value First Appeared (age)

Sep 14, 2015

So, I have the following tables

transactions = [date], errorID, status
calendar = [date], year, month etc .

The critical field here is errorID.

Maybe the best way to illustrate the raw transaction data is by example :

I now need to create a report to show the AGE of errors.

age1 = days since first occurrence
age2 = days since last 'Repeat' status

I tried to ignore AGE for now, and just see if I could get the correct earliest date to show for each errorID.

How to do a MIN on date, ignoring all row context apart from keeping my errorID !!?!

View 2 Replies View Related

Power Pivot :: How To Do Nested Date Calculation With DAX

Aug 9, 2015

I have a CSV file that looks like below

JobOrderId CandidateId From_StatusID From_StatusDate To_StatusID

1234 5000 1 07/01/2015 2
1234 5000 2 08/01/2015

I've read this file in PowerPivot and I need to calculate the time in weeks between the two dates for the same JO, Candidate and when the to_StatusId = From_StatusID. In other words, the number of weeks that took from going From Status "1" to Status "2".

Is it possible to do something like this using DAX? Do I need to create a calculated Column?

View 4 Replies View Related

Power Pivot :: Date Fields Do Not Allow Value Filtering

Sep 8, 2015

I have an Excel 2010 Pro pivot table that uses a Power Pivot view sourced to a SQL Server 2012 SQL view.  This view contains multiple date fields, and when I attempt to use the 'Value Filter' on one of these, the data is not filtering.  The data type in the SQL view is a date.

After attempting to set a Value Filter, and seeing that the filtering didn't work, when I go back to the Value Filter criteria (in my case, I used 'Greater Than' option), the date value that I plugged in to filter has been replaced with a numeric value.

View 3 Replies View Related

Power Pivot :: Stock Balance - Last Date

Oct 23, 2015

I am struggling with the Lastdate function. I have got stock balance data and want to show the number of products/models that are on stock at the latest date of the stock balance table.

My DAX formula is as follows:

=CALCULATE(DISTINCTCOUNT('3S-StockData'[Article Model]);LASTDATE('3S-StockData'[Date]))

I get the wanted results for all aggregated product groups, on product/model level however the formula does not give me the information wanted (see screenshot).

Basically, the formula calculates correct, but I want in my example only models shown with the date 2015-10-21.

View 2 Replies View Related

Power Pivot :: LY Sales - Function Throws Error When Selecting Periods In Different Years

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

Power Pivot :: Measure Count Rows - If Condition - Multiple Tables

Nov 7, 2014

I want to count the rows in the Incident Table by using filters to limit the rows to be counted if they meet the below conditions. I know I need a logical test for each row of the incident table based on the apparatus table’s rows. But, I want to test for each row in the incident table, counting, but not returning a true or false in the overall measure.Something like look at each incident row, test for true or false and then count IF the statement is true. Then go to the next incident row and do the same. The aggregation would be the final count of “true” results.I tried this for MET objective:

=CALCULATE(COUNTROWS(incident),
        apparatus[Incident Response Time] >-1 ||
        apparatus[Incident Response Time] <320,
        uv_901APP_TYPE[Description]="Engine",
        uv_901INCIDENT[Top_Category]="Fire"

[code]....

View 13 Replies View Related

Power Pivot :: Revenue Schedules - Creating Rows By Number Of Months

May 26, 2015

I am trying to calculate how much revenue we may get, based on potential new business opportunities. The core fields we have are

Total Contract Value ($ or £)Duration of contract (months)Revenue start dateVarious information about the new business - ID, Title, Customer etc.

We can easily calculate the revenue per month with "Total Contract value divide by duration".

However what I would really like to do is be able to know how much revenue we will be getting each month.

To do this I was thinking we should probably create a new row for each month entry, with the mm-yyyy being the only difference for each row. But how to create the appropriate months and the correct amount of rows.

View 3 Replies View Related

Power Pivot :: Prior Month To Date Calculation With DAX

Feb 9, 2013

I’m using DAX to calculate the prior MTD count of a specific column. My data ends on 2/8/2013 and that day's PriorMTD is incorrectly corresponding to 1/31/2013. Whereas, the previous 7 days in February correctly match their corresponding January dates..Below is an image of my pivot table and I have outlined the values in red that are in question.Below are my DAX formulas used each column visible in my image:

Distinct Count of Events:=DISTINCTCOUNT([EventID])CurrentMTD:=CALCULATE([Distinct Count of Events], DATESMTD(Events[EventDate]), ALL (dimDate) )PriorMTD:=CALCULATE([Distinct Count of Events], DATEADD(DATESMTD(Events[EventDate]), -1, MONTH), all(dimDate)) ParallelMonth:= CALCULATE ([Distinct Count of Events], ParallelPeriod(Events[EventDate], -1, MONTH), ALL(dimDate))

View 5 Replies View Related

Power Pivot :: Budget Month-to-date Calculation

Aug 19, 2015

I have a fact table with sales data at line item level, meaning lots of rows with sales orders, revenue etc. per product group pr month. Then I have a budget table with the budgetted revenue pr product group pr month, meaning a lot less detailed. I followed the method at daxpatterns.com/budget-patterns/ with the headline "Complete pattern" to make it work. Now I have a working data model where I can see actual and budget as seperate values.

I then need to calculate the actual revenue month to date and the budgetted revenue month to date. For actual it has worked fine by doing:

=CALCULATE(
sum(Sales[Revenue]);
(DatesMTD(Time[date]))
)

The problem occurs for me doing the same on the budget figure. Since I don't have a budget per day, but only at "YearMonth" level, I have calculated the amount of workdays per month and then used the DatesMTD formula to get the cumulative workdays during the month. Furthermore I calculated the total amount of workdays in the month and made a ratio between this figure and the cumulative workdays so I get a figure in percent that tells me per day how much of the month that has went by (0-100 %). I would then multiply this percentage with the budget and get the budget month to date. This does not work since my budget figure is not at day level.

How I can get it to work. My desired result will be all the dates during a month in my rows and then actual revenue Month to date in values and a corrosponding figure for the budget.

View 7 Replies View Related

Power Pivot :: How To Convert Date / Time From UTC To Local (CET) Considering DST

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







Copyrights 2005-15 www.BigResource.com, All rights reserved