Data model loop: cant point 2 transaction dates to DateTable

[nanma94]

On a high level, I have the requirement to show customers sales, and customer interaction (sales initiated activities) count, and whether customer interaction (activity count) is relate to driving revenue. 

My sales table has month end sales# per customer per month, my activity table has every single activity detail so I count the # of activity records to get the total count per customer - this table has activity date; and I have requirements to count MTD, QTD, and YTD. 

I have a DateTable. But my current data model wouldnt allow me to point both month end sales date, and activity date to the DateTable (it complains about having ambiguity bewteen account and DateTable). Without the linkage from both sides, when I pull both monthly sales and monthly event count in a single PBI table, the data is incorrect.

What would be a solution in this case?
Data Model

Thanks much!

NM 

[BhaveshPatel]

You should checkout this whitepaper. It clearly explains about the Relationship Ambuiguity issue and solutions.

https://powerbi.microsoft.com/en-us/blog/bidirectional-cross-filtering-whitepaper-2/

[nanma94]

Thanks much Bhavesh for your reply. Its definitely relevent but still doesnt solve my issue directly:(

I really need both sales table and event table to point to date table in order to allow a date slicer to drive both to change. Though the white paper points out better not have relationship to a date table bidirectional...

[v-yuezhe-msft · Accepted solution]

Hi @nanma94,

You got the ambiguity warning when making the relationship between the DateTable and Event tables active, right? If so, assume that you are allowed to make the relationship active, in this case, you create an ambiguous path from DateTable to Account, should DateTable traverses Sales Plan Monthly or Event?

In your scenario, you can set cross filter direction of relationship between Account and Event from “Both” to “Single” , that way, you will be allowed to make the relationship between the DateTable and Event tables active.


Or as other post, keep the relationship between the DateTable and Event tables inactive, and you can use USERELATIONSHIP function to override the active relationship when utilizing DAX.

Thanks,
Lydia Zhang

[BhaveshPatel]

I would suggest you to read it carefully on topic  "Ambiguous relationships tables, what do end users want to see?" on Page 11.

It asks to keep the one of the relationships inactive and utilize DAX instead to create what you are looking for. Your data model fits exactly in that scenario.