Userelationship not showing correct value with date field

[ysherriff]

Hi all

I have a simple formula for userelationship to reflect the date based on received date. See formula below. But for some reason, it is not showing correctly. I have also included the formula for $ Global or Potential in the second image as well as the model schema in the third image.

Opportunity Influenced RFP YTD =

CALCULATE ( [$ Global or Potential RFP Amount],

    DATESYTD ('Date'[Date]),

    USERELATIONSHIP( 'Date'[Date],

    Campaigns[RFP Received Date])

)

Image#1

Image # 2

Image #3

[tamerj1]

Hi @ysherriff 

if you activate this relationship your model becomes ambiguous. You need to deactivate the relationship between Date and Contacts Table using CROSSFILTER- NONE. On the other hand please advise what result would you expect to see in a card visual where there is no filter context?

[ysherriff]

If there is no filter context, the data works. If I physically make the contacts table inactive and make the campaign table active, then it works but it is not scaleable if I want to do calculations from the other table.

I tried to use the crossfilter (none) but having errors as well.

Thanks Tamer

[tamerj1]

@ysherriff 

 

[amitchandak]

@ysherriff , Based on what I got, Try like

CALCULATE (
CALCULATE ( [$ Global or Potential RFP Amount], USERELATIONSHIP( 'Date'[Date],
Campaigns[RFP Received Date])),
DATESYTD ('Date'[Date])

)

[ysherriff]

Thanks Amit but I still get the same outcome.

[ysherriff · Accepted solution]

Thanks to Enterprise DNA for this. Just wanted to post solution.

The reason why I was not able to achieve the results using the “USERELATIONSHIP()” function is because inside the data model, I had created a cyclical relationship between the tables

This is how the data was flowing based on the relationship created -

1). “Contacts Table” is directly linked with “Date Table” based on “Created Date” field.

2). “Campaigns Table” is directly linked with “Contact Table” based on “Contact ID” field.

3). “Campaigns Table” is in-directly linked with “Date Table” based on the fields - “Created Date” and “RFP Received Date”.

And therefore, “Campaigns Table” got linked with the “Date Table” based on “Created Date” field since active relationship took precedence over the in-active relationship, in this case, because of the way model was designed. It followed the chain like this -

“Date Table > Contacts Table > Campaign Table”

In the data model, “Date Table” and “Contacts Table” possess the characteristics of “Dimension Table” whereas “Campaigns Table” is considered as a “Fact Table”.

As per data modelling best practices, relationship between the “Dimension Tables” should not be created. In case, if required, then create a bridging table between them and convert the “Star Schema Model” into a “Snowflake Schema Model”.

In this case, what actually happened is, a relationship was created between the dimension tables i.e., between “Date Table” and “Contacts Table” which ultimately flowed it to the fact table i.e., to “Campaigns Table” and in turn had an adverse effect over the results.