Power Pivot :: Relationship Cannot Be Created Because Each Column Contains Duplicate Values

Nov 15, 2011

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".

View 15 Replies


ADVERTISEMENT

Power Pivot :: Use Relationship Not Overriding Conflicting Active Relationship?

Oct 14, 2015

I have four tables with relationships as shown. They have a circular relationship and so one of the relationships is forced to be inactive.

   Operation (Commodity, OperationKey)   ==========> 
Commodity (Commodity)
                      /                                                                        
/
                       |                                                                         
|
   Advice (OperationKey)       ====== (inactive)=======>
Operation Commmodity (Commodity, OperationKey)

I have the following measure:

Advice No. Commodity:=CALCULATE (
COUNTROWS ( 'Advice' ),
USERELATIONSHIP(Advice[OperationKey],'Operation Commodity'[OperationKey]),
operation
)

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.

View 9 Replies View Related

Power Pivot :: Why Can't Create One To Many Relationship

Nov 3, 2015

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... 

View 3 Replies View Related

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:

->Employee Table: Employee_name, Previous_Manager, Current_Manager
->Manager Table: Manager_Name

Because I have 2 manager fields in the employee table, I need to create 2 links between the employee and manager tables:

-> Link 1: Previous_Manager ---- Manager_Name
-> Link 2: Current_Manager ---- Manager_Name

Right now, one of the links is inactive...

Is there a way in PowerPivot to create 2 active links like that ?

I have Power Pivot version 11.0.3000.0 on Excel 2010 on Windows 7

View 5 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 :: Creating A Summary Report Using Relationship Between Date Table And Two Fact Tables

Oct 19, 2015

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.

Month   Count
July
August
September
October.

View 3 Replies View Related

Power Pivot :: How To Flag Unique And Duplicate Records (DAX)

Oct 26, 2015

I am trying to find duplicate records based on a set of criteria, and flag one of those records as unique and the other duplicate ones as dup.

I found this suggestion:

IF( CALCULATE( 
  COUNTROWS( Table1 ); 
  FILTER( Table1;  [Column1] = EARLIER( [Column1] ) && [Column2]
= EARLIER( [Column2]
) ) 
  ) > 1;
  "dup";
  "unique"
)

However, this method flags all duplicates as dup. how I can get to the enclosed results for one particular student?The dup check column shows the desired results. The criterion is any record with the same STUDENT_ID, PROG_DESC, FISCAL_YEAR will be considered as duplicates, but should at least have one record out of that group of duplicates flagged as 1.

STUDENT_ID| ACAD LEVEL | GENDER |
PROG_DESC | RACEETHNICITY |
HOME_STATE | ACADEMIC_LEVEL | FISCAL_YEAR |
Dup Check
815581 Undergraduate

[code]....

View 6 Replies View Related

Power Pivot :: Calculating Values For Future Dates Based On Past Values

Nov 13, 2015

I am working with a data set containing several years' of monetary values. I have entries for past dates and the associated values, and I also have entries for future dates. I need to populate the values of the future date records with the values from the same date the previous year. Is there any way this can be done in Power Pivot?

View 6 Replies View Related

Power Pivot :: Calculate Repeated Values

Jun 15, 2015

I have one table

Customer ID; Sort of Transactions;Value
1234;Kit;1
1235;Kit;1
1236;Revenue;30
1235;Kit;2
1238;Revenue;10
1235 ;Kit;1

Looking for a formula for calculated column, which gives me sum of "Kit" for Customers

1234;Kit;1;1
1235;Kit;1;4
1236;Revenue;30;0
1235;Kit;2;4
1238;Revenue;10;0
1235 ;Kit;1;4

View 2 Replies View Related

Power Pivot :: DAX Calculated Measure For Sum Of Max Values

Sep 21, 2015

We have a client that wants to take the Max value of a count at the Product level, but then for each Brand (Parent of product), sum the Max values for that particular slice.  The example below shows the Max sales per month over the last 6 months. 

Prod1 had it's best month in Month2 (20 units) and Prod2 had its best month in Month4 (30 units).  Brandx should roll up to 50 units.  Can DAX handle this in a single Calc Measure?

Brandx          50
    Prod1        20
    Prod2        30
Brandy          60
    Prod5        40
    Prod6        20

View 2 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 :: Selected Report Filter Values Display In A Cell

Oct 28, 2015

I have a power pivot with 2 multi valued report filters   student_branch & blood_group. These report filters are used to fetch the data set that contain below result set

student_branch               blood_group           count

Everything works fine. But, what i am looking for , is there any way to show the what are all the report filters that are selected currently by , separated in a separate cell ? below is the image for output reference.

View 2 Replies View Related

Power Pivot :: How To Create Subtotal Column

Nov 20, 2015

