Power Pivot Related table not providing the correct results

[Robin_Lindstrom]

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?

[#2]

@Robin_Lindstrom 

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.

[#3]

@Subodh_Tiwari_sktneer 

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

[#4 · Accepted solution]

Spoiler

@Robin_Lindstrom 

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.

[#5]

Thanks a lot! Really appeciate it!

[#6]

You're welcome @Robin_Lindstrom!