It’s important to understand the different behavior of relationship between current Power BI desktop Engine and previous.
I have a table FactInternetSales_LT like this one with a column SalesAmount that contains the amount sold and then ProductKey that contain a primary key of the product. Show in the image below
and i have a Product_LT table with a column Product Key that contains a primary key of the product and the Name that contains the name of the product. Show in the image below
Beetween the two table there is a relationship
if you look the FactInternetSales_LT table there are many sales that have a productkey that correspond at a product in the product_LT table but there are a productKey 10000 that not exist in the product_LT table.
The previous version of analysis services multidimensional would complain about that the data is wrong and will never be able to process 🙂
The current version of analysis services tabular in Power BI is better and show some number anyway but if i put the Sales amount, product key and name in a grid in Power BI the total is wrong. Show the image below
For solve this problem the engine does one trick witch is important to understand that is: “if the relationship is broken” because the value isn’t in Product_LT it add a new row to the related table that contains blank for all column and now it is possible create the relationship with the wrong row (with the productkey = 10000) and the blank one.
In this way if count row of Product_LT table with COUNTROWS the result is 13 and not the number aspected 12 because the engine add this new blank row
We pay attention because if you use the COUNTROWS get a correct result of the actual products if I use VALUES will get another . If you do not know this mechanism is likely to make a mistake in the count in our formulas 🙂
download the NewBehavio.pbix