Merging tables with large numbers of rows

[naninamu]

Hi everyone. In theory I know how to do this, but the volume of data is giving me issues so need some help.

Essentially, and this is simplified for the sake of this example, I have 2 tables. 

• One is a Fact type table giving a list of Groups, let's call them Group A, Group B etc. In reality this table is some 20m rows.
• The other is a dimension type table that lists out the Items in each Group, let's call them Item a, Item b etc. Each Group has a mix of different Items, and in reality each Group has around 10 or so Items.
• Each item has a Value assigned to it.

Based on the Groups listed out in Table A, I need to be able to provide a total score for each type of Item.

Initially working with a subset of Test data, I merged the 2 tables and then summed up the scores. This worked for my Test data, but for the actual real data I am running into issues - due to the fact that I have 20m Groups each expanding out into 10 Items = 200m rows - PBI is taking way to long to do this and times out (keeping in mind in real life the data is a bit more complex and the tables wider)

Does anyone have any suggestions of now I might handle this more efficiently? Can't help thinking there's something obvious I'm overlooking here.

Thanks!!

[naninamu]

Hi - OK so if this is my sample data

Table A - lists the Groups

Group

B

A

B

A

B

C

B

A

D

A

B

D

D

Table B - lists the Items that make up each Group and their value

Group	Item	Value
A	a	10
A	b	15
A	d	22
A	e	18
A	g	9
B	a	10
B	b	15
B	d	22
B	g	9
B	h	3
C	b	15
C	c	25
C	d	22
C	f	6
C	g	9
D	a	10
D	c	25
D	d	22
D	e	18
D	f	6

If I were to expand out the Groups into their component Items and summarise the result I'd get this:

Item	Count	Value Sum
a	12	120
b	10	150
c	4	100
d	13	286
e	7	126
f	4	24
g	9	81
h	5	15
Grand Total	64	902

I want to then put it in a scatter to look a bit like this:

'

I can't work out how to get it to sum up all the items for each Group on the X axis - it just sums up what's in Table B when I do it, doesn't take table A into account to account for multiple Groups.

ie - in Table B, which is like the Dimension table, Item a appears 3 times. PBI is giving me 3 lots of 10 equals 30 as the sum, rather than 12 lot of 10 equals 120 - Item a appears 12 times if all the Groups from Table A were to be expanded out.

Thanks in advance for any help,

[lbendlin]

There's a typo in your expected result - g is appearing 10 times not 9 times.  So the total is 911, not 902.

You may want to try TREATAS as an alternative, it will likely perform better with larger data sets

[naninamu]

Thanks - the issue is I need to drag the Sum of each Item onto the X axis of a scatter chart. ( ie the User selects the Item, and there is a scatter chart showing on X the sum of the scores and on Y the  number of times that Item appears. Not quite sure how to do this... Item and Score are both on Table B so if I drag them onto the scatter, it's not going to know I'm trying to expand out the Groups in Table A. Is there some kind of measure I can write to drag onto the axes?

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[lbendlin]

Don't merge them. Let the data model do the work for you. It will be substantially faster.