How to avoid blank codes/values being removed

[Anonymous]

Hi all,

I hope everyone is well!

I am creating a new Dashboard but I'm having some issues I'm hoping someone might have an answer to 🙂

I have two main tables, a PostedSalesShipmentHeader and a ShipToAddress one. Both tables include a 'Ship-to Code', but when creating a relationship between them I'm getting a Many-to Many message as the ShipToAddress table does not contain a blank value in the code column, whereas the SalesShipmentHeader table does. Here's some screenshots:

So I'm finding that, when adding values to a table visual, the blank codes will stay there up until the point I bring in a value from the ShipToAddress table:

I want to keep the blank values in the Ship_to_Code column visible in the table, so if the Code field is blank then the Address column doesn't populate with anything. Is there a way for me to do this?

I hope this makes sense! Thanks so much for any help in advance 🙂 

[vojtechsima · Accepted solution]

@Anonymous  hello,

the way power bi "hide" the row is that there is blank value for the measure you use. If you wanna brute force it, you can set your field by right clicking and click "Show data with no values" (or something like that.

Alternatively, you can modify measure that would calculate something for the blank values as well. (be careful with these, this can go quickly sidewise, as it will assign value to everything in your table).

[Angith_Nair]

Hi @Anonymous 

Use the below dax:

DisplayedAddress = 
IF(
    ISBLANK(SELECTEDVALUE(PostedSalesShipmentHeader[Ship-to Code])),
    BLANK(),
    MAX(ShipToAddress[Address]) 
)