Q1-2013 Q2-2013 Q3-2013 Q4-2013 2013 Total
Distinct Count Member Distinct Count Member Distinct Count Member Distinct Count Member Member Months
AMBULANCE - LAND 264 301 355 352 1272
AMBULATORY SURGICAL CENTER 16 38 30 34 118
COMMUNITY MENTAL HEALTH CENTER 6 7 11 13

I have the above data, and want to create a column adding up the Distinct count member of each quarter. The number of distinct count member of each quarter is from a measure. The result I want to create is the column of 2013 Total Member Months as   shown above. How to create a dax formula. 

View 5 Replies View Related

Power Pivot :: How To Detect New View Column

Aug 24, 2015

Using PowerPivot I created a connection to a view on a SQL Server database. All fields imported correctly.A column has since been added to the view. How do I get this to appear in my PowerPivot?

View 3 Replies View Related

Power Pivot :: How To Reference A Column Based On Row Value

Sep 4, 2015

I have two tables shown below and I wish to add a calculated column to Table 1.

Table1                                      Table2
ID    Activity                             Activity 1     Activity 2   Activity 3    etc    etc   etc   Total
1    Activity 1                           values         values       values                                  Total value
2    Activity 2                           values         values       values                                  Total value
3    Activity 3                           values         values       values                                  Total value
etc

I want each row in the new column to show the corresponding activity divided by the sum of the total. So in the row with Activity 3 I want the formula SUM(Table2[Activity 3])/SUM(Table2[Total]). Also the number of activities will vary.

I am actually wanting to calculate a more complicated formula which calculates the correlation .

View 2 Replies View Related

Power Pivot :: DAX Formula Copying Data From Column A To B

Oct 1, 2015

I have a problem copying data from column A to column B in Power Pivot.

View 3 Replies View Related

Power Pivot :: DAX Calculated Column Formula Using SEARCH

Oct 30, 2015

I have written a simple DAX calculated column formula using SEARCH and got an error: It tells the text "PWP" cannot be found but as you can see the screen shows this text exsist within "Promotional Claim"The same happens when I try FIND function.

View 2 Replies View Related

Power Pivot :: Lookup Inside Calculated Column

Oct 13, 2015

My simplistic data model looks like this: 

Table.1

Spread Product Sales Channel ..

2 x CB ..
2 y CB …
4 x GFR ..
4 y GFR ..

I need a column with Spread Mapping, which should look like this:

->There are more columns in data model. 
-> Spread for GFR is wrong so it must be mapped to be able to compare with CB

I guess there should be calculated column formula to calculate Spread Mapping. How to do it?

View 6 Replies View Related

Power Pivot :: SPC Average Of A Count Of Text Column

Dec 1, 2015

This is an SPC chart controlled by a slicer that operates a powerpivot table. This is then copied (by cell formula) into a "normal" table where the average, UCL, LCL and Erlang are calculated which are just basic calculations involving average and standard deviation. To make it work the values in the Average, UCL,LCL and Erlang must be repeated all the way down the table to create the chart. In the current format it works well and using a standard table keeps the chart range dynamic.

However this is a very clunky solution involving repeating the data tables in excel. I need to create dozens of charts and it will get large and slow.I would like to create the whole thing in a powerpivot table using measures so i can use powerpivot charts and ultimately powerBI. The data column PasID is a text column so I need a measure that calculates the "count of PasID" for each day(the row labels) and then repeats that value down a whole column in the same way the standard table does. I couldn't figure out how to get the correct number repeat down the whole column, which measures to use,Whether to create calculated columns in the data model or any of it. SO I need to be able to get a count of a text column then display the average of that count in a second column all the way down.

View 9 Replies View Related

Power Pivot :: Calculate Is Not Recognized When Adding A Column

Nov 3, 2015

