Is Inactive Relationship automatic in Power BI Desktop?

[mgirvin]

Dear Team,

I am trying to use SAMEPERIODLASTYEAR in CALCULATE. My formula did not work because I cannot have a relationship between the Data Table and the Fact Table. So I was intending to use an inactive relationship and then activate it with the USERELATIONSHIP function, but when I checked the formula it was working without having to use the USERELATIONSHIP function in CALCULATE. Has Power BI Desktop updated to let functions automatically use an inactive relationship, if that is the only relationship from the Date Table to the Fact table?

To repeat: My CALCULATE SAMEPERIODLASTYEAR Measure works when I create the inactive relationship and I DON"T use the USERELATIONSHIP function. Then when I delete the inactive relationship, the Measure does NOT work.

Any ideas?

Sincerely, Mike Girvin

 

[Ozeroth]

Hi Mike,

No there hasn't been a change in how inactive relationships are handled.
An inactive relationship is the same as no relationship, unless activated with USERELATIONSHIP or CROSSFILTER.
Could you post either a link to a shareable version of the model, or a list of tables/columns:relationships and the DAX for the measure in question?

Regards,
Owen

 

[mgirvin]

Thank you very much, Ozeroth, for your reply : )

Here is an example of what appears to be the inactive relationship working DAX Measure without USERELATIONSHIP:

https://people.highline.edu/mgirvin/YouTubeExcelIsFun/SmallRentalSetFormForumQuestions.pbix

Look on the sheet "USERELATIONSHIP Issue"

P.S.

If that link does not work from this Mr Excel Site (when I clicked it, it zipped it), just use this web page:

https://people.highline.edu/mgirvin/ExcelIsFun.htm

It is the very last file at the bottom of the page.

 

[mgirvin]

If that link does not work from this Mr Excel Site (when I clicked it, it zipped it), just use this web page:

https://people.highline.edu/mgirvin/ExcelIsFun.htm

It is the very last file at the bottom of the page.

 

[Ozeroth]

Hi again Mike,

This is an interesting one It has to do with the special treatment of Date tables in Power BI.

Here is the explanation:

1. When a data model is set up in a "normal" fashion with a Date table (in your case dDate), and another table is related to the Date column of that table (in your case dDate[Date]) with dDate[Date] on the 1-side of the relationship, then dDate[Date] is designated a primary key.
2. It turns out that even an inactive relationship turns dDate[Date] into a primary key! Sorry, didn't realise this.
3. The special consequence of the column dDate[Date] being a primary key is that if CALCULATE is called with a filter argument containing the column dDate[Date] (such as SAMEPERIODLASTYEAR ( dDate[Date] ) or any of the time intelligence functions that return columns of dates), then all filters on the dDate table are automatically cleared. This is the same as ALL( dDate ) being provided as an additional filter argument. This is a convenient feature to get time intelligence functions to work regardless of which columns of your Date table happen to be filtered.
   See this article for a fuller explanation: https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/
4. So your measure:
   

   # Rented Last Year = CALCULATE ( [# Rented 01], SAMEPERIODLASTYEAR ( dDate[Date] ) )

   is actually translated to:
   

   # Rented Last Year = CALCULATE ( [# Rented 01], SAMEPERIODLASTYEAR ( dDate[Date] ), ALL ( dDate ) )

5. If you remove the inactive relationship, dDate[Date] is no longer treated as a primary key, and your measure [# Rented Last Year] won't work correctly if filters are applied on columns of the dDate table other than filters on dDate[Date], since the automatic ALL ( dDate ) is no longer applied.
6. One way to get your measure to work regardless is to rewrite it as:
   

   # Rented Last Year = CALCULATE ( [# Rented 01], SAMEPERIODLASTYEAR ( dDate[Date] ), ALL ( dDate ) )

7. By the way, USERELATIONSHIP is not required at all for either [# Rented 01] or [# Rented Last Year] since the DAX formula for [# Rented 01] itself simulates a relationship with dDate. The SAMEPERIODLASTYEAR function shifts the Date filter context a year earlier, and the 'simulated' relationship with dDate still functions correctly in that 'shifted' filter context.

Regards,
Owen

 

[mgirvin]

Thank you very much Owen!!

 

[akice]

I also didn't know about the "special" treatment of inactive relationship with respect to date tables. Good to know.

Being able to use a non date-typed column as the key from dDate on one side of the relationship is the reason why Excel has the "Mark as Date Table" feature. Behind the scenes It automatically adds the ALL ( dDate ) for you. AFAIK, Power BI desktop does not have this feature because...well, I have no idea. I asked Marco Russo and he said he didn't know either. But the SQLBI article listed above also goes into how to implement a workaround in the data model so you don't have to remember to append ALL ( dDate ) everywhere.