Filtering on dates - low cardinality fact table, large time dimension table?

[j567boston]

I have a fact table with thousands of records in it, but only 10 distinct dates in it - Represented by YYYYMMDD Integers.

I have a time dimension table with YYYYMMDD integers as keys, and the full date / time / week / holiday / moon phase / whatever in the other fields.  This runs from year 1900 to 2100.  No problem, just join the tables.

I want to show regular nicely formatted dates - " Aug 12, 2017" etc in a filter.

My problem is the filter is showing 1900 to 2100 by default.  I just want to see the dates that exist in the fact table.

I look at the relationships in the direct query star schema (in SQL Server) and don't see any options for "inner join" or "left inner join".

What am I missing?  This seems like it should be obvious.

Thanks!

[Ashish_Mathur · Accepted solution]

Hi,

In a calculated column of your FACT TABLE, write the following formula

=DATE(LEFT(Data[Date],4),MID(Data[Date],5,2),RIGHT(Data[Date],2))

Write a similar function in the Time Dimension table as well.  Now connect both date columns.  In the slicer, drag the date column from the Time Dimension table.

[v-shex-msft]

HI @j567boston,

Current power bi date analysis feature seems not properly works on live mode source, it is pretreatment feature and if you modify the source date, the original hierarchy will be broken and it need to re-analysis the date hierarchy.

Reference:

Date hiearchy does not work automatically in case of Direct Query

Regards,

Xiaoxin Sheng