Forum Discussion
Pivot Table Relationships and Multiple Responses
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?
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] ) )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?
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.
