Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.

Don't join your Date Dimension on Date Field.

By Farhan Ahmed posted Mar 11, 2019 05:56 AM

Most of us usually create joins on Date Values when we create internal date dimensions on PBI. Below is the case when actually it doesn't work properly.

Let me brief a bit about data sources.

1- PostgreSQL
3- Date dimension created internally using Calendar function. 
4- all the joins are on date column and they are working perfectly on PBI Desktop even after refreshing the data from PBI Desktop.

When I published that on Server(PBI Report Server) report was running fine unless after Schedule refresh for Power BI Report. What actually was happening is that

- Schedule was running on time
- Schedule completed successfully
- As soon as it completes all/most of the visuals showing no/nothing data.

It appears very odd to see schedule completed and but some visuals data are gone (Not gone in actual)

For resolving this issue I dropped data from every table to each individual table/matrix visual and data seems to be coming for each table after Schedule Refresh data on Report Server.

But then I realized that the Format of Date is changing for PostgreSQL when data refreshed on Report Server and Join is getting failed causing no data in visuals using Calendar Data.

After that I had to Create a DateKey on both Tables in order to join.

Then everything works fine.

Lesson Learned.

Always create DateKEY (Integer Value) in order to join Date table with transaction table.