Forum Discussion
AS > Tabular Model > How to set multiple relationships between 2 tables?
Hello,
I am new at preparing Tabular Model using SSAS and have got a query which I feel should be very easy for you experts to address. I plan to use this model in Power BI.
My problem is – I am unable to manage 2 relationships between 2 tables.
My situation is -
I have got a calendar table & a fact table. My fact table has got 2 date type columns, say Date1 & Date2 and my calendar table (which is my date table) has got a Datekey column of Date type.
I want to set 2 separate relationships between these tables as –
1. ‘calendar’[datekey] & ‘fact’[date1]
2. ‘calemdar’[datekey] & ‘fact’[date2]
And then prepare 2 separate visualizations with ‘calendar’[datekey] used in the timeline & one visualization should be filtered on ‘fact’[date1] and the other one on ‘fact’[date2].
Please guide how this could be achieved.
6 Replies
- ImkeF
Community Champion
You can import the calender table twice. They normally don't cost (performance).
If you prepare your visualizations, make sure their calendar filters don't interact.
- vinaypugalia
Resolver I
Yes, you can prepare a copy of the calendar table as Imke has guided but please note that as per my knowledge, you will not be able to mark it as a "Date Table" as 1 data model can have only 1 data table.
ImkeF please correct me if I am worng and if this is correct, please suggest a way to handle this.
Thanks!
- MattAllington
You can create a duplicate table as suggested, but you can also create an inactive relationship. I have covered both and the merits of each in this article here: http://exceleratorbi.com.au/multiple-relationships-between-tables-in-dax/
- vinaypugalia
Hello Daxesh,
I have validated and in this case you can go ahead and create multiple calendar tables. Marking as a Date table is required in excel while adding a TImeline control. But for Power BI this seems working fine.
Hence, you can have Calendar table 1 to map with Date1 & Calendar table 2 to map with Date2. Once the maping is done, you can use Calendar table 1 in the timeline in view one to filter your data on Date1 of fact table & use Calendar table 2 for the another view in the same manner.
Hope, this helps!
- MattAllington
vinaypugalia wrote:Marking as a Date table is required in excel
This is only required if you use surrogate keys to link a data table to a calendar table. If you link the tables with a date column, then this is not required. I seem to remember (not 100% sure) that you currently can't use surrogate keys in power bi.
