Why does (blank) show in related table categories?

[Anonymous]

I have a dashboard with a bunch of ways to visualize diversity data. I need every category to show on every chart, so I created tables and made the relationship (as in https://stackoverflow.com/questions/62189824/showing-items-with-no-data).

However, now (blank) is showing as a column on all of my bar/column and line charts. Every row has data for these fields; none are blank.

I could put a filter on the whole report to exclude (blank) but I'm worried about optimization. Is that my best option? Is there another way to not have (blank) show, like something in PowerQuery that will process faster or make this not happen?

Thanks much.

[Anonymous]

@TomMartens  and @smpa01 Thanks for the thorough answers.

The thing is, there are no nulls or blanks in the many/fact table. It is linked on a column created in Power Query that doesn't allow there to be blanks (and I've checked numerous different ways - no blanks). When I do COUNTROWS with no filters, there's no number in (blank). 

It's the phantom-blank row discussed here, but I don't know how to get rid of it.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[TomMartens · Accepted solution]

Hey @Anonymous ,

i created a little example.

The relationships:

The data:

This leads to (Blank) inside the slicer

The DAX to create the calculated column in the manyside table:

_check foreign key column value exists in the one side = 
IF(
    ISBLANK( RELATED( 'oneside'[key column] ) )
    , "does not exist"
    , "exists"
)

Hopefully, this helps to tackle your challenge.

Regards,

Tom

[smpa01]

The thing is, there are no nulls or blanks in the many/fact table.- are there any values in many table (Y) for which you don't have corresponding value in one sided table (X).

It is case of Y value supplied for no X

[Anonymous]

Nope. I literally copy-pasted from the column in the fact table into the dim table to make sure they were identical. 

Does it matter that the column in the dim table is created in PowerQuery? The original column that it is built off of has all kinds of blanks and weird entries and whatnot, which is why I cleaned it into a new column.

[smpa01]

never experienced anything as such and specially with Power Query which pays my bill😀

[smpa01]

@Anonymousthe blank appears beacuse on the right side of 1-many relationship has values that do not have corresponding value on the relationship column on the left side.

e.g. left table

//t1
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMlKKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}})
in
    #"Changed Type"

e.g. right table

//t2
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeJYHWSWERaWMYQVCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, val = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"val", Int64.Type}})
in
    #"Changed Type"

t1 to t2 relationship

blank appears

[TomMartens]

Hey @Anonymous ,

a blank shows up most of the time for this reason,

on the many side of a relationship a value is present in the column used to form the relationship where this value is not present in the column of the table on the one side of the relationship.

Then if you are using the column from the table of the one side of the relationship (adhering to the concept of star schema modeling this table is called dimension table) there will be (blank) item.
This indicates a data quality issue.

One of the most robust rules of star schema data modeling are these 2 rules:

Each value from the many side column has to be present in the dimension table.

There must not be NULL values on the many side nor on the one side.

This link provide a good start into data modeling with Power BI: Design a data model in Power BI  - Learn | Microsoft Docs

Hopefully, this helps to tackle the challenge you are facing.

Regards,
Tom