I've been trying to use a simple calculate forumla when adding a column
CALCULATE(SUM([Credit]),
FILTER(ALL([Date],[Date]<=MAX([Date])))
or just a CALCULATE(SUM([Credit])

And I have always the same error

Calculate is not recognized. I've try it on PowerBI and Excel ...

View 2 Replies View Related

Power Pivot :: Count Of Items Between 2 Dates Of The Same Date Column?

Nov 5, 2015

I am try to count number of items that will result by filtering a date column (one Date column). Ex Column "Created Date" between 1-Sep-2015 To 30-Nov-2015. 

I am unable to get any function that is getting right value. The below function return 40, however the actual value when i do manual filter and count is 132.

CountofQ1:=COUNTAX(DATESBETWEEN(DumpLoad1[Start Date],[StrtDate],[EndDate]),DumpLoad1[Start Date])

View 4 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 :: DAX - Lookup Scalar Text Value With A Filter In Calculated Column

Jun 23, 2015

In a calculated column I am trying to get a scalar text value from a lookup to another table. This works quite well when getting numerical values with the following formula:

=MinX(Filter(LookUpTable;LookupTable[from]<=MySourceTable[Day] && LookupTable[to]>=MySourceTable[Day]);LookUpTable[numericalColumn])

But as soon as I substitute the numerical column by a string column, #error results.

I also want to mention that the above query yields only one row as a result. It should be simple to return the value of one of the columns but after searching for quite some time, I could not find any function for that.

View 5 Replies View Related

Power Pivot :: DAX Formula For Extracting A Column Value From Table Based On Certain Conditions

Jul 20, 2015

I'm trying extract a column from the table based on certain Conditions: This is for PowerPivot.

Here is the scenario:

I have a table "tb1" with (project_id, month_end_date, monthly_proj_cost ) and table "tb2" with (project_id, key_member_type, key_member, start_dt_active, end_dt_active).

I would like to extract  Key_member where key_member_type="PM" and active as of tb1(month_end_date).

Is this possible using DAX ?

View 6 Replies View Related

Power Pivot :: How To Create Incrementing Sub Counter For (sort By Column) Purposes

Nov 16, 2015

I have a table ComponentPeriod. In it we have the combination of a component (e.g. A,B,C ) and a period (2014 Q1, 2014 Q2, 2014 January etc)I want the periods to be in descending order (2015 Q4, 2015 Dec, 2015 Nov, 2015 Oct, 2015 Q3 ... etc) and so I need to create a sequential number series to allow this to happen (as we can only order in the client tools by a single column - and so I guess the technique I'm looking for is used a lot to produce these types of "order by" columns)

I have done this in the past using

Period_Sequence =
calculate
(countrows(Period)
,filter
(Period
, Period[Start_Date] <= earlier(Period[Start_Date])
&& Period[Duration_String] = earlier(Period[Duration_String])
)
)

Which was fine when I was referring to a table where Periods where distinct directly but now I have denormalised this for ComponentPeriod so I need something a little more sophisticated Whats the best way to get a sequence with perhaps some partitions in across a subset of distinct columns (I guess from SUMMARIZE or similar)

E.g. I want

Period_Name, Type, Sequence
2015 Q4, Quarter, 1
2015 Dec, Month, 2
2015 Nov, Month, 3
2015 Oct, Month, 4
2015 Q3, Quarter, 5
2015 Sep, Month, 6
etc

even though there may be multiple records in ComponentPeriod that have the period 2015 Q4, but I want them all to have the value Sequence value of 1? I've got as far as:

Period_Sequence Desc =
calculate
(countrows(summarize(ComponentPeriod, ComponentPeriod[Period_End_Date]))
,filter
( ComponentPeriod
, ComponentPeriod[Period_End_Date] >= earlier(ComponentPeriod[Period_End_Date])
)
)

But this doesn't distinguish between the different types. I need an equivalent of the t-sql -

row_number() over (order by Period_End_Date desc, case when 'P3M' then 1 when 'P1M' then 2 end asc

View 7 Replies View Related

Power Pivot :: Running Rank Calculated Column That Obeys Context Filters

Jul 13, 2015

I am looking to add a column to one of my tables that displays a running rank of how many times a customer has ordered in a given period. 

I currently have such a column however this column ranks against ALL of the orders that a customer has placed and ignores filters, whereas I need one that ranks based on the filters that are active at any given time. 

The current formula is:

CustOrderCountPersistant=RANKX(FILTER('Q1 Data Set',[k1_customer_id]=EARLIER([k1_customer_id])),[order_id],[order_id],1,DENSE)

For example, if I am looking at a full years worth of data and a customer has placed 10 orders in that period this formula will add a 1 in the column for first order, a 2 for the second and so forth all the way to 10, the last order. 

However it will give me exactly the same results if I filter the data to just one month of that year where they may have order only 2 orders. 

In this scenario I want to have another column with a table that is filter sensitive and would show 1 for the first order and 2 for the second order. 

Now, I do understand that the issue here is probably the FILTER() I have on as, if I understand correctly, that means all other filters are ignored. My attempts at reworking the formula to remove this have been unsuccessful (such as using a CALCUALTE and trying to use filter properties within that forumula).

To explain the context - I want to create a measure that counts how many customers have placed x amount orders in y number of days e.g. how many customers have placed 2 orders in 30 days.

View 3 Replies View Related

Power Pivot :: Currency Symbol Dynamically Based On Column In Data-source?

Oct 15, 2015

Is it possible to include a currency symbol in an amount-field in PowerPivot/Pivottable based on a Currency column in a table? Something as the same as with SSAS MD. And I don't want fixed values in my code.

View 3 Replies View Related

Power Pivot :: Create Power View Report - Button Does Not Show Up For Some Reports

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

Power Pivot :: Power Query Error Expression / Cannot Convert The Value To Type Text

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

Power Pivot :: IF In Power Query Statement - Converting Text To Numbers

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

Power Point :: How To Generate Rank Based On Some Group In Power Pivot

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

Power Pivot :: Error When Updating From Power Query Source

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

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







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