Forum Discussion

daxesh's avatar
daxesh
Frequent Visitor
10 years ago

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 un...
Need Help
Tips and Tricks
vinaypugalia's avatar
vinaypugalia
Icon for Resolver I rankResolver I
10 years ago

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's avatar
vinaypugalia
Icon for Resolver I rankResolver I
9 years ago

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