Power Pivot :: Multiple Relationship Between Same Tables
Sep 8, 2015
I am developing a database in PowerPivot and I am wondering how to create many relationships between the same 2 tables. All relationships must be active.
Let me give you a DUMMY example: let's say that the database has 2 tables, the Employee table and Manager table:
I have below tables in my power pivot.Fact 1 & Fact 2 - connected directly to Mainframes - Data is from the same table broken up due to size of the data.Date Table - Relation ship between both the fact tables.How do i create a summary pivot to get the number of tasks that have been completed in each month.
However, the userelationship function does not override the active relationship between Operation & Advice and so the measure is limited to Advices directly filtered by the Operation table.
If I delete the relationship between Operation and Advice, then the measure works as expected i.e. Operation indirectly filters Operation Commodity which filters Advice.
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:
I am bring two fact tables and a Date Dim into my Power Pivot model. I try to create relationship where but Power Pivot just reverses them. When I create the relationship I get an info icon warning me it will be changed.Why can't I create a one to many relationship with fact table on many side and dimension on one side?Power Pivot will not let me mark my dimCalnder as a date table either. My date dim has surrogate key, year, month and monthlongname columns only...
I have 2 excel tables files.One table has info about sales by country, by model, by date...and the other table has units by date.Obviously, the common key is "date"....and by creating a relationship, I can add "units" to my combined resulting table.However, I can't create the relationship. Excel keeps telling me: "the relationship cannot be created because each column contains duplicate values. Select at least one column that contains only unique values".
1) Production data with column headers: Key, Facility, Line, Time, Output 2) Costs data with column headers: Key, Site, Cost Center, Time, Cost
The tables have a common key named obviously as Key. The data looks like this:
Key Facility Line Time Output Alpha
I would like to have two pivot tables which I can filter with ONE slicer based on the column Key. The first pivot table shows row labels Facility, Line and column labels Time. Value field is Output. The second pivot table shows row labels Site, Cost Center, and column lables Time. Value field is Cost.How can I do this with Power Pivot? I tried by linking both tables above to a table with unique Keys in PowerPivot and then creating a PivotTable where I would have used the Key from the Keys table.
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
I work in a medical facility and and client confidentiality prevents loading my pivot tables to sharepoint at this time.
I am creating several reports that combine data from SQL Server, Cerner medical report DB and some lookup tables in Excel.
Everything works great on my desktop but I'm having trouble sharing my work.
Our normal routine is to drop a copy of our excel files in a Pass through file on the server that has strict access controls.
I think my main problem is my supervisor doesn't have powerpivot on his machine. that will be corrected tomorrow.
My question is: When close your powerpivot workbook, do all the connections go with it. If I just drop myproject.xlsx into the pass through will all the links to the varying data sources still be available?
Product No Grade Quantity A Good A Normal A Bad B Good B Bad C Good C Normal C Bad
In Table 2, Product No divided by Grade. I want to lookup the Quantity from Table 1 to Table 2. The same Product No will have 1 value, the other value is 0. The result for Column Quantity should be like this:
Table 2:
Product No Grade Quantity A Good 1 A Normal 0 A Bad 0 B Good 2 B Bad 0 C Good 3 C Normal 0 C Bad 0
Our business model involves a lot of dates and the business owners frequently want reports based on each of these different dates. For example in any given order there are as follows:
- Order created date - Client due date - Order first payment date (an order can have multiple payments) - Order fully paid date - Date assigned to vendor - Vendor return date - Date delivered to client
On top of that we have other areas of the business, the data from which ties into the above. Here we have more dates e.g.
- Date vendor recruited - Date vendor reviewed
At any given point the manager may want a report based on any of these dates. For example;
- Product type by order creation date (fiscal year / month) - Product type by first payment date (fiscal year / month) - Product type by client due date (fiscal year / month)
and so forth. I have been asked to create a report using all of the above on at least one occasion, many of them far more frequently. At the moment I have created a standard date table and then duplicated that for each type of date that I need however this is becoming excruciating to work with as I have approximately 10-12 date tables in my data model. Is there a better way of doing this now, in Excel 2013? If not, is there an improvement in 2016 that may make life easier?
I am trying to implement some powerview report in excel 2013 itself (not in sharepoint).
I have implemented a SSAS tabular model and deployed to server. Now my aim is to load this tables in the SSAS tabular database into powerpivot so that i can use this data for creating powerview reports. However when i tried to import data from SSAS, it asks database and MDX query. My requirement is to load the dimension and fact tables to power pivot as it is.
I'm building a Search Function with different pull down options. I have 9 Tables and 6 of them are not related. How do I build an effecient SELECT? Thanks I'm somewhat new to MSSQL.
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.
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.
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?
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?
Inner join will remove rows from the first table if there is not a corresponding row in the 2nd table. Outer join will keep the rows, and use all NULL values to fill in the gaps in the 2nd table.
In general outer joins are more likely to grow a result set and inner joins are more likely to shrink one (though that will depend on the data and the query). For this reason, it is usually better when building complex queries to try to do your inner joins before your outer joins, because then you will keep your result set smaller for longer and thus the later joins can run faster.
I am relatively new to MS SQL (not a novice, but hardly a master).
I am working on a content management application for a magazine publisher. It’s written in ASP (VB Script) and being created on Dreamweaver 8 with an MS SQL 2000 database. Now, I’m trying to decide the best and fastest approach to coding a complex INSERT and UPDATE function. My question isn’t as much about the SQL (although that will probably come up after I decide how to do this), but about the procedural steps and approach I shuold be taking to do this.
Reporters will use an online form to enter their story into the system. It collects the usual data: Headline, byline, story content, and the story category (feature, opinion, entertainment, business, sports, etc.). Each story may belong to MULTIPLE categories (feature & business, for example).
So, I’ve created three tables to support this many-to-many realtionship:
Story Category StoryCat (a junction table with the IDs from both the other tables).
The online form has a dropdown menu which pulls the available categories from the Categories table. When the reporter has entered the data I use ASP to performs the insert just as you would expect it to.
The next step needs to be to update the StoryCat table so that it creates a new record with the StoryID of the record it just inserted, along with the CategoryID that was in that record.
------------------
As I said, I’m not sure of the best way to do this.
Should I just pull back the last record inserted and then create a procedure that would insert into the StoryCat table (which is what I’m thinking of doing on the confirmation page), or is there another approach I should take (perhaps some sort of temporary table or stored procedure?).
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?
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....
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?
I 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).
I have two table studenTtable and courseTable which is each student take more than one course . 1:M...for example Student1 take 2 courses (C1 , C2). Student2 take 3 courses (C1,C2, C3).I need to create a table/View that contain student information from StudentTable plus all the courses and the score for each course from CoursTable in one row.
for example Row1= Student1_Id ,C1_code ,C1_name ,C1_Score ,C2_code,C2_name ,C2_Score Row2= Student2_Id,C1_code, C1_name,C1_Score,C2_code ,C2_name ,C2_Score , C3_code,C3_name,C3_Score
and since Student one just have two courses , I should enter NULL in 'Course 3 fields'.My Struggle is in the insert statement I tried the following but it show an error
Insert Into Newtable ( St_ID, C1_code,c1_name, C1_Score ,C2_code ,C2_name,C2_score,C3_code ,C3_name,C3_score) Select (Select St_ID from StudentTable) , (Select C_code,c_name,c_Score from Coursetable,SudentTable where course.Stid =Studet.stid) , (Select C_code,c_name,c_Score from course ,student where course.Stid =Studet.stid ), (Select C_code,c_name,c_Score from course ,student where course.Stid =Studet.stid );
I'm fully aware that the New table/View will break the rules of normalization ,but I need it for specifc purpose.I tried also the PIVOT BY functionality but no luck with it .I also tried writing a code using Matlab (because it is high level sw that it is easy to learn for people not expret in programming as me) but didn't know how to combine the Student and Courses Matrices in my loop.
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]
How can I apply "Min" formula under a "new measure" (calculated field) within a pivot table under Power pivot 2010?Can see that neither does it allow me to apply "min" formula directly "formula box" nor could find any other option.Intent formula: "=Min(1,sum(a:b))" this isn't allowed so all I can do is "=sum(a:b)".
I have simple pivot table (below screenshot with info redacted) that displays a population number ("N" below), this is the denominator, a cumulative numerator number (below "#") and a simple cumulative percent that just divides the numerator by the denominator. It cumulates from top to bottom. The numerator and percent are cumulative using the below functions. There are two problems with the numerator and percent:
1. When there is not a number for the numerator, there is no value displayed for both the numerator and the percent..There should be a zero displayed for both values. 2. When there has been a prior number for the numerator and percent (for a prior month interval) but there is no number for the numerator in the current month interval, the prior month number and percent are not displayed in the current month interval--see the 3rd yellow line, this should display "3" and "16.7%" from the second yellow line.Here is the formula for the numerator:
=CALCULATE(count(s1Perm1[entity_id]),FILTER(ALL(s1Perm1[ExitMonthCategory]),s1Perm1[ExitMonthCategory] <= MAX(s1Perm1[ExitMonthCategory]))) Here is the formula for the percent: =(CALCULATE(countrows(s1Perm1),FILTER(ALL(s1Perm1[ExitMonthCategory]),s1Perm1[ExitMonthCategory] <= MAX(s1Perm1[ExitMonthCategory]))))/(CALCULATE(COUNTROWS(s1Perm1),ALL(s1Perm1[Exit],s1Perm1[ExitMonthCategory])))
We 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).
One 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?
I have data in my Powerpivot window which was generated by a sql query. This data includes a field named 'Cost' and every row shows a value for 'Cost' greater than zero. The problem is that when I display this data in the pivot table all entries for Cost display as $0. At first I thought that maybe Cost was set to a bogus data type (such as 'text) but it is set to ''Decimal Number' so that's not the problem.
What is happening and how do I fix it so that my pivot table reflects the values for 'Cost'?
I have a data table that contains budget and actual data by month. I use the data to create a pivot that shows actual results next to budgeted results. I need a column that shows that variance between those columns. I think my issue is that the "Type" field contains actual and Budget. I sum on "Type". I can't seem to create a sum since those items are in the same field or am I missing something?
