How to make it so that my dimension date table only shows relevant dates?

[daircom]

Hi all,

At our organization we use a centralized date table which is stored in database. This is very convenient as all our power bi reports use the same date table. 

I want to use the dim calendar table as a slicer to filter the visuals in my report. However, it shows all avaialble dates in the dim calendar table. So it shows a lot of dates which are not in my fact tables (latest date in my fact table is 2028). I looked online it and shows that I have to filter the slicer using DAX etc but shouldn't there be a more straightforward way? 

[Anonymous]

Hello,rohit1991 ,danextian and Bibiano_Geraldo ,thanks for your concern about this issue.

Your answers are excellent!
And I would like to share some additional solutions below.
Hi,@daircom .I am glad to help you.

Below is my test.
Use the dax code to create a date column that meets the conditions.

If you need your date column to contain all the dates in your data table and the dates in your calendar table to be consecutive
You can use the calendar function directly (using the Today function in the function ensures that the date is updated to the latest date of the day).

calendar1 = CALENDAR(MIN('Fact'[Date]),MAX('Fact'[Date]))


calendar1 = CALENDAR(MIN('Fact'[Date]),TODAY())

If you want your date table to contain only date values that exist in the data table
You can use DAX to create a new calendar table directly and create the relationship

calendar2 = 
DISTINCT(
    SELECTCOLUMNS(
        'Fact',
        "Date", 'Fact'[Date]
    )
)

This is my test data:

Generally for the date table is generally handled in the following ways (this is also suggested by other users, I have summarized their suggestions)

1. Create a relationship: By establishing a relationship between the calendar table used by the slicer and the date columns of the original data, you can directly utilize the relationship for screening. This method is simple and intuitive, and is suitable for situations where the data model is relatively simple.
2. Create a calculated column: Use DAX to create a calculated column to mark whether the date exists in the fact table (states =ture() ), and then use this calculated column in the filter. This approach is more flexible and is suitable for situations where the filter criteria need to be updated dynamically.
3. Use DAX or M code to create a new calendar table:
Use DAX or M code to filter the target date according to user requirements, and create a new calendar table. This method is suitable for the need to customize the requirements to ensure that the dates in the calendar table fully meet the business needs.
In general, the use of the dax function is one of the most widely used methods, it is the most flexible, because the user can modify the code according to the actual needs of the code.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian

[Bibiano_Geraldo]

Hi @daircom ,

To achieve your goal, create a calculated column to the dim calendar table using DAX:

IsDateInFactTable = 
IF(
    COUNTROWS(
        FILTER(
            'FactTable', 
            'FactTable'[Date] = 'DimCalendar'[Date]
        )
    ) > 0, 
    TRUE, 
    FALSE
)

1. Go to the Filters pane in Power BI.
2. Drag the IsDateInFactTable column from the dim calendar table into the Filters on this page or Filters on all pages section, depending on your requirement.
3. Set the filter condition to IsDateInFactTable = TRUE.

After applying the filter, you can safely use the dim calendar table's Date field in a slicer. The slicer will now only show dates that exist in the fact table.

[danextian]

HI @daircom 

I am assuming that you DimDate table is connected via a single direction relationship to your Fact table. You can add a calculated column that picks up the max date in your fact table and compare it agains the dates in DimDate.

DateFilter =
VAR MaxFactDate = MAX ( FactTable[Date] ) RETURN DimDate[Date] <= MaxFactDate

This will return TRUE/FALSE which you can use as a report level filter. This should limit the visibility of the dates up to the max in your fact table.

[daircom]

Hi @rohit1991 ,

Yes but is this really the "best practice" way to do it? 

[rohit1991]

Hi, hope this helps :),
To filter your slicer to show only dates with data in your fact tables:

1. Use a Visual-Level Filter:

   • Select the slicer.
   • In the "Filters on this visual" pane, drag the fact table's date field.
   • Set it to is not blank.

2. Relative Date Filter (if applicable):

   • Use the slicer's drop-down and apply "Relative Date Filtering" (e.g., Last 5 Years).

Both options avoid writing DAX and keep it simple.