Modelling

[PDRTXRA]

Hello. I'm having issues building my data model.
I have four tables:

1. Clients Table (each row has an unique ClientID)
2. Transactions Table (each row has and unique TransactionID and a ClientID and a Date)
3. Behaviour Table (each row represents an action on the website and have a ClientID and a Date)

With these 3 tables, I want to build a report where I can select a Date or a interval of dates that tell me how many Clients had a transaction AND had a specific behaviour. 

At the moment I have the Clients Table connected to both Table 2. and 3. via ClientID. But this way I can't connect the Dates.
And I cant build a CALENDAR Table since it gets a loop, where Clients its connected to both and both 2 and 3 are connected to the Calendar Table.

[v-yifanw-msft]

Hi @PDRTXRA ,

Based on the details you have provided, you seem to be on the right track and you can troubleshoot and resolve your issue by following the steps below:
1. Please create a calendar table that covers the date range you are interested in analysing. 
2. Looping issues occur when there are multiple paths between tables that may confuse the calculation engine. To avoid this, make sure that you have a single direct path from the Calendar table to each fact data table and that these fact data tables are not directly connected to each other. For more information, see:

Model relationships in Power BI Desktop - Power BI | Microsoft Learn

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.