New behavior in Power BI Desktop

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
TableSales

 

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

TableProduct

 

Beetween the two table there is a relationship

Relation

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

Error

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

blank

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 🙂

Result

download the NewBehavio.pbix

 

Comments are closed.

Switch to our mobile site