Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
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
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?
User | Count |
---|---|
102 | |
90 | |
80 | |
71 | |
69 |
User | Count |
---|---|
114 | |
100 | |
97 | |
72 | |
68 |