cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nanma94 Regular Visitor
Regular Visitor

Data model loop: cant point 2 transaction dates to DateTable

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?
DataModel.PNGData Model

Thanks much!

NM 

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Data model loop: cant point 2 transaction dates to DateTable

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.
1.PNG

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Super User
Super User

Re: Data model loop: cant point 2 transaction dates to DateTable

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/

 

 

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
nanma94 Regular Visitor
Regular Visitor

Re: Data model loop: cant point 2 transaction dates to DateTable

Thanks much Bhavesh for your reply. Its definitely relevent but still doesnt solve my issue directlySmiley Sad

 

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...

Super User
Super User

Re: Data model loop: cant point 2 transaction dates to DateTable

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.

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
Moderator v-yuezhe-msft
Moderator

Re: Data model loop: cant point 2 transaction dates to DateTable

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.
1.PNG

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.