cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
swwong1
Helper II
Helper II

Do inactive relationships affect DAX?

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 

 

1 ACCEPTED SOLUTION
swwong1
Helper II
Helper II

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

View solution in original post

6 REPLIES 6
swwong1
Helper II
Helper II

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

View solution in original post

v-yingjl
Community Support
Community Support

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

AllisonKennedy
Super User III
Super User III

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



?? Check out my March Madness Report??


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. 


www.excelwithallison.com

Greg_Deckler
Super User IV
Super User IV

@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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




amitchandak
Super User IV
Super User IV

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

Appreciate your Kudos.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.