Forum Discussion
Table Relations and Blanks
SNik - As I recall from previous posts and greggyb might be able to help you because I have seen him post on this before, the blank values in slicers are caused when you have some relationships that don't match up correctly. For example, you have a Product specified as part of the Catalog that is not in your Products table. That's the likely cause of your blanks in your slicers, something along those lines. Power BI has a way of making problematic data obvious like that.
Greg_Deckler has got it. So, I just had another post in another thread with a very different topic that is nevertheless relevant to this discussion. That post where I describe the dictionary associated with each field in a table is where we can find the source of this phantom blank.
As Greg_Deckler said, when you have a row in the fact (many side of the 1:N) table that does not have an associated key in the dimension (one side of the 1:N), the storage engine will implicitly create a phantom-blank row. This phantom-blank becomes the lookup key for every row in the fact that doesn't have an appopriate lookup key in the dimension. This shows up in a few places. It becomes a display value, as you've seen in slicers. When you do a COUNTROWS( VALUES( 'Dimension'[Key] ) ), you'll see one more row than you expect. The row is the phantom-blank. You'll also see it in ALL(). This is because those functions reference not the values in the table rows, but in the column dictionary. DISTINCT() behaves identically to VALUES() in nearly every way, except it will omit the phantom-blank.
So how do we get rid of it? We can either add an explicit row to the dimension for all possible values in the fact table, or we can alter the fact table to exclude those rows, or transform the key field on all rows that don't have a match in the dimension to point to an alternate 'Unknown' key in the dimension.
Once you've altered the dimension or the fact or both, you should be able to refresh the entire model and the phantom-blank should be gone.
This behavior differs from a foreign key in SQL or a relationship in Multidimensional, which both require that everything in the fact table have a matching row in the dimension table. Tabular is more permissive and lets you do things like this, and implicitly puts the pieces in place to support it.
SNik - As I recall from previous posts and greggyb might be able to help you because I have seen him post on this before, the blank values in slicers are caused when you have some relationships that don't match up correctly. For example, you have a Product specified as part of the Catalog that is not in your Products table. That's the likely cause of your blanks in your slicers, something along those lines. Power BI has a way of making problematic data obvious like that.
Greg_Deckler has got it. So, I just had another post in another thread with a very different topic that is nevertheless relevant to this discussion. That post where I describe the dictionary associated with each field in a table is where we can find the source of this phantom blank.
As Greg_Deckler said, when you have a row in the fact (many side of the 1:N) table that does not have an associated key in the dimension (one side of the 1:N), the storage engine will implicitly create a phantom-blank row. This phantom-blank becomes the lookup key for every row in the fact that doesn't have an appopriate lookup key in the dimension. This shows up in a few places. It becomes a display value, as you've seen in slicers. When you do a COUNTROWS( VALUES( 'Dimension'[Key] ) ), you'll see one more row than you expect. The row is the phantom-blank. You'll also see it in ALL(). This is because those functions reference not the values in the table rows, but in the column dictionary. DISTINCT() behaves identically to VALUES() in nearly every way, except it will omit the phantom-blank.
So how do we get rid of it? We can either add an explicit row to the dimension for all possible values in the fact table, or we can alter the fact table to exclude those rows, or transform the key field on all rows that don't have a match in the dimension to point to an alternate 'Unknown' key in the dimension.
Once you've altered the dimension or the fact or both, you should be able to refresh the entire model and the phantom-blank should be gone.
This behavior differs from a foreign key in SQL or a relationship in Multidimensional, which both require that everything in the fact table have a matching row in the dimension table. Tabular is more permissive and lets you do things like this, and implicitly puts the pieces in place to support it.
Thank you all for the feed back, one other thing that I found that might help other people is that
1- I copied and past the data from PowerBI to a Note pad and i see blanks on the left side, this was an display issue by PowerBi anyways, i trimmed it off
2- some case you see that the data looks the same from both tables, for example "ABC XYZ" is a name of a product, the issues was that between ABC and XYZ i was seen a space but in one table was a space and another was a Tab
thank you again
Hi gerggyb
you mentioned that ......
or transform the key field on all rows that don't have a match in the dimension to point to an alternate 'Unknown' key in the dimension.
....... how would you do thatÉ do you have a sample? assuming that we dont have SQL server . and the DIM and the FACT are provided in a text file, my point is that you must do all the maneuvering in PowerBI only.
Thanks
SNik, you can create a surrogate key and do all sorts of joins and transformations in Power Query. One way would be to merge the dimension with the fact in Power Query, and then create a new dimension key that is some default value for those rows which did not have an associated key in the dimension.
' and then create a new dimension key that is some default value for those rows which did not have an associated key in the dimension.'
How do I create a row the returns a default value for every value that does not match a key?
