Show development in hitrate per day - multiple tables

[phelms]

Hi,

I am trying to show the hitrate per day in a bar chart, filtered by date.

The hitrate is a measure, combining data from two tables: Sales and Calls. Both tables contain multiple posts on the same date and by the same user. The measure is: Salgsforsøg% = COUNT(Sale_reg[LID1]) / sum(TLF_application_user[callsanswered])

I created a third table, creating a relationship between the user id from both tables to a unique user id in the third table (many to one - both ways)

I try to create a fourth table to sync the dates from the two tables, using: TimeDim = CALENDAR(FIRSTDATE(TLF_application_user[date]);LASTDATE(TLF_application_user[date])).

I cannot create a relationship between the Sales and Calls table to the TimeDim table, since this would introduce ambiguity between the two filter tables.

If I leave out the filter, it shows the total count of sales, divided by the filtered amount of calls answered.

How do I work around this problem?

[v-caliao-msft]

@phelms,

Could you please elaborate your data structure? If possible provide us some sample data and screenshots, so that we can make further analysis. You can also upload a pbix file to Onedrive or any web storage and share the link. Do mask sensitive data before uploading.

Regards,

Charlie Liao

[phelms]

I try to connect these two tables by date and user. The connection is many-to-many, but the tables are in different sizes (you can receive a call without making a sale). Each line in the table "Sale_reg" represent a sale.

I also imported a table, containing the unique user ID's to link the tables, and I created a TimeDim table, using this formula: TimeDim = CALENDAR(FIRSTDATE(TLF_application_user[date]);LASTDATE(TLF_application_user[date])).

In the relations, I know the current setup creates an ambiguity, but I can not wrap my head around how to make the connection?

Table overviewRelation overview