How does Null Handling work in USERELATIONSHIP and TREATAS

[gitgithan]

I want to ask about null handling demonstrated in this lab: https://microsoftlearning.github.io/mslearn-fabric/Instructions/Labs/15-work-with-model-relationship...
If there is a better place to ask, such as forums specially for labs, please direct me there.  
If you never done the lab and can't explain null handling, I hope you could do it too and maybe we have the same doubts, add better questions and discussion.
Please explain what's really going on during relationship evaluation when data contains nulls.  I believe the key to the answer lies in knowing join semantics differences between regular relationship and virtual relationships, then further understanding how Null interacts with that.

To summarize the lab, it shows how to handle role-playing dimensions.
Screenshot of data model shows the final state. 
Date is the role playing dimension, with [DateKey] being active with Sales[OrderDateKey]  initially, then later being active with Sales[ShipDateKey] through 2 methods.
- Clicking on UI ("Modify relationship active status" section)
- Using dax ("Use inactive relationships" section, also the 1st dax pasted below)

Finally, it teaches another method, instead of messing with active/inactive and pointing Date[DateKey] to Sales[OrderDateKey] or Sales[ShipDateKey], it creates a new "Ship Date" table that points to Sales[ShipDateKey] under "Add another date table" section.

Key points are that orders may not be shipped, so Sales[OrderDateKey] do not have nulls, but Sales[ShipDateKey] has nulls, causing differences in output visualization (4 row vs 3 row) which is crux of my question.

Though datamodel screenshot contains 1-1, all relationships relevant to the lab are 1-many from Date dimensions to Sales fact.
All tables are import storage mode from db restored from https://github.com/MicrosoftLearning/DP-500-Azure-Data-Analyst/tree/main/Allfiles/00-Setup/DatabaseB... into sql server.

Relevant Dim tables are Date and Ship Date (Reference query from Date, which i understand is a simple COPY PASTE).
- Columns involved with Date are DateKey
- Columns involved with Ship Date are ShipDateKey
Relevant Fact table is Sales
- Columns involved are OrderDateKey, ShipDateKey (the 2 keys being role-played with Date dim)
    

 

I don't understand how the 2 ways of defining measures leads to one table that includes 4 rows (3 years + 1 blank) and the other table having 3 rows (3 years with no blank).

1st measure (Under "Use Inactive Relationships" section): 

 Sales Shipped =
 CALCULATE (
 SUM ( 'Sales'[Sales Amount] ),
 USERELATIONSHIP ( 'Date'[DateKey], 'Sales'[ShipDateKey] )
 )

Screenshot from the section on relationship (un)checking on UI method, which i assume is exactly the same as USERELATIONSHIP method.

2nd measure (Under "Explore other relationship functions" section)

 Sales Shipped =
 CALCULATE (
 SUM ( 'Sales'[Sales Amount] ),
 CROSSFILTER ( 'Date'[DateKey], 'Sales'[OrderDateKey], NONE ),
 TREATAS (
 VALUES ( 'Date'[DateKey] ),
 'Ship Date'[ShipDateKey]
     )
 )

Screenshot from under "Explore other relationship functions".

Both screenshots contain explanations of blanks which i don't understand because i don't get the underlying mechanism. 
I have read the docs of TREATAS, USERELATIONSHIP, and understand concepts of filter propagation from one-to-many, active/inactive.
https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand talks about Relationship evaluation with LEFT OUTER JOIN semantics from many to one side.
However the animation shown causing nulls in Category, Product dimensions due to new unseen value in Sales fact, is different from the case in this lab where the null was in Fact, and there from the getgo.

Please point me to any resources (articles, books, videos, people) that explain null handling.

I will break down my questions into 3 parts. 
1. Questions on 1st dax and it's 4 row output
2. Questions on 2nd dax and it's 3 row output
3. Questions on output inconsistency between the 2 dax.

1. Do i understand right that the relationship evaluation occurs as many (Sales)-to-one (Date), with the joining key of the LEFT(many) table Sales[ShipDateKey] containing 4 unique values {NULL,2020,2021,2022}, and the joining key of the Right (one) table Date[Datekey] containing 3 unique values {2020,2021,2022}. Even if filter is done on any of the 3 Date[Datekey] values, it does not block the NULL row from showing up because LEFT join semantics means you don't filter out any unique values in joining key of Left side?

2.  CROSSFILTER shuts down the original link and TREATAS activates the new link. Is TREATAS a regular relationship (so LEFT JOIN) or limited relationship (so INNER join) or neither?
What's the join semantics when relationship is virtual? (INNER,LEFT,CARTESIAN?)
And why is the explanation in result screenshot even talking about Sales[OrderDateKey] not containing nulls?
Isn't that irrelevant (deactivated by CROSSFILTER) since we have already changed role from Sales[OrderDateKey] to Sales[ShipDateKey] with TREATAS?

The role has changed through a chain of 2 steps 
Date'[DateKey] --(TREATAS )--> 'Ship Date'[ShipDateKey] --(set up during Ship Date table creation earlier in lab)--> Sales[ShipDateKey] , so how does values in Sales[OrderDateKey] feature in the filtering/relationship evaluation process at all?

3. What confused me is how 2 very similar paths lead to different results.
The 1st dax connected Date to Sales. The 2nd dax connected Ship Date (basically a copy of Date) to sales.
After "Visualize ship date data" section and before "Explore other relationship functions" section, lab also showed how Ship Date creates the same 4 row output with Date, which further convinces me Ship Date is just a copy of Date. Thus the key is understanding what TREATAS is doing to relationship evaluation.

Please help, i either learn nothing from the lab (current state), or learn a ton.

[gitgithan]

Thanks Gao, 
The 1st link was really helpful. That explains the 4 row output. I also found another explanation of the null row in https://support.microsoft.com/en-us/office/context-in-dax-formulas-2728fae0-8309-45b6-9d32-1d600440a... under "Referential Integrity and Power Pivot Relationships" section

The 2nd link did not explain the 3 row output, and the other questions in my 1st post, so that's still a mystery

[v-cgao-msft]

Hi @gitgithan ,

Your understanding of the relationship evaluation as many (Sales) to one (Date) is correct. The function activates an inactive relationship for the calculation context. When nulls are present and you're summing based on them, the DAX engine creates a separate empty row to aggregate these values for those records that cannot be sorted by the sort field. Most of the time, we need to plan wisely during the modeling phase to avoid this.
Understanding blank row and limited relationships - SQLBI

Looking at the second formula, it deactivates/removes the relationship before 'Date'[DateKey], 'Sales'[OrderDateKey] from the calculation and then uses TREATAS to pass the filter on 'Date'[DateKey] to 'Ship Date'[ShipDateKey].

It's like this:

As for why the empty rows disappeared, my guess is that at this point none of the keys in the fact table are empty and can be found in the dim table.
Propagating filters using TREATAS in DAX - SQLBI

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group