Date Table and Fact Table one-to-many relationship displaying blanks despite same data type

[MorePowerBI]

Hi all,

I have _DateTable with the date column [Date Table] that's connected one-to-many with date columns from two other tables.

When I plot data using APP_Z_RECEIVING and _DateTable everything works as expected. However when I plot data from APP_Z_RI_TURNAROUND and _DateTable, I get blanks.

The data type is date for all three tables so I know this isn't the issue. Help is greatly appreciated!

UPDATE 6/1/2021:

Per @KNP 's investigation

There's something not right with the TXN_IN_DATE type. If you convert that date to epoch, like so...

Epoch = DATEDIFF(DATE(1970,1,1), APPS_Z_RI_TURNAROUND_TIME_REPORT_STG_GG[TXN_IN_DATE], SECOND)

it gives accuracy that includes hours, minutes seconds.

If you add the same 'Epoch' column to the date table, it does not.

Epoch = DATEDIFF(DATE(1970,1,1), _DateTable[Date Table], SECOND)

Date table for 1/24/2020 = 1579824000

Your fact table 1/24/2020 = 1579874507

Still looking to get some assistance with this. Is there a way I can align the epoch times?

[KNP · Accepted solution]

I would start by going back to Power Query and look at this transform...

  #"Changed Type" = Table.TransformColumnTypes(APPS_Z_RI_TURNAROUND_TIME_REPORT_STG_GG1,{{"TXN_IN_DATE", type date}}),

Maybe try adding a new column, date only from TXN_IN_DATE.

and then see if that column behaves as expected.

[MorePowerBI]

Appreciate the response!

I previously tried transforming the TXN_IN_DATE column to date only but the extract date step would not complete/render so I gave up on that avenue; didn't occur to add a new date only column based on the TXN_IN_DATE column.

Doing this and then creating a relationship between the date table and this new dat column solved the issue.

[KNP]

@MorePowerBI - it appears your date table doesn't go back far enough. Starts at 19/01/2021, it needs to go back further. 

[MorePowerBI]

No, my date table starts 500 days ago.

[KNP]

Sorry, I meant to say 2020. (edit: I'm only seeing 1/19/2020)

[MorePowerBI]

that's not the visual i'm having issues with.

I'm having issues with this table:

[KNP]

There's something not right with the TXN_IN_DATE type. If you convert that date to epoch, like so...

Epoch = DATEDIFF(DATE(1970,1,1), APPS_Z_RI_TURNAROUND_TIME_REPORT_STG_GG[TXN_IN_DATE], SECOND)

it gives accuracy that includes hours, minutes seconds.

If you add the same 'Epoch' column to the date table, it does not.

Epoch = DATEDIFF(DATE(1970,1,1), _DateTable[Date Table], SECOND)

Date table for 1/24/2020 = 1579824000

Your fact table 1/24/2020 = 1579874507

I hope this helps you to debug further.

[KNP]

I see what you mean now. That is weird. I even created a new date table and joined to TXN_IN_DATE but it suffers from the same issue. 

I'll let you know if I think of something but I currently have no idea as the types and join looks sound.

[Icey]

Hi @MorePowerBI ,

I can't reproduce your issue. Please re-check if the relationships are created correctly.

In addition, it will be better if you can share us a sample file with the same issue. Please remove sensitive information and real data.

Reference: How to provide sample data in the Power BI Forum - Microsoft Power BI Community

Best Regards,

Icey

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

[MorePowerBI]

Bumping this up for visibility

[amitchandak]

@MorePowerBI , This means dates are missing in date table.

or your date in fact have timestamp and you need to create a date without the time and join that

Date = [datetime].date
or
Date = date(year([datetime]),month([datetime]),day([datetime]))

[MorePowerBI]

no that didn't solve it. I don't believe I have a timestamp in the APPS_Z_RI_TURNAROUND table. Under query editor it shows as data type date and doesn't have any time stamps. I actually change the data type in a step.

I know the data type in the date table is also correct:

I'm also not missing any dates as you can tell from the second image in my original post.

[Ashish_Mathur]

Hi,

Share the download link of your PBI file.