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
That is interesting. A couple of things I noticed in your model:
A) Stock date is text data type - should be date to match with the Date table via inactive relationship
B) Date table does not contain all the dates for Stock date column, so this is why the 'blank' is coming through
I'm still not sure why it's doing so with an inactive relationship, call it a feature? As Greg_Deckler suggested, contact support. When I try the same visual using CALENDARAUTO in your sample file (so date table contains all dates in stock date and order date), the DAX behaves as expected with or without the inactive relationship.
Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos.
I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.