THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

Many-to-Many relationships in PowerPivot

UPDATE: This blog post is still good for learning DAX principles, but a better description of many-to-many patterns is available on The Many-to-Many Revolution whitepaper that is available here: http://www.sqlbi.com/articles/many2many/ - please download it and use the pattern described there to write faster DAX formulas!

PowerPivot doesn’t have the capability of really understand a many-to-many (M2M) relationship between two tables. In a relational world, a many-to-many relationship is materialized using a bridge table that split this relationship in two separate one-to-many relationships between the two original tables and the bridge table. Apparently, we can do the same in PowerPivot, but the behavior is not the expected one. Some workaround is possible using DAX, but there are some undesirable side effects if we only use calculated column. As we will see, it is necessary to use calculated measures to get the best results.

Consider two tables, Customers and Accounts.

 image
Customers Table

image 
Accounts Table

The many-to-many relationship between Customers and Accounts is expressed by the following Cross table.

image 
Cross Table

In PowerPivot we define the same three tables (Customers, Accounts and Cross) and then we define these relationships:

 image
As we will see, the best result is obtained using calculated measures. However, it is interesting to look at the results that can be obtained defining calculated columns, just to understand how PowerPivot works under the hood. You can skip the following section going directly to “Defining Calculated Measures” if you just want to look at the best solution. But if you want to understand how it works, it is better to start from the beginning, looking at the calculated columns before.

Defining Calculated Columns

At this point, we can try the PivotTable using the Amount measure next each Customer, but the result will sum up all the accounts without filtering only the accounts belonging to the Customer in the same resulting row.

image
The value of Grand Total is right, because it represents the sum or all the accounts – but this is also the same value for each customer and this is not right.

We can solve this by adding a DAX expression into a new column in the Customers table. We will call this column Amount of Accounts.

image
The DAX expression will sum (SUMX) all the value of the column Amounts in the Accounts table (‘Accounts’[Amount]) that are related (RELATED(‘Accounts’[Amount])) to the rows in the Cross table considered for each customer (RELATEDTABLE(Cross)). The final result is the desired value for each single customer.

image
As you can see, Marco has a total of 30, which is the sum of the accounts A (10) and B (20). However, the Grand Total is now 95, which is right if you sum the amount of each customer, but it is wrong if you consider that the same account (B) is considered for both customers Marco and Hal. In other words, if these were bank accounts, looking at this number we would be saying that the bank has more deposits than real.

Moreover, if you want to analyze which are the accounts accessible by each customer, dicing Customers and Accounts doesn’t filter out the accounts that don’t belong to the analyzed Customer. In the following screenshot you can see that all the accounts are present with the total amount of the accounts belonging to analyzed Customer.

image
We can obtain a better result with a model that is less intuitive from the point of view of the final user. We can create a RelatedAmount calculated column in the Cross table using the DAX expression RELATED(Accounts[Amount]).

image
In this way, we will use the implicit selection on Cross table made by the relationships we created before in the PowerPivot model, and the final result will also consider the relationship existing between the Cross and the Accounts tables to filter out Accounts which are not related to the selected Customer. Moreover, the value for each account will also be right.

image
However, the Grand Total is still higher than the real amount of all accounts.
This work-around still is not ideal, even because the more intuitive place for the end user for having the amount for all the customers is the Customers table, and not the Cross table which should be invisible to the end user (it is a service table and shouldn’t have meaningful information for the data analyst).

Defining Calculated Measures

The best solution is defining a calculated measure in the Customers table (right click the Customers node in the PowerPivot Task Pane):

image
We define a formula that sums the amount of all the accounts that have at least a line in the Cross table which is related to the context of the calculation in the PivotTable (we will analyze the formula in details later).

image
The final result in our PivotTable is exactly what we wanted: right results for accounts and customers, exclusion of the accounts which are not owned by a customer, right total for all customers. Moreover, the measure we defined (Available Amount) can be defined wherever we want, even in the Customers table (like we did).

image
Now, the problem is only that the Amount value in the Accounts table might be misbelieving (look at the first example in the previous “Defining Calculated Columns” section). However, we can hide a column (even if we cannot hide a calculated measure, like the “Available Amount” we just created using the PivotTable).

image
At this point, we can also move the calculated measure under the Accounts table, which seems the more intuitive place to look for, especially if there aren’t other measures with similar names.

image
In this way, we have the cleanest presentation for the PivotTable user.

image

Calculated Measures Under The Hood

To understand how we created the Available Amount calculated measure, we have to split it into smaller terms (I thanks Marius Dumitru for his help in clarifying me these steps). In the following steps, we will keep the original “Accounts[Amount]” column hidden from the PivotTable.

First of all, we define these two calculated measures:

  • Accounts[Amt] = Sum( [Amount] )
  • Cross[x] = CountRows(Cross)

Apparently, these two formulas calculate the sum of all the rows in the corresponding tables (Accounts and Cross). But, remember, we are defining calculated measures and these formulas are executed in a particular execution context for each cell of the resulting PivotTable. Look at the following picture.

image
The x calculation has been filtered by the selection of Customers and Accounts: this is regular, because the Cross table is like a fact table, which is filtered by the selections made on all of the dimensions. However, the Amt calculation is filtered only by Account selection and is not affected by the Customer in the context. In fact, there are not direct relationships between Customers and Accounts tables. To get the right Amount value, we need to filter the Amt sum also by Accounts that are owned by selected Customers, and to do this, we can filter only the rows in the Account table that have at least one corresponding row in the Cross table for the current Account and Customer in the execution context. For this reason we use the FILTER function to get the accounts for the current selection of customers. These accounts will have at least one row in the Cross table, which is related to the Customer table, and this predicate will filter all the accounts we have to sum to get the desired result. The following expression implements this logic, returning the set of accounts to be summed for the current context.

( Filter(Values(Accounts[Account]), [x] > 0) )

At this point, it is sufficient to reiterate this process for each cell that have to be calculated. The same formula can be written with a shorter version that eliminates the CALCULATE syntax.

