Forum Discussion
Do inactive relationships affect DAX?
Hi All
Was reading about inactive relationships and they mentioned something about table expansion where a left outer join is used from the many-to-one side of the tables; including inactive relationships. If the "many" to the "one" side doesn't exist, a blank virtual row is added to the "one" side table.
Based on the above, as my "many" side (stock date) to the "one" side doesn't exist, a blank row was created in the "one side" which probably explains why there is a blank row before 1/1/2015. I guess it isn't really due to DAX but this behavior was shown when using one of the DAX formulas.
Link is below (3/4 of the page on the above point):
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand
Hi swwong1 ,
When using time-intelligence-functions like datesinperiod(), it needs a whole date dimension table to detect the context .
In your data dimension table, it seems only have order dates not included all dates like stockDate. I think there is no relationship between inactive realationship and dax expression. With your date table, those measure cannot detect the right context of the table so that you will get some blank rows. When using calendarauto() as AllisonKennedy mentioned to create a date dimension table, it seems work fine.
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Thanks AllisonKennedy & Greg_Deckler
Hi v-yingjl
What I am confused about is if I remove the inactive relationship (dotted line) under the model tab, the blank row in the matrix disappears; therefore this means the inactive relationship is causing the blank row???
I would expect the empty row if I have an active relationship between stockdate and date table as stockdate have dates before 1/1/2015 while my calendar table only starts from 1/1/2015.
Thanks