Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Pivot Table Relationships and Multiple Responses

Copper Contributor

Hi, I'm new to Pivot Tables and relationships and while this feels like a basic question, I can't find a suitable example anywhere so hoping someone can help.

 

I have three tables with relationships

NeilMcLeod_1-1700646300548.png

NeilMcLeod_0-1700646249057.png

One community response I found suggested creating a Measure, which I've tried (as below):

 

 

 

 

Total Value:=CALCULATE(
sum(more_details[Value]),
USERELATIONSHIP(details[Type],'Type'[Type])
)

 

 

 

 

This creates a pivot table with rows 'details[Item]' and 'Type[Type]' and gives multiple entries for type/item.  The table I'm after is shown to the right.

NeilMcLeod_2-1700646317355.png

Please could someone show where I am going wrong?

5 Replies

@NeilMcLeod 

Relationship don't work from one to many

image.png

You may build virtual one Propagating filters using TREATAS in DAX - SQLBI

with

Total Value:=CALCULATE( SUM(MoreDetails[Value]), TREATAS( VALUES( Details[Type] ), MoreDetails[Type]  ) )

 

 

Thank you @Sergei Baklan (and sorry for the delayed response, I had a 'post flooding' issue)

 

I can see that your example works and I think understand how but I'm not quite following why it's necessary.

 

The article you linked to (thank you) and reading around this suggests that that physical relationships are more reliable and refers to bi-directional filters, which look similar to what I started with (snip below). Is this approach not feasible here?

NeilMcLeod_0-1700661002914.png

 

@NeilMcLeod 

Yes, in general physical relationships are better. However,

- many-to-many (bi-directional) is not supported natively in Excel. As workaround here may generate measure with CROSSFILTER which imitates such behaviour

- in general bi-directional is not recommended, that could be negative side effects with it. If you can avoid it - better to avoid.

The only limitation could be performance. In most practical cases for Excel TREATAS works fine. If you don't have dozen millions of rows in the model we usually speak about milliseconds.

@Sergei Baklan thank you so much, this has really helped me out 

@NeilMcLeod , you are welcome