Unexpected relationship behavior using DirectQuery

[Stefan_38]

Hi PBI community, 

I am experiencing an unexpected issue with a dimension table that comes from a DirectQuery (PowerBI model dataset) I use a data set to further build an adjusted data set from the same sources. The main model includes all dimensions and actual sales, while the new model contains forecast numbers. The tables are 1:1 in the design and use the same dimensions and in general, the performance is as expected until I noticed this issue. 

The product table coming from the dataset doesn't include the values from the transaction that doesn't have a product key, but it is still counted in the total. This is different compared to how it acts if I create the same table in the local model as linked, as we have two types of blanks where all the numbers sum up to the total.  See the picture below. both relationships are one-to-many.

 

So I do wonder if this is intentional or if there is something i do not consider?  I would have expected the behavior to be identical but I guess dealing with blanks might be difficult with data coming from DirectQuery. Any suggestions on how to solve this or if this is just how it works for now. 

BR

[Anonymous · Accepted solution]

Hi @Stefan_38 ,

--When two tables are linked through a regular relationship, the table on the one-side might contain the additional blank row in case the relationship is invalid. Thus, if the many-side of a regular relationship contains values that are not present in the table on the one-side, then a blank row is appended to the one-side table. The additional blank row is not added to the target of a limited relationship.

Please Read this blog for more details:
Regular and limited relationships in Power BI

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