Forum Discussion
AS > Tabular Model > How to set multiple relationships between 2 tables?
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/
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!
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.
Infact, you can also take below approch to acheive your requirements -
- Create two inactive relationships as below
1. ‘calendar’[datekey] & ‘fact’[date1]
2. ‘calendar’[datekey] & ‘fact’[date2]- In formula , which you have prepared and going to plot on visual, you can use DAX function USERELATIONSHIP (Refer this https://msdn.microsoft.com/en-in/library/hh230952.aspx)
E.g.
1.CALCULATE(EXPRESSION,USERELATIONSHIP(‘calendar’[datekey],‘fact’[date1])
2.CALCULATE(EXPRESSION,USERELATIONSHIP(‘calendar’[datekey],‘fact’[date2])When you'll plot above formulae, they will get data as per relationship is being mentioned in formula dynamically.
Hope it helps.
Thanks