Calculate( [Amt], ( Filter(Values(Accounts[Account]), [x] > 0) ) )

[Amt] ( Filter(Values(Accounts[Account]), [x] > 0) )

Please note that the calculated measures [Amt] and [x] doesn’t need to be preceded by the name of the table – other than calculated columns, the name of the owning tables has only two purposes for calculated measures:

  • Providing a “default table” that will be used when you write simple aggregation functions without specifying a table: writing Sum([C]) in a measure will get expanded to Sum(DT[C]) (or SumX(DT, [C])), where DT is the table that the measure is defined on.
  • Defining where (under which table) the UI should the measures in the FieldList (like DisplayFolder does in MDX).

Thus, starting from this formula:

Calculate( [Amt], ( Filter(Values(Accounts[Account]), [x] > 0) ) )

We can obtain our [Available Amount] calculated measure by simply replacing [Amt] and [x] terms (we use the Accounts[Amount] explicit term so that the formula can be defined under any table of the model):

Calculate( Sum(Accounts[Amount]), ( Filter(Values(Accounts[Account]), CountRows(RelatedTable(Cross)) > 0) ) )


The following ones are alternative ways of writing the same calculation (please note we are using an additive measure like Amount – in case of non-additive measures, the SumX alternatives don’t work well).

Calculate(Sum([Amount]), Filter(Values(Accounts[Account]), Calculate(CountRows(Cross)) > 0))

SumX(Filter(Values(Accounts[Account]), CountRows(RelatedTable(Cross)) > 0), SumX(RelatedTable(Accounts), [Amount]))

SumX(Filter(Values(Accounts[Account]), CountRows(RelatedTable(Cross)) > 0), Calculate(Sum([Amount])))

SumX(Filter(Values(Accounts[Account]), CountRows(RelatedTable(Cross)) > 0), [Amt])

Final considerations

When you look at PowerPivot, you might think that calculated columns and calculated measures are pretty similar, just because they share the same expression language (DAX). However, they are really different and provide complementary tools to implement more advanced calculations. I don’t like the fact that calculated measures belong to the PowerPivot model but can be defined only using a PivotTable. I would prefer having also a dedicated tool into the PowerPivot window to access calculated measures definitions.

Calculated measures are flexible enough to make us implement a many-to-many relationship calculation. This is a very important feature to handle more complex models presenting data in an intuitive way. I’m just at the beginning of the exploration of these capabilities and I still hadn’t time to analyze performance and scalability of such a calculations. In the future, I’d like to compare features and performance with equivalent “traditional” SSAS models.

Published Monday, December 07, 2009 8:30 AM by Marco Russo (SQLBI)
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Davide Mauri said:

Hi Marco

I don't know what you think, but if PowerPivot has to be a "Self-BI" tool and thus should be used by power-users (but still users not developers), this seems by far too complicate for them IMHO...

December 9, 2009 4:32 PM
 

Marco Russo (SQLBI) said:

I know, but to be honest many-to-many relationship are not something for the newbie.

However, PowerPivot may be not so intuitive even for more trivial task, like date comparison, YTD and role dimensions. I'll talk about this in future posts...

December 9, 2009 4:39 PM
 

terry hull said:

If powerpivot is to be the freindly easier to use extension of excel as promised then these sort of work arounds are totally unacceptable.

once you start playing with millions of rows the last thing you can afford is an overly complex system in the hands of normal/power users.

My testing (having been excited by the size and perceived strength)shows that no it in fact does not add or filter numbers as expected.

(really the hallmark of excel) with this serious issue around adding numbers, i think we will need to rethink if we allow any usage.

January 12, 2010 12:57 AM
 

Jerry Artman said:

I get an error when trying this.

File one - Claims with admitNo as the key

File two - Payments with admitNo as the key

both are many to many, multiple services and payments for same admission.

I take the claims admit column, make a copy and delete duplicate admits.

