Do inactive relationships affect DAX?

[swwong1]

Hi All

I have created a 10-day rolling revenue DAX measure and I have a query about the blank row on the matrix. I have asked the online instructor from my course and they have replied "Power BI doesn't recognize the first date as a termination point and therefore continue the calculation one more day. "

However, when I remove the inactive relationship (between date table and the shipped date), the blank row is gone. So, it seems like the inactive relationship is causing the issue as the ship date does have dates before 1/1/2015 while all transactions are on 1/1/2015 or after.

My question is, am I correct to say the inactive relationship does affect the DAX results?

Many thanks!!

https://www.dropbox.com/s/p43xpwn7hmax2ec/Inactive%20Relationship.pbix?dl=0 

[swwong1 · Accepted solution]

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

[v-yingjl]

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.

[swwong1]

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

[AllisonKennedy]

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. 

https://sites.google.com/site/allisonkennedycv

[Greg_Deckler]

@swwong1 - That is really, really interesting. Before taking a look at your PBIX file I would have said no, that they do not. But, I can't explain this behavior otherwise. It's actually really odd. You might want to post this as an issue.

https://aka.ms/PBI_Comm_Issues

[amitchandak]

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.