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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
THEG72
Helper V
Helper V

Blank Transaction Date shown in Date Dim table

Hi PBI Community,

 

I have a problem with my Date Dimension table showing transactions in my fact table.

 

When i run the transaction date from the fact table it shows the correct date as Sunday, 11th December 2016. However the Date Dimension Table is showing a blank row for this one transaction.

 

All other transactions are shown perfectly.

 

Does anyone have a similar experience? Could this be a bug in software? The data is definately in POWER BI but the date dimension table seems to be the source of the problem.

 

DATEDIM Issue.PNG

Can anyone suggest a fix ? Should i delete the DATE DIM and recreate it?

1 ACCEPTED SOLUTION

Hi there,

 

Your DATEDIM table is missing a record for the 11th of December 2016.  It looks to be an issue with the way you build it in M

 

You could try creating a DATE table in DAX using DATE = CALENDAREAUTO()

 

missting date.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

12 REPLIES 12
Phil_Seamark
Employee
Employee

What are the oldest and newest days in your DATE table?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi Phill, thanks for taking time to reply to my question. Here is data from Datedim table.

 

First Date is 1 Jan 2013

 

 

DATE DIM TABLE RECORD 1DATE DIM TABLE RECORD 1

 

Last Date is 1 Jan 2026

DATE DIM TABLE RECORD 4746DATE DIM TABLE RECORD 4746

And you have a relationship between the two tables?  Are each of the columns in the relationship set to use the DATE datatype?

 

If one side is Date and the other DateTime you can encounter similar issues to this.  Just checking more than anything.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

v-shex-msft
Community Support
Community Support

Hi @THEG72.

 

 >>Does anyone have a similar experience? Could this be a bug in software?

Yes, I faced this issue, but it is hard to reproduce the issue.

 

I'd like to suggest you to check the datasource mapping first, it may caused the issue.  If your data can be match well, the issue may related to power bi. Perhaps you can try to clean your cache file and recreate the report.

 

In addition, if above still not help, you can also submit a support ticket at below link.  (Right-Bottom -> "Create Support Ticket")

Support

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi Xiaoxin

I sent you a link to the PBIX file if you want to review it.

Hi @THEG72,

 

You link has broke so I can't download it, can you upload to 1 drive and share the link?

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Thanks for help but solved the issue, problem was my date table.

 

Cheers

 

Hi Xiaoxin, thanks for your time.

 

I tried to clear the cache from the options / Global / Data Load and then Clear Cache.

 

I then deleted report and closed and restarted software.

 

I still end up with same issue. I can see the data in the journalrecord table from the accounting system

JOURNAL RECORD TABLE shows resultJOURNAL RECORD TABLE shows result

I can also list the transaction, using the journal record transction date from same table

 

 

When i use the DATEDIM table shown below, i get the blank for that 1 transaction.

 

 

What do i need to check for the data source?

 

I guess the last option is to do a test and delete the Datetable and start again?

Any chance you can post your PBIX file (strip out all other tables) so we can have a play?

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Sent you an email with the link! Let me know if any issues accessing it

Hi there,

 

Your DATEDIM table is missing a record for the 11th of December 2016.  It looks to be an issue with the way you build it in M

 

You could try creating a DATE table in DAX using DATE = CALENDAREAUTO()

 

missting date.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Thanks for the help....! that solved it...

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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