I duplicate that column (only because I tried it with just the single column and that didn't work.

Import and set the relations just like in this example.

When I create a column in the claims table to get the sum of the payments

" =sumx(RELATEDTABLE(admitXlink),RELATED(payments[Amount])) "

It returns an error "The column 'payments[Amount]' either doesn't exist or doesn't have a relationship to any table available in the current context. "

Since I picked the column from the autocompleter and can see it in the table tab I am at a loss, why?

Ideas.

September 22, 2010 7:38 AM
 

Marco Russo said:

It seems from your description that a relationship between admitXlink and payments tables doesn't exist. Is it your case?

September 23, 2010 7:34 PM
 

Alberto Ferrari said:

If you have already read this post from Marco Russo and have understood everything, then this new post

October 19, 2010 6:43 AM
 

Tim Webber said:

Aarrgh!  Thanks Marco.  So far, from what I have seen, the only reason to use PowerPivot is for slicers.  I like them and it seems they dont exist for Excel 2010 against SSAS 2008 cubes.  Very sad about that.  I'll take SSAS modelling capablities anyday over this.  I reserve the right to change my mind (because I am just getting into this) but this many to many issue will cause me great amounts of grief.  I have over 25 measures in one fact table that will be used and this fact table has a many to many relationship that must be modelled.  I then at least 25 new calculated columns and a bunch of calculated measures to with them.  :(.

February 9, 2011 9:09 PM
 

Marco Russo (SQLBI) said:

Tim,

Excel 2010 supports slicer also on SSAS cubes.

The user interface is slightly different - you have to use the "Insert Slicer" button in the PivotTable Tools / Option ribbon that you see when you select a cell inside a PivotTable.

Marco

February 10, 2011 11:43 AM
 

Hakon said:

Hi and thanks for a very nice article!

When I create the measured field "available amounts" as you have described in the first section below "Defining calculated measures" the "available amounts" does not turn up as "Sum of available amounts" just "available amounts" in the Values section. Although the actual calculated values per line item A, B or C is correct, the subtotals per Marco, Hal and Chris are wrong (it's still the total amount i.e. 75). There is no way to right-click the element and do a "summerize by". Why is this happening? It does not happen when I later created the "amt".

Best regards

Hakon

September 23, 2011 6:13 AM
 

Marco Russo said:

Hakon,

the "Sum of..." prefix appears whenever you move a column into the values area, an implicit measure is defined in these cases and the name is automatically defined.

Whenever you create an explicit measure with a DAX formula, the name is exactly the one you defined.

Marco

September 23, 2011 2:33 PM
 

Tommy said:

Hi Marco,

I wonder if there is a typo error is the following paragraph quoted from the above article:

"However, the Amt calculation is filtered only by Account selection and is not affected by the Customer in the context. In fact, there are not direct relationships between Customers and Accounts tables. To get the right Amount value, we need to filter the Amt sum also by Account, and to do this, we can filter only the rows in the Account table that have at least one corresponding row in the Cross table for the current Account and Customer in the execution context. "

Is "filter the Amt sum also by Account" suppose to read as "filter the Amt sum also by Customer"?

I'm confuse by the statement. Please help. Thanks

December 6, 2011 7:45 PM
 

Tommy said:

Hi Marco,

I wonder if there is a typo error is the following paragraph quoted from the above article:

"However, the Amt calculation is filtered only by Account selection and is not affected by the Customer in the context. In fact, there are not direct relationships between Customers and Accounts tables. To get the right Amount value, we need to filter the Amt sum also by Account, and to do this, we can filter only the rows in the Account table that have at least one corresponding row in the Cross table for the current Account and Customer in the execution context. "

Is "filter the Amt sum also by Account" suppose to read as "filter the Amt sum also by Customer"?

I'm confuse by the statement. Please help. Thanks

December 6, 2011 7:45 PM
 

Marco Russo (SQLBI) said:

Tommy,

the statement meaning is: in order to filter by Customers, you have to identify the accounts of the selected customers and then filter the Amt by these accounts.

Anyway, I edited the text to clarify this.

Marco

December 7, 2011 12:18 AM
 

Tommy said:

Thanks !! That clear my confusion.

December 7, 2011 2:40 AM
 

Mayur said:

Hi Marco,

I'm working with Many to Many relationship data in PowerPivot.

I have 3 tables comp, Intro & Adv(Lookup table).

I have created relationships between table comp - Adv and Intro - Adv.

Then create a pivot on this.

I'm not getting desired results when I'm using "attribute" as a measure in Pivot.

e.g I'm creating new measure as

=iF(calculate((comp[Sum of Final MP]),FILTER(VALUES(Comp[Final MP I]),COUNTROWS(RELATEDTABLE('Adv Tagging'))>0))>250000,"2N 3D","All the Best")

Please provide any solution on this!!!

December 14, 2011 1:40 AM
 

Marco Russo (SQLBI) said:

Tommy, sorry but your description is not very clear - If I understand well, your "Adv" is the bridge table between comp and Intro. But not having the complete structure of the table and the purpose of what you want to obtain, and what are the numbers you expect with a sample data and the supposed wrong result you obtain, it's hard to understand where is the issue.

Marco

December 14, 2011 2:29 AM
 

Mayur said:

Actually I want to filter out the data from both Intro & Adv table.

But when i used the above new calculated measure then it show all the details which is present in bridge table Adv.

December 14, 2011 2:44 AM
 

Marco Russo (SQLBI) said:

Mayur (sorry for wrong name in comment before), again, I don't have the whole picture.

December 14, 2011 3:26 AM
 

Mayur said:

See I have Advisor information in Adv Tagging table which has "Adv Code" as my Primary key and i want to relate my other two tables which has "Adv code" as well as amount columns for intro it is "starting amount" and for Comp it has "Close Amount" now when i take a pivot I just want those advisor from Intro & comp tables but above calculated measure not filtring the data properly. It is showing all the advisors in bridge table(Adv Tagging) in the Pivot.

Can you pls suggest me right syntax for it.

Regards,

Mayur

December 14, 2011 4:17 AM
 

Marco Russo (SQLBI) said:

Mayur,

I'm really sorry but I am confused.

Please, can you show me the list of tables and, for each table, the list of columns you have?

December 14, 2011 4:09 PM
 

Mayur said:

See I have following columns:

Intro Comp Adv Tagging

Adv Code Adv Code Adv Code

Amount Final MP Adv Name

Final MPI

Now my Intro & Comp tables are my transaction tables having duplicate "Adv Code". Now i can't established direct many-to-many relationship between these 2 tables, so I'm using bridge table as "Adv Tagging" which has unique "Adv Codes". Now when I set relationship through bridge table I can able to show the measure Amount from Intro & Final MP from Comp in front of common Adv Code from Adv Tagging table.

But when I want to use above formula as if(comp[Final MP]>250000,"2N 3D","All the Best") the relationship is no longer valid. Can you pls provide any further light on this.

December 14, 2011 11:27 PM
 

Mayur said:

Hi Marco,

When I use "Create Linked Table" I get following error:

"Unable to load the Tables in PowerPivot Window".

Is there any table limitations of loading in PowerPivot?

============================

Error Message:

============================

The given key was not present in the dictionary.

============================

Call Stack:

============================

  at System.ThrowHelper.ThrowKeyNotFoundException()

  at System.Collections.Generic.Dictionary`2.get_Item(TKey key)

  at Microsoft.AnalysisServices.Modeler.SheetTabs.PopulateSheetList(String selKey)

  at Microsoft.AnalysisServices.Modeler.SheetTabs.SetSelectedSheet(String key)

  at Microsoft.AnalysisServices.Modeler.ClientWindow.RefreshClientWindow(String tableName)

============================

December 14, 2011 11:58 PM
 

Marco Russo (SQLBI) said:

Mayur,

it seems you have a particular type of M2M relationship.

Usually, you define a 1-to-many followed by a many-to-1 relationship, and the bridge table has more rows than the other table.

In this case you have a many-to-1 followed by a 1-to-many relationship and you are in the opposite situation, even if, at the end, this is also a M2M relationship.

However, at this point I don't understand the role of

if(comp[Final MP]>250000,"2N 3D","All the Best")

calculation.

It makes sense that you put this on the Comp table in a calculated measure, right? If it doesn't want to do that, you might want to define it in the Intro table, and in this case the calculation has to be changed.

What do you want to do?

I cannot say what is the other error, it depends on data you have...

December 15, 2011 12:28 AM
 

Mayur said:

As u point out I'm creating this measure

RewardC = if(comp[Final MP]>250000,"2N 3D","All the Best") in Comp table only,

Also I'm creating

RerwardI = if(Intro[Amount]>250000,"2N 3D","All the Best") on Intro Table.

but as i told u earlier I want to show data side by side for Intro as well as Comp table against common adv codes from bridge table.

i.e my report will be

AdvCode RewardC RewardI

But it is not at all filtering data correctly it showing all the Adv codes present in Bridge table. Please let me know u get my concern.

December 15, 2011 12:47 AM
 

Marco Russo (SQLBI) said:

Mayur,

and that's correct.

Sorry but I'm still missing your goal.

If I don't understand what you want to obtain, it's hard to be helpful.

You are taking for granted that I know what your desired result should be, but this is not clear to me!

An example of source data and desired result and actual result would be helpful.

Marco

December 15, 2011 1:00 AM
 

Mayur said:

Please find sample data as required:

Please let me know any other information u want

IntroData

Adv Code Final MP I

100005 3083

100005 1046.33

100007 11284

100165 3452

100165 3329.65

100182 1999.99

100188 2999.99

100238 12995

100238 5106.44

100238 9048.76

100238 16468

--------------------------------------------

CompData

Adv Code Final MP

100005 1030

100165 3278

100165 3130

100182 1970

100188 2954

100238 7904

100238 4460

100238 16217

100276 100000

---------------------

Adv Tagging Data

Adv Code Adv Name

100005 A

100006 B

100007 C

100008 D

100009 A

100010 B

100011 C

100012 D

100013 A

100014 B

100015 C

100016 D

100017 A

100018 B

100019 C

100020 D

100021 A

100022 B

100023 C

100024 D

100025 A

100026 B

100027 C

100028 D

100029 A

100030 B

100031 C

100032 D

100033 A

100034 B

100035 C

100036 D

100037 A

100038 B

100039 C

100040 D

100041 A

100042 B

100043 C

100044 D

100045 A

100046 B

100047 C

100048 D

100049 A

100050 B

100051 C

100052 D

100053 A

100054 B

100055 C

100056 D

100057 A

100058 B

100059 C

100060 D

100061 A

100062 B

100063 C

100064 D

100065 A

100066 B

100067 C

100068 D

100069 A

100070 B

100071 C

100072 D

100073 A

100074 B

100075 C

100076 D

100077 A

100078 B

100079 C

100080 D

100081 A

100082 B

100083 C

100084 D

100085 A

100086 B

100087 C

100088 D

100089 A

100090 B

100091 C

100092 D

100093 A

100094 B

100095 C

100096 D

100097 A

100098 B

100099 C

100100 D

100101 A

100102 B

100103 C

100104 D

100105 A

100106 B

100107 C

100108 D

100109 A

100110 B

100111 C

100112 D

100113 A

100114 B

100115 C

100116 D

100117 A

100118 B

100119 C

100120 D

100121 A

100122 B

100123 C

100124 D

100125 A

100126 B

100127 C

100128 D

100129 A

100130 B

100131 C

100132 D

100133 A

100134 B

100135 C

100136 D

100137 A

100138 B

100139 C

100140 D

100141 A

100142 B

100143 C

100144 D

100145 A

100146 B

100147 C

100148 D

100149 A

100150 B

100151 C

100152 D

100153 A

100154 B

100155 C

100156 D

100157 A

100158 B

100159 C

100160 D

100161 A

100162 B

100163 C

100164 D

100165 A

100166 B

100167 C

100168 D

100169 A

100170 B

100171 C

100172 D

100173 A

100174 B

100175 C

100176 D

100177 A

100178 B

100179 C

100180 D

100181 A

100182 B

100183 C

100184 D

100185 A

100186 B

100187 C

100188 D

100189 A

100190 B

100191 C

100192 D

100193 A

100194 B

100195 C

100196 D

100197 A

100198 B

100199 C

100200 D

100201 A

100202 B

100203 C

100204 D

100205 A

100206 B

100207 C

100208 D

100209 A

100210 B

100211 C

100212 D

100213 A

100214 B

100215 C

100216 D

100217 A

100218 B

100219 C

100220 D

100221 A

100222 B

100223 C

100224 D

100225 A

100226 B

100227 C

100228 D

100229 A

100230 B

100231 C

100232 D

100233 A

100234 B

100235 C

100236 D

100237 A

100238 B

100239 C

100240 D

100241 A

100242 B

100243 C

100244 D

100245 A

100246 B

100247 C

100248 D

100249 A

100250 B

100251 C

100252 D

100253 A

100254 B

100255 C

100256 D

100257 A

100258 B

100259 C

100260 D

100261 A

100262 B

100263 C

100264 D

100265 A

100266 B

100267 C

100268 D

100269 A

100270 B

100271 C

100272 D

100273 A

100274 B

100275 C

100276 D

December 15, 2011 1:18 AM
 

Mayur said:

Also find desired results:

Adv Code Final MP I Final MP C RewardI RewardC

100005 4129.33 1030 All The Best All The Best

100007 11284 All The Best All The Best

100165 6781.65 6408 All The Best All The Best

100182 1999.99 1970 All The Best All The Best

100188 2999.99 2954 All The Best All The Best

100238 43618.2 28581 All The Best All The Best

100276 100000 All The Best      All The Best

------------------------------------------

Sample Result obtained:

Adv Code Final MP I Final MP C RewardI RewardC

100005 4,129 1,030 All the Best All the Best

100006 All the Best All the Best

100007 11,284 All the Best All the Best

100008 All the Best All the Best

100009 All the Best All the Best

100010 All the Best All the Best

December 15, 2011 1:33 AM
 

Mayur said:

Hi Marco,

Did u get my issue regarding Many-to-Many relationship??

Plese let me know if you required more data reagarding this issue.

Regards,

Mayur

December 16, 2011 4:19 AM
 

Marco Russo (SQLBI) said:

Yes but I'm too busy these days, I've put it in my todo list.

December 16, 2011 4:46 AM
 

Mayur said:

Hi Marco,

As Excel has remove duplicate option, how we can implement this in Power Pivot???

I have million rows data & I want to remove duplicate policy codes how can I do this??

Regards,

Mayur

December 16, 2011 5:23 AM
 

Marco Russo (SQLBI) said:

Mayur,

you cannot remove duplicate in a PowerPivot table, at least in current release.

To obtain the result you asked, you have to create two measures and not two calculated columns.

Here are the formula you need.

RewardC = if(SUM(comp[Final MP])>250000,"2N 3D","All the Best")

RerwardI = if(SUM(Intro[Final MP])>250000,"2N 3D","All the Best")

I still don't understand what is the goal of such a calculation, but you are using a simple 1-many relationship on two different tables, without leveraging a many-to-many relationship.

I order to get the result you want, you have to put the Adv Code column of AdvTagging table on Rows in your PivotTable.

Marco

December 17, 2011 2:29 AM
 

Mayur said:

Hi Marco,

While copy 25000 data from excel to Power Pivot, I'm getting following error.

Can you please let me know what is the Rowcount limitation for Power Pivot while pasting the data?

Unable to copy the data to the Clipboard.  The amount of data you selected to copy might exceed the amount of data that can be copied at one time.  Try to copy a smaller amount of data. The process can be repeated as many times as necessary in order to copy all the data.

December 21, 2011 4:15 AM
 

Mayur said:

Hi Marco,

Happy New Year!!!

I've create Pivot table using 3 PowerPivot tables.

Currently my Pivot contains 10 dimensions & 10 measures with 31,000 rows.

If I try to add 11th measure or 11th dimension then I'm getting following errors:

1) "The command was canceled. Please press F1 to get the help topic "Excel Window: PowerPivot Field List" for more details". It's not allowing me to add either this measure or dimension.

2) The XML for Analysis request timed out before it was completed.

My Machine's configuration is as follows:

"Intel(R) Core(TM) 2 Duo CPU E7300 @ 2.66GHz"

Installed Memory(RAM): 4 GB (2.99 GB usuable)

32-bit Operating System .

Please let me know solution on this issue.

Regards,

Mayur

January 3, 2012 2:43 AM
 

Marco Russo (SQLBI) said:

Sorry I've never ssen this issue.

You should try writing here:

http://social.msdn.microsoft.com/Forums/en-US/sqlkjpowerpivotforexcel/threads

There are more people (also from MS support) that might have already seen a similar issue.

January 3, 2012 2:59 AM
 

Mayur said:

Thanks for ur reply.....

Can u pls let me know what configuration of machine to be used if we want to work on millions of rows of data??

Is my machine's configuration is okay??

"Intel(R) Core(TM) 2 Duo CPU E7300 @ 2.66GHz"

Installed Memory(RAM): 4 GB (2.99 GB usuable)

32-bit Operating System .

Regards,

Mayur

January 3, 2012 4:15 AM
 

Marco Russo (SQLBI) said:

The machine is good, the problem is that in order to understand whether you have too many rows it is important to know how many columns you have and with how many distinct values in each column.

As a rule of thumb: if the Excel file you save with all of your data is below 100Mb, you shouldn't have major issues. The larger the workbook, the larger is the memory that you use once you start working on it.

January 3, 2012 4:19 AM
 

Mayur said:

In Total I have more than 50 columns in those 3 tables!!!

Approximately each column has more than 20,000 distinct values in it...

My current excel file size is 45MB, but still my pivot is working very slow, if I try to add single field it takes more than 10 minutes!!

Can you pls suggest any optimise technique!!!

Regards,

Mayur

January 3, 2012 4:50 AM
 

Marco Russo (SQLBI) said:

It's big bot not too much. Time do add field is very long, maybe is the PC too slow (are you sure you disabled power saving?)

January 3, 2012 6:45 AM
 

Mayur said:

No PC is not at all slow.

It's a brand new machine, very few softwares are installed on it.

Infact it has 35 GB Free space on C Drive & 230 GB free Space on D Drive.

January 3, 2012 7:02 AM
 

Marco Russo (SQLBI) said:

PowerPivot is a CPU-intensive application, free space on hard disk doesn't matter and CPU speed is the only important thing.

Again: have you checked performance by disabling any power saving?

January 3, 2012 7:06 AM
 

Mayur said:

Can you please guide me what exact options I need to change for disabling power saving???

January 3, 2012 7:14 AM
 

Marco Russo (SQLBI) said:

Contro Panel / Hardware / PowerOptions / Choose a Power Plan / High Performance

January 3, 2012 7:20 AM
 

Mayur said:

Please suggest values for 2 options:

Default values are:

1) Turn of the Display: 10 Minutes

2) Put the Computer to Sleep: 20 Minutes

January 3, 2012 7:24 AM
 

Marco Russo (SQLBI) said:

It doesn't matter, what is important is that you choose the High Performance profile that doesn't lower the clock speed to save battery.

January 3, 2012 7:39 AM
 

Mayur said:

Hi Marco,

Please find below table shows time taken as well as memory used for adding dimension one by one in Pivot table after doing the settings you suggested. But when I try to display pivot as "Classic PivotTable Layout" I'm getting

"Memory Error:Allocation Failure: Not Enough Storage is available to process this command"

Dimension Measures Total Cached Physical Memory use Available after Dimension Added Time Taken

1 10 3 GB 679 MB 1.2 GB 2.3 GB 3 min

2 10 3 GB 679 MB 1.25 GB 2.25 GB 3 min

3 10 3 GB 679 MB 1.27 GB 2.23 GB 4 min

4 10 3 GB 679 MB 1.30 GB 2.2 GB 4 min

5 10 3 GB 679 MB 1.33 GB 2.05 GB 4 min

6 10 3 GB 679 MB 1.35 GB 1.90 GB 4 min

7 10 3 GB 679 MB 1.37 GB 1.95 GB 5 Min

8 10 3 GB 679 MB 1.40 GB 1.85 GB 5 Min

9 10 3 GB 679 MB 1.43 GB 1.7 GB 5 Min

10 10 3 GB 679 MB 1.55 GB 1.6 GB 5 Min

11 10 3 GB 679 MB 1.55 GB 1.45 GB 5 Min

Request you to suggest solution on this...

January 4, 2012 1:23 AM
 

Marco Russo (SQLBI) said:

There might be a lot of reasons for that.

As I said, I suggest you writing on the MSDN forum in order to enlarge the audience that might help you with this.

January 4, 2012 1:59 AM
 

Mayur said:

Hi Marco,

My PowerPivot worksheet contains total 70,000 rows.

Now based on some filters my data size reduced to 55,000 rows in PowerPivot.

When I try to copy & paste this data several times in Excel I'm getting some duplicate values i.e. the filtered data pasted in Excel is incorrect data.

Please suggest any solution on this.

January 4, 2012 5:06 AM
 

Tommy said:

Hi Marco,

I encountered an issue when using excel's  powerpovot and appreciate you could advise me.

While I was using the powerpivot's pivot table by slicing the data fields and refreshed for updates, the slicers areas in the field list window, out sudden, disappeared by itself and it looks like the powerpivot's pivot table get converted into normal exel pivot table. May I know why is this happen? I tried to rebuild the powerpivot' pivot table again by erasing the previous normal excel pivot, unfortunately I still encountered the same issue again while I was working on it.

For your info, the powerpivot windows consists of tables I imported from both access as well as excel. And the table imported consist of calculated columns using DAX.

Regards Tommy

January 4, 2012 9:58 AM
 

Marco Russo (SQLBI) said:

Tommy, it could be an issue of the PowerPivot add-in. I would suggest you to check whether you have a recent version or not and then, as I suggested to Mayur, it would be better to write these questions on MSDN forum (see address below) for PowerPivot - there are more people reading there that might have seen a wider range of issues.

January 4, 2012 10:06 AM
 

mayur said:

Hi Marco,

When I add new columns in worksheet & arrange these columns position from last to middle, it works well.

But when I refresh my data then again those newly added columns position gets changed to last one.

Is there any solution for fix the postion of newly added columns in powerpivot worksheet???

Regards,

Mayur

January 10, 2012 4:43 AM
 

Susana said:

Thanks !

I was desperate trying to find out how to do that, your paper has been a great help!

March 14, 2012 10:20 AM
 

Danny said:

I guess in the minds of the PowerPivot developer all data comes from textbook examples. Real data is dirty, doesn't lend itself to uniqueness, and certainly is never immune from many-to-many relationships. For this reason alone PowerPivot is IMO a dud.

April 18, 2012 10:04 AM
 

Emil Glownia said:

Thanks that worked for me.

November 30, 2012 6:15 AM
 

Bob Phillips said:

I have a many to many relationship where I need to sum all values, overriding a particular pivot filter.

I managed to adapt your example to show the problem. I created an example with account years, by:

- adding a Year column to the Accounts table

- adding a Year column to the Cross table

- adding extra rows so that one customer had account amounts for more than one year

- added a calculated column AccountYear to Accounts, concatenating Account and Year

- added a calculated column AccountYear to Cross, concatenating Account and Year

- changed the join in PowerPivot for Cross->Accounts to the calculated AccountYear columns.

Everything at this point worked as per your example, and of course I can add Year to column headers and get an analysis over Year as well.

I then tried to create a measure to sum the account amount irrespective of the year filter, so that in the pivot, the basic measure would show the amount for 2011 for each customer for 2011 and another would show the the total amount for all years for that customer, and so on for each year. Easy enough you might think, just add ALL to the measure. So I added this measure:

Accounts[All Years]: =CALCULATE(

   SUM(Accounts[Amount] ),

   FILTER(

       VALUES( Accounts[AccountYear] ),

       COUNTROWS( RELATEDTABLE( Cross ) ) > 0

   ),

   ALL( Accounts[Year] )

)

This did not work. It returned the exact same result as the first measure, no year filter over-ride is happening.

Any idea how I can get the result I need? I might add that I also tried Gerhard Brueckl's M2M technique, and had the same problem.

February 19, 2013 6:22 AM
 

Marco Russo (SQLBI) said:

Bob, it's not clear to me the model you create and the purpose of your analysis. Probably you are not overriding filters over some table that is related to Accounts and the existing relationships still applies, so that your ALL( Accounts[Year] ) is put in an AND condition with the filter coming from another table - you should remove the filter also in such a table, but I prefer not to give you a tentative solution if I am not certain of the issue you have.

Please, take a look at the M2M whitepaper here:

http://www.sqlbi.com/articles/many2many/

You will find many useful information and DAX formula that are much faster than the ones described in this blog post.

Marco

February 19, 2013 6:48 AM
 

Bob Phillips said:

Marco, my actual model is for managed funds, each fund can have multiple managers, and each person can manage multiple funds. Also, the fund could be managed by different people in different years.

My thinking in this example was that by adding account year to your model, we had a very similar structure, and so I did this and I managed to replicate the issue.

I see what you are saying that adding ALL( Accounts[Year] ) might not override that relationship elsewhere, but your model is very simple, it only has year in Accounts and in Cross, and in my pivot the column header Year is Accounts. So I am struggling to see where else the relationship still applies.

I have seen the white paper, but it is a lot to digest, and whilst it might offer better techniques, does it explain why the filter over-ride does not apply here?

February 19, 2013 7:42 AM
 

Marco Russo (SQLBI) said:

Bob, sorry if I don't have time to create a repro for your scenario.

If I understand well, you have the year in the cross table, so when you filter a manager you get all its accounts, but not the accounts of the same manager in other years (is this what you want?).

But the point is that if you put the year in the relationship, you are removing the year from the Accounts column but not from the AccountYear column used for the relationship...

Fundamentally, regardless of your Accounts[Year] column you have a different account for every year at this point. I am not sure that this satisfy your requirements and probably this should be designed in a completely different way.

Generally speaking, I would keep the relationship between accounts, year and managers in a cross table, filtering there (on Cross) instead than on Account - but maybe my interpretation of business requirements is wrong.

Anyway, in case you need to outsource this design, I do this for job :-) http://www.sqlbi.com/consulting/remote/

February 19, 2013 7:56 AM
 

prafula said:

Hi Marco

Before my question i need to say a big thanks for your articles in powerpivot/tabular.

my question-

i have an issue with m21-12m-m21

fact-X-XY-Y

on using your formula from your m2m revolution book

calculate(sum(fact[value]),summarize(X,XY(col))) (Denali Version)

I have acheived to get the values for XY and Y on each and every row. But my issue was at the grand totals. The totals in XY and Y were giving the totals of X table. As each number in X contains multiple names in XY

Thanks

Prafula

May 22, 2013 10:43 PM
 

Marco Russo (SQLBI) said:

The formula should be

CALCULATE ( SUM ( fact[value] ), SUMMARIZE ( XY, X[col] ) )

but you can use also:

CALCULATE ( SUM ( fact[value] ), XY )

which is much simpler.

See this article for an explanation:

http://www.sqlbi.com/articles/optimize-many-to-many-calculation-in-dax-with-summarize-and-cross-table-filtering/

May 23, 2013 1:48 AM
 

Prafulla said:

Hi the issue was

fact(num)-X(num-PK)-XY(num,name)-Y(name-PK)

with the above formula you have suggested. I am getting the values at row level in Y but the grand total its giving only num not the name ( as each num have multiple names).Can you please tell me how can i make the grand totals at Y level for all names

Thanks

Prafulla

May 28, 2013 10:25 AM
 

prafulla said:

In the above link which you have provided.

you are summarizing with accounts, if i want to get total for all the Customers in DIM_customer, do the formula works ?..

May 28, 2013 10:59 AM
 

Marco Russo (SQLBI) said:

Prafulla, I'm sorry but I didn't understand what is the issue you have. Can you be more specific? Thanks,

Marco

May 28, 2013 5:50 PM
 

Prafulla said:

FACT Table(ID-Account,amount) - DIM Acct(Id-Account) - Bridge Account Customer(ID-Account, ID-Customer) - DIM Customer(Id-Customer,Customer Name)

By using this formula

CALCULATE ( SUM ( fact[amount] ), SUMMARIZE ( Bridge Account Customer, DIM Acct[Id-Account] ) )

but how can i summarize it for  customer names. By using the above formula am getting the grand totals of accounts for customer.How can i get the grand total for all customers.If an account has more than one customers.

May 29, 2013 10:47 AM
 

Prafulla said:

Account  Amount

1234 -  56

6789 -  67

Total - 123

Customer

xyz - 24

abc - 46

ghj - 34

fghj -46

Total- 123

In the above example you can see the total values are good for each and every account but when it comes to customer its giving the same total amount 123, but it should be 150

May 29, 2013 11:04 AM
 

Marco Russo (SQLBI) said:

Sorry, but the value in one account cannot be calculated more than once just because the account is owner by more customers! If you waant such a calculation, you have to write another formula, but are you really sure you want that?

I never heard anyone with a many-to-many relationship asking for that - which would be easy to do with a JOIN in SQL but is exactly what usually nobody want to do (counting the same account more than once when you Group more customers).

Please let me know what is your exact use case, I'm interested to that.

Marco

May 29, 2013 7:02 PM
 

Chris said:

Good stuff, thx

October 9, 2013 12:14 AM
 

ivan said:

mayur wins the ungrateful prick award of the day.  How about pay Marco a consulting fee and stop spaming his awesome blog.  

January 23, 2014 12:37 PM
 

GWP said:

Hello,

We are using PowerPivot (Excel2013 64-bit) to manage timeseries data from many different sources. The Fact table looks something like this:

-Date

-SeriesID

-LocationID

-ParameterID

-DataSourceID

-Value

We have dimension tables for LocationID, ParameterID, DataSourceID, etc, and we have slicers on these dimensions to allow user to select the SeriesID of interest.

We are not actually interested in any measures or aggregation – rather we are just using DataModel/PowerPivot to assemble timeseries data from multiple sources and bring it all under one roof. What we surface to the user is just a timeseries plot showing the date/value for the SeriesID they have selected using the slicers.  So the pivot table has Value in the Value field, Date in the Row Field, and e.g. LocationID, ParameterID, DataSourceID, SeriesID in the column field.

So all is well. However, we have a many-to many issue, in that the user needs to establish groups of SeriesIDs, and one SeriesID can belong to multiple Groups, e.g.:

GroupA SeriesID_1

GroupA SeriesID_2

GroupB SeriesID_1

GroupB SeriesID_3

I understand that we can develop a bridge table and measures that reflect the MTM relationship. But what we’d like to do is reflect the MTM relationship at the column level. That is, I want a slicer on Group, such that if user selects GroupA, then SeriesID_1 and SeriesID_2 appear as columns. And if user selects GroupB, then SeriesID_1 and SeriesID_3 appear as columns.

Is this do-able? Apologies in advance if this is unclear!

Thank you,

GWP

April 15, 2014 12:24 PM
 

Marco Russo (SQLBI) said:

You might just use a measure that returns blank when you have a combination that cannot return values (so that Excel pivot table will hide the empty columns), or you might use a hierarchy between Group and Series, but this requires creating a table that contains all the possible combinations, so that you can create a hierarchy (if this is the combination returned by the M2M, then you have to join these combinations and materializing them into a single table to use as a dimension to join the bridge table).

Marco

April 15, 2014 1:30 PM
 

GWP said:

Marco,

Thank you very much for these two great suggestions. I'll set to work trying to adapt them to our case.

I'm a DAX newbie and have been reading and re-reading your book 2013 Building Data Models with PowerPivot, the white paper, blog posts, etc... The learning curve is steep but enjoyable. Thanks again for all your contributions to this field.

April 17, 2014 3:20 PM
 

Dale said:

Is there a way you can look up the minimum of a range

REF

REF

1

2

I want to get 2, it is not working. I tried everything, I Iam going nuts

August 12, 2014 12:10 AM
 

Dale said:

CORRECTION:

Is there a way you can look up the minimum of a range

REF

REF

1

2

I want to get 1 as the minimum of the range, it is not working. I tried everything, I Iam going nuts

August 12, 2014 12:10 AM
 

Marco Russo (SQLBI) said:

Dale, probably a CALCULATE ( MIN (table[column]), table[column] > 0 ) should work, but I'm not sure about why you have two invalid cells so I don't know if this works.

August 12, 2014 1:08 AM
 

Sherri said:

Hi Marco,

First I want to say thank you to both you and Alberto for your contributions to the BI community and for your advice in the past. I purchased your Power Pivot Workshop Complete bundle and have learned so much. You guys are an inspiration.

I am working on a complex project that involves M2M. I think I have resolved the relationship issue with a bridge table and some RELATED calculated columns in my Fact table (AlarmIncident) but now want to use the DimKPIvalues table to compute the proper KPITarget value based on the CentralStation and Priority. I have attached a link to my model for more clarity.

http://i.imgur.com/jjm5gdg.png

AT 103 Target:=Calculate(COUNTROWS(AlarmIncident), AlarmPriority[alarmque_id]=103,AlarmIncident[AT]< 46)- This calculation works of course but I need the KPIvalue to be variable as the user wants the ability to adjust the values. So here are a FEW of my attempts to get this working. I am suffering from the 'CALCULATE has been used in a TRUE/False expression' error. Any advice would be greatly appreciated.

AT 104 Target:=Calculate(COUNTROWS(AlarmIncident), AlarmPriority[alarmque_id]=104,AlarmIncident[AT]< Calculate(Values(DimKPIvalues[KPITarget]),

Filter(DimKPIvalues, DimKPIvalues[CentralStation] = AlarmIncident[CentralStation]),DimKPIvalues[Priority] = AlarmIncident[priority]))

AT 104 Target:=Calculate(COUNTROWS(AlarmIncident), AlarmPriority[alarmque_id]=104, Countx(AlarmIncident,AlarmIncident[AT]< Calculate(Values(DimKPIvalues[KPITarget]),

Filter(DimKPIvalues, DimKPIvalues[CentralStation] = AlarmIncident[CentralStation]),DimKPIvalues[Priority] = AlarmIncident[priority])))

AT 104 Target:=Calculate(COUNTROWS(AlarmIncident), AlarmPriority[alarmque_id]=104, CALCULATE(AlarmIncident[AT]< Calculate(Values(DimKPIvalues[KPITarget]),

Filter(DimKPIvalues, DimKPIvalues[CentralStation] = AlarmIncident[CentralStation]),DimKPIvalues[Priority] = AlarmIncident[priority])))

AT 104 Target:=Calculate(COUNTROWS(AlarmIncident), AlarmPriority[alarmque_id]=104, CALCULATE(AlarmIncident[AT]< Calculate(Values(DimKPIvalues[KPITarget]),

Filter(DimKPIvalues, DimKPIvalues[CentralStation] = AlarmIncident[CentralStation]) && DimKPIvalues[Priority] = AlarmIncident[priority])))

AT 104 Target:=Calculate(COUNTROWS(AlarmIncident), AlarmPriority[alarmque_id]=104, CALCULATE(Countrows(AlarmIncident),Filter(AlarmIncident,AlarmIncident[AT]< Calculate(Values(DimKPIvalues[KPITarget]),

Filter(DimKPIvalues, DimKPIvalues[CentralStation] = AlarmIncident[CentralStation]) && DimKPIvalues[Priority] = AlarmIncident[priority]))))

September 19, 2014 10:16 AM
 

Marco Russo (SQLBI) said:

Sherri,

please consider that I'm pleased to answer to simple questions on comments and by mail, especially when are related to Adventure Works, so anyone can benefit from a public answer.

Your question require some more analysis and quality time.

I would be happy to provide you direct consultancy for your specific model - we provide also remote consulting, that could be useful for questions like this one.

More info here: http://www.sqlbi.com/consulting/

Thanks,

Marco

September 19, 2014 10:24 AM
 

Sherri said:

Marco,

Thanks for your prompt reply. I will consider that option.

September 19, 2014 11:51 AM
 

Peter said:

Hi Marco, I'm sure you're busy! first off i do enjoy reading your blog, it's got me started in a whole new world of powerpivot tables and relationships.

could you help me with the following problem? i have 2 data sources, well the same source but 2 different tables (ms Dynamics NAV 2013). the end result i'm after is to create a pivot table with slicers but able to show and slice data from both tables.

the tables contain Item ledger entries (customer, date, unique ref no, amount, region etc). there are actually many differences in the columns between the 2 tables but many are the same. both tables contain unique records to be reported on.

I've looked at powerquery and had some minor success but it seems a little cumbersome (out of memory issues on 350,000 rows)

is there a way of creating a new table in powerpivot by combining the 2 tables? this is all a bit new to me so just finding a rough answer will be good, i will then enjoy grinding out the details!

many thanks

Peter

November 18, 2014 1:22 PM
 

Marco Russo (SQLBI) said:

Peter, Power Query is the right tool to use, Power Pivot does not have a tool to manipulate data, that tool is Power Query!

If it doesn't work, you should check why the query does not do query folding (send the query to SQL). Maybe you are reading data from an odata feed that produce memory pressure on Power Query?

However, I'd suggest you to write in the Power Query forum for this:

https://social.technet.microsoft.com/Forums/en-US/home?forum=powerquery

November 18, 2014 2:16 PM
 

Peter said:

thanks Marco, power query it is then!

November 20, 2014 10:20 AM

Leave a Comment

(required) 
(required) 
Submit

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement