I need some assistance with centralizing the dates in my data model that contains 2 fact tables and a single dimension. My model is as below. I am struggling to make the "Year" selection global (I do not have a date field in my dimension table, only in my facts), such so that I only have one year field in the front-end which will allow filtering on the entire app. I tried common dates an d the cononical dates but I cannot seem to get this to work. Kindly assist.
Typically with multiple dates I would recommend concatenating all of the facts into a single table and rename the dates so they are all generic (i.e. just Date) and then have a field of DateType also (with Make and Model as values). I don't think that is the best way in this case though.
What I would do here is have a data island with a calendar in it (just Year, if that is all you need) that doesn't join to any other table in the data model. This can be used for selections in the normal way.
Applying the selections to expressions is then a bit more manual, as you then have to use Set Analysis to apply the selection to the expression. There is an operator P() that will apply possible values in one field to one in the expression, so to calculate the Model Purchase Amount it would be:
sum({<ModelYear=P(Year)>}PurchaseAmount)
You could then do the same for the Make also:
sum({<MakeYear=P(Year)>}PurchaseAmount)
The downside of this approach is all of the expressions are slightly more convoluted, but it should work fine and be performant.
In you example you have a bigger problem, in that PurchaseAmount appears in both Fact1 and Fact2, but I presume that is just because you have simplified to post the question?
@Sello_Mputsoe I would create data model as below in this case. As your both Fact tables are at different grain, you can't really concatenate them. I would join them rather as Model is lower grain and Make is at Higher grain. So, first step would be to load Model Fact which is at lower grain than Make Fact. You can join dimension table to the Model so that you will have make, model and Year in Model Fact. Now you can join Make Fact to the model on Make_ID & Make_Year (Because now model fact has make_id as well). I don't think you will need mk_purchase amount as you already have md_purchase amount which is at lower grain which you can be aggregated by make_id. So you will automatically have single reference of year now.
Model: LOAD Model_ID, "Model Year", "MD_Purchase Volume" FROM [lib://DataFiles/Fact Table1.xlsx] (ooxml, embedded labels, table is Sheet1);
Left Join LOAD Model_ID, Make_ID, "Reference No.", "Model Name" FROM [lib://DataFiles/Dimension.xlsx] (ooxml, embedded labels, table is Sheet1);
Left Join LOAD Make_ID, "Make Year" as "Model Year", "Purchase Location Code", "MK_Purchase Volume" FROM [lib://DataFiles/Fact Table2.xlsx] (ooxml, embedded labels, table is Sheet1);
