Data model question

Quick question about data model and slicer behavior.

I have a table that is a list of Serial numbers. I have a table that is a list of invoices that contain a serial number as well. The relationship is a one to many that I have set up. But I am trying to understand one thing. In the Equipment list of serial numbers if I build a slicer, all serial numbers are displayed. In the invoice table there are a few invoices that have no serial number (bad data). I noticed that a value of “blank” shows up in the Equipment list slicer. When I break the relationship the “blank” value goes away. Also when I select the “blank” value in the Equipment list slicer visual the invoice visual displays all the invoices and not the one with the blank serial number. Not clear on why this happens. Any help would be appreciated. Attached is pbix file.test.pbix (1.2 MB)

@lomorris,

Take a look at this thread - I think it will solve your problem:

Blank value in slicer and total

  • Brian
1 Like

@lomorris In case of missing relationship from the many side a invisible blank row is added to the One/Dimension side so that any row that doesn’t matches from many side with the one side is aggregated under blank(), now there are few functions that makes use of that blank row, such as ALL () and VALUES ()

Here is the query generated by that slicer:

DEFINE
  VAR __DS0Core = 
    VALUES('Customer Equipment'[Serial No])

  VAR __DS0PrimaryWindowed = 
    TOPN(101, __DS0Core, 'Customer Equipment'[Serial No], 1)

EVALUATE
  __DS0PrimaryWindowed

ORDER BY
  'Customer Equipment'[Serial No]

Result:

If we could change VALUES to DISTINCT, then the problem of blank row would go away, but unfortunately we can’t:

When you break the relationship, engine doesn’t add any invisible blank row to the one side, therefore you don’t see a blank value:

There are 105 Distinct Values in your Cost table (fact) and 41 Distinct Values in your Customer Equipment (Dimension), for those 64 values there is blank row added in your Customer Equipment table.


test (2).pbix (1.2 MB)

Solution - Update your dimension table to include missing values.

2 Likes

Thanks, I was able to quickly determine that I had bad data in in this case transactions with missing serial numbers. I can updated the table to correct the values, but ultimately I will have to go back to the data source to get those records updated.

Thanks again.

Brian,
Thanks for the link to the thread that had the Guy in a cube video. Yup, bad data for sure.