Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jmartin0807
Frequent Visitor

Data missing when connecting to published data set

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!

1 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.

View solution in original post

4 REPLIES 4
v-lionel-msft
Community Support
Community Support

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.

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
Community Support
Community Support

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).

v-lionel-msft_0-1620110854893.png

 

 

Sumanth_23
Memorable Member
Memorable Member

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? 

Sumanth_23_0-1619731035500.png

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!  

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!

Proud to be a Super User!



Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.