topic Re: Table Relations and Blanks in Developer

Table Relations and Blanks

SNik — Thu, 18 Feb 2016 15:37:28 GMT

I have 3 tables that have a 1 to many relation with each other

Product-----> catalog -----> 2015Data (fact)

the relations are sets with no errore

when for the first 2 tables i am using a Slicer and the Fact table a Table

in the Product Slicer i see a Blank and the list of my Products, if i select Blank the data in the Catalog table gets wiped out but the data in the FACT remains without been filtered

How can i fix this issue so that i can start slicing the data from Product table then CalatogTable and seeing the final filtered in the 2015Data fact table.

one thing that i see is in the FACT table i have the names of the Catalog and some are in french like

Catalog table ------> 2015Data(Fact)

ACE AÇÈ

and the relation doesnèt give me an error

whats the best approche

Re: Table Relations and Blanks

Greg_Deckler — Thu, 18 Feb 2016 16:24:54 GMT

@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.

Re: Table Relations and Blanks

greggyb — Thu, 18 Feb 2016 17:19:07 GMT

@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.

Re: Table Relations and Blanks

SNik — Fri, 19 Feb 2016 02:19:35 GMT

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

Re: Table Relations and Blanks

SNik — Fri, 19 Feb 2016 02:23:11 GMT

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

Re: Table Relations and Blanks

greggyb — Sat, 20 Feb 2016 03:39:57 GMT

@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.

Re: Table Relations and Blanks

gellismyers — Tue, 16 Apr 2019 05:27:00 GMT

' 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?