Forum Discussion
Power Pivot Related table not providing the correct results
Obviously USERELATIONSHIP is straightforward and better than CROSSFILTER where you know what you are doing. CROSSFILTER has some drawbacks unless you know how the filter context is being evaluated in your Pivot Table.
Though in your case both the methods produce the same output, I would prefer USERELATIONSHIP over CROSSFILTER.
The context filter in your existing pivot table is on Date from the date table Test3 and this table has no relationship with the table Test2 that's why it is not showing what you expect it to show.
First create a relationship on Date field of Test2 and Test3 tables like below and this would be an inactive relationship showed as dotted lines.
Then create the following Measure...
Total Contacts:=CALCULATE (
SUM ( Test2[Total Number of Contacts] ),
USERELATIONSHIP ( Test3[date], Test2[TheDate] )
)
And then drag this measure in the Values area of the Pivot Table and it will show you the figures you are trying to see.
Please refer to the attached for more details.
Thank you, that works!
Before I received your answer, I tried this solution as well, it provides the same results, please look in the file attached.
Which way is better 🙂 ?
Kind Regards,
Robin
Obviously USERELATIONSHIP is straightforward and better than CROSSFILTER where you know what you are doing. CROSSFILTER has some drawbacks unless you know how the filter context is being evaluated in your Pivot Table.
Though in your case both the methods produce the same output, I would prefer USERELATIONSHIP over CROSSFILTER.
- Thanks a lot! Really appeciate it!
- You're welcome Robin_Lindstrom!