relationship create a blank with no reason: data is consistent

[MarcoFogale]

I'm studying DAX and I created a simple file. I'm having trouble with a relationship that creates an unexpected extra blank, even though the data is super simple and consistent.

This is the dimension table:

The column on the fact table is this:

There are no errors, only 5 uniques values and no blank row in both tables. The two columns are about the same data type, without any aggregation set. Whole number, only 5 numbers. Easy-peasy.

But it's not...

I created a one-to-many relationship:

And the slicer on "ID Plats" created using the column on the dimension table shows a blank:

 

That is linked to... nothing on the other side:

 

Any idea? Why Power BI creates an extra blank in this case? What does it mean?

Of course, I could just hide the blank by filtering the visual, but I don't want to... I just would like my data to be consistent...

As it's just a simple file I created for studying purposes I can share it if anyone wants to give it a look...

Did anyone have a similar issue? How did you solve it? 

[MarcoFogale]

Ok... I finally figure out what happen to my slicer.... but I still don't know how to deal with this problem.

This is my whole model:

 With these relationships, everything works fine and all the slicers, taken from the dimensions tables show no blanks:

If I add the relationship with the Dates table (that I created following the Mastering DAX video course from SQLBI, and contains all the dates from January 1st to Dec 31th) I have blanks on the others tables because, obviously, not all day appears in the "Sales" table.

It's a relief, I don't have to start everything over. But... how do you deal with these blanks, created out of the date table? Is there a better practice to adopt to have a date table with all dates, in order to take advantage of time intelligence functions, avoiding at the same time these blanks on slicers that could lead to doubts about data consistency and reliability?

[v-kelly-msft · Accepted solution]

Hi  @MarcoFogale ,

Yes,when you create relationships,those only have values on one side,the other side will return blank.Just as below:

A workaround for you:

Select the visual>go to filters pane>choose "advanced filtering">"is not blank",then "apply":

And you will see:

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[negi007]

@MarcoFogale more oftern or not this is caused by data intergrity issues. There might be some data mismatch which causing blank values to appear in the slicer. It is possible that one side of your relationship table might be having null or blank values.

please read this article to have more clarity on this issue

https://www.encorebusiness.com/blog/blank-value-in-the-power-bi-slicer-and-filter/#:~:text=In%20actu....

in the meantime, if it possible for you to share your powerbi file so that we can do investigation at our end as well.  I recreated the similar data structure at my end as well and I did not face this blank value issue. 

[MarcoFogale]

Hello and thank you for your answer.

How can I share the file? I'm sorry I'm new to the Community and I haven't already figure out how to share files...

It's not a data issue. The strange thing is that if I start a new file, with the very same data source, it works perfectly. I create the relationship, I can even create a new one on another table with the same column from the dimension table, and it works.

But when I try to make it on the file I'm using, it simply can't.

I have tried to get rid of the ID and use the name instead. It works perfectly with the column name from my dimension table to another table. But when I try to link it to the "Sales", it creates a blank, exactly as you see in the pictures I posted before. So it has the same behavior using an ID column with the whole-number data type or a text column with the same data pattern. 

It really looks like a corrupted file. It's a little scary. I mean, this is only a training file and I don't care a lot about it, but if it happens to an important report and I have to restart it over, that can be a real issue....

Please let me know how I can share it with you.

Thank you a lot