Mar 25 2021 01:19 AM - edited Mar 25 2021 01:20 AM
Hi guys,
I am in need of some serious help.
I am trying to get the related values from the "Test 2" table regarding Total Number of Contacts for the specific dates. Instead of showing the correct values, you can see that it takes the total number of contacts for both dates, instead of 100 & 200. Does anyone have any idea what it provides the wrong answers?
Mar 25 2021 02:20 AM - edited Mar 25 2021 02:26 AM
Mar 25 2021 02:20 AM - edited Mar 25 2021 02:26 AM
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.
Mar 25 2021 02:45 AM
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
Mar 25 2021 03:06 AM
Solution
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.
Mar 25 2021 04:27 AM
Mar 25 2021 05:48 AM
Mar 25 2021 03:06 AM
Solution
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.