Forum Discussion
Issue with relationship between fact and datetable
Hello,
I am capturing data for "Daily Optical Sales" which includes the number of spectacles sold per day in one sheet. There is another sheet where the revenue from monthly sales is captured. I have created a "Date Table" in the model. However, I am unable to set the relationship of the "Daily Optical Sales" and "Monthly Revenue" with the Date Table simultaneously.
My tables are as follows:
Daily Optical Sales Table
===============
Date Total OP Total GP (Glass Prescriptions) Conversion (Patients actually purchasing Spects)
01-04-2018 156 85 62
02-04-2018 224 102 87
So on and so forth .....
Monthly Revenue
===========
Month Revenue
Apr-18 254,689
May-18 325,648
So on and so forth .....
My requirement is to filter the visuals as per either a date or a month.
How do I set relationship so as to achieve this?
TIA
2 Replies
Hi deepvibha,
From your description, it seems that you have three tables, and you cannot create the relationship among the three tables?
If it is, do you get any error when you create the relationship? And how do you create the Date table? Dax or M query?
If it is convenient, could you share a dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)
Best Regards,
Cherry
Hi,
As long as the Month column in the Monthly Revenue table is a proper date, you should be able to create a relationship from this column to the Date column of the Calendar Table. I am assuming creating a relationship from the Date column of the Sales Table to the Date column of the Calednar Table is not a problem because i see a proper date there.
If the Date column in the Monthly revenue table is a text date, you can try to convert it into a proper date by writing this calculated column formula
=1*("1/"&Sales[Date])
If this still does not help, then share the link from where i can download your PBI file.
