Use of Multiple 'USERELATIONSHIP' in a single measure

[pjmorris]

I have a complex problem at work. Currently the dimension table runs to over 100,000 lines. I need to include a 3 dimension which would simply render the current solution unworkable (way too slow). Having discussed it with colleagues who use SQL (I don't) they suggested applying multiple filters (derived from the dimension table) to the fact table. Although you can define multiple relationships in Powerpivot only one can be active at a time. I therefore thought to use 'USERELATIONSHIP'. In the help guide to this function it alludes to being able to use this multiple times in a single measure, but provides no examples. It also says that in certain circumstances if they're nested then the inner most takes precedence. This has left me somewhat confused and I'd be grateful for any advice. I've created a test scenario as follows:

1. Fact Table:

Book1
	A	B	C
2	Key 1	Key A	Value
3	1	a	1
4	1	b	2
5	2	c	4
6	2	a	8
7	3	b	16
8	3	a	32
Sheet1

2. Dimension Table:

Book1
	G	H	I
2	Group	Key 1	Key A
3	House	1	b
4	Boats	2	c
5	Stuff	3	a
Sheet1

3. Required Output:

Book1
	L	M
2	Expected Results
3	House	2
4	Boats	4
5	Stuff	16
Sheet1

4. Clip of the relationships established in Powerpivot. (qryTable1 is Fact Table, qryTable2 is Dimension Table)

5. My attempt at a measure, which does not work:

=CALCULATE(SUM((qryTable1[Value]),
    USERELATIONSHIP(qryTable1[Key 1],qryTable2[Key 1]),
    USERELATIONSHIP(qryTable1[Key A],qryTable2[Key A])
    )

Any advice on how to make this work would be gratefully received.

Many thanks.

 

[RoryA]

Wouldn't it be easier to create a composite key column?

 

[pjmorris]

Hi Rory,

That's the current solution, but the dimension table is already over a 100,000 rows. I did contemplate that option, but if I add in another dimension which requires at least 40 options I end up with about 4,000,000 and I just can't see Excel working quickly enough to satisfy users. The example above is an extremely simplified version of the problem.

Regards

Peter

 

[RoryA]

Well, as far as I know, you cannot use two USERELATIONSHIP functions to activate multiple relationships between the same tables, so I think you will have to either restructure your model (my general feeling is that when something gets horrendously complicated, it usually means your design could be better) or work with something like composite keys.

 

[pjmorris]

Hmmm, sadly, that's what I've suspected. I would agree that a redesign of the structure would probably make sense, but I'm stuck with the current data sources and I don't know enough to conceive a different structure. I have discussed this with someone who has some formal training in data management and we remain with what we've got.

You do give me a suggestion though and that is to try and separate the dimension table into separate parts. I'll let you know how I get on.

Regards

 

[RoryA]

Good luck! ?

 

[pjmorris]

well that turned out to be simpler than I imagined. And I think its a solution I can use in my work problem.

I created two additional tables based on the original Dimension Table. I then linked them as shown below. The relevant measure is here and uses cross table filtering by the inclusion of qryTable2 in the CALCULATE expression, which interestingly is how a many-to-many relationship can be managed.

=CALCULATE(SUM(qryTable1[Value]), qryTable2)