Missing Data

[Godseyma]

 

I have uploaded the last 3 fiscal years worth of data and everything seems to checkout , however I am missing some info. I have checked my relationships between tables and everything seems to be working. How do I fix this issue? It obviously shows that there are values but would like to figure out where 

[TomMartens · Accepted solution]

Hey @Godseyma 

this often indicates that values from the column of the many side (the fact table) of a relationship are missing in the column on the one side of the relationship (the dimension table). Rephrasing this means, the dimension table products contains the values P1, P2, but the fact table has values for P1, P2, and P3.

Assuming I have a very simple model that looks like this:

As you can see, there is a fact row with p3, but p3 is not in the dimension table. Building a simple table visual yields the below result:

This issue can be tracked down by creating a calculated column in the fact table using the following DAX:

_check product key = 
IF( ISBLANK( RELATED( 'dim product'[productkey] ) ), "value is missing in product dimension" , "everything is fine" )

Now, using this calculated column in combination with the productkey column from the fact table helps to track the missing value:

The missing value will also appear if you use the column productcolor from the dimension table:

First check if all product keys are present in the dimension table, then you have to look for empty productcolors values in your dimension table.

If your semantic model is more complex, and there are relationships with the dimension table, as in fact --> product --> prouct subcategory  --> product category, the tracking will become more cumbersome 😉, but the approach will the same.

Hopefully, this will help to tackle your challenge.

Regards,

Tom

[danextian]

Hi @Godseyma ,

Compare the industry column from your dimension table vs the industry column in your fact table. A blank row means that there are more distinct values in your fact that in your dim. You can just put the two columns side by side in one table visual.

[Godseyma]

the only table that has industry in it is my industry table. My fact table does not have industry for this data. Any thoughts? Thank you for your response!

[danextian]

There should be some kind of a key that connects the two tables or the Industry column in the dimension table has blanks.  Alternatively, you can write a calculated column in  your fact table to check whether the current row has an equivalent in the industry table. Example:

= RELATED ( IndustryTable[IndustryColumn )

Check for the rows that return blank.