Data missing when connecting to published data set

[jmartin0807]

Hi everyone, I have a problem that I cannot seem to figure out.

I have created a dataset in a PBIX file where I have a date table and a fact table. Like any good date table, there is a date for every day from 1900 to 2099 and every date on the fact table ties to a date on my date table.

After I connected the date table to my fact table, I hid the date column on the fact table. I tested and confirmed that slicing a measure by calendar_year resulted in no blank values for calendar_year. I then uploaded the dataset to the cloud service, created a blank report, and connected the new file to the published dataset.

I noticed that something was off when my totals didn't match the test that I performed in the PBIX file with the dataset. I performed the same test as before and there was now a blank row (some of the rows in the fact table did not have a relationship with the date table). I noticed in my date table, each year only has dates from January to June, everything else was missing. I didn't filter or change the date table at all.

I went back to the original model and unhid the date column in the fact table, republished the dataset, and checked my numbers and everything was working fine.

Do you know why this happened? I would like to have the date column in the fact table hidden, but I am not sure how to get around this. Thanks!

[v-lionel-msft]

Hi @jmartin0807 ,

Has your problem been solved?

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jmartin0807 · Accepted solution]

Yes it has! So sorry for not checking this sooner. I'm not sure why this fixed the issue but it didn't have anything to do with hiding the column. Once I changed when I had the data refreshed, it solved the issue. Thank you everyone for your support.

[v-lionel-msft]

Hi @jmartin0807 ,

" I noticed in my date table, each year only has dates from January to June, everything else was missing. I didn't filter or change the date table at all."

The calendar table in your data model only has data from January to June? Or does the date field in the table visual only have data from January to June?
If it is the former, please check Power Query and data source, if it is the latter, it may be that the field in the fact table affects the date field (Show item with no data).

[Sumanth_23]

hi @jmartin0807 - would you be able to share a pbix file with some sample data?

Also when you say you hid the Date column in the FACT table - are you referring to the column level hide as shown below? 

I have not experienced such an issue with missing data when you hide the relationship column - I have done it multiple models and it works perfectly fine. 

Hope that helps!