Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
SNik
Helper I
Helper I

Table Relations and Blanks

Hi

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                       

Sincerely
Nik- Shahriar Nikkhah
2 ACCEPTED SOLUTIONS
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

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

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

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

Sincerely
Nik- Shahriar Nikkhah
greggyb
Resident Rockstar
Resident Rockstar

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

 

 

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

 

Sincerely
Nik- Shahriar Nikkhah

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.