Pivot Table Relationships and Multiple Responses

[NeilMcLeod]

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

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.

Please could someone show where I am going wrong?

[#2]

@NeilMcLeod 

Relationship don't work from one to many

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]  ) )

[#3]

Thank you @SergeiBaklan (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?

[#4]

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

[#5]

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

[#6]

@NeilMcLeod , you are welcome