Filter content of date dimension

[MiKeZZa]

Hi all,

I have 2 fact tables and 1 date dimension. The date dimension has data from 1-1-1900 untill 31-12-2100.

Fact table 1 has data from 1 year ago untill now and fact table 2 has data from 14 months ago untill 6 weeks in the past (by example). The content of the fact table will be different tomorrow, or next week.

All tables have a column named DATE_ID

I now want to filter the content of the date dimension by the content of the fact tables, so that a data slicer that I'll add to the report is showing only the correct data.

(How) is this possible?

Ps; I don't want to do DAX-tricks on measures, but I want to populate the content of the date table.

[Greg_Deckler]

Can you supply some sample data and output? Having trouble following from your description. I'm wondering if you should append both your fact tables together. Also, check on the cross-filtering of your relationship and in this case, I think you want to have a bi-directional cross-filter.

[MiKeZZa]

Date dimension

date_id date            year month day
1           1900-01-01 1900 1           1

......

100000 2099-21-31 2099 12         31

Fact 1

date_id     cust_id   count

1234          2345       10

54321        12           100

........

Fact 2

date_id     prod_id   sum

12              23           5

4524          120          25

Both fact_tables are now joined to date dimension by date_id.

[v-ljerr-msft]

Hi @MiKeZZa,

Do you want to use cust_id column and prod_id column as Slicers to get the corresponding date from Date dimension? Could you be more precisely with your expected output with some sample data?

Regards

[MiKeZZa]

Hi @v-ljerr-msft,

No I don't want that. I want áll the data of the facttables and based on the dates of the facts I want to make the content of the date dim 'smaller'.

When you look al the facttables with the 2 record that I have as an example I want the date dim to start at 12 (lowest date_id) and go to 54321 (highest date_id). Cust_ID and Prod_ID don't do anything at all in this case.

This all is to make thet timeline slicer (https://app.powerbi.com/visuals/show/Timeline1447991079100?WT.mc_id=Blog_Visuals) to be filled with dates that have facts related with it.

[v-ljerr-msft]

Hi @MiKeZZa,

In your scenario, I would suggest you use CALENDARAUTO Function (DAX) to create the Date Dim table, so that the range of dates in Date Dim table is calculated automatically based on data in the model(both "Fact 1" and "Fact 2" table). The formula below is for your reference.

Date Dim = CALENDARAUTO ()

Regards

[MiKeZZa]

I'll try this tomorrow! Looks good.

But is it possible to join this one to our regular date dimension? I want to use things like (local) holidays and so in, that are specially prepared in our own date dimension....

[v-ljerr-msft · Accepted solution]

Hi @MiKeZZa,

---

But is it possible to join this one to our regular date dimension? I want to use things like (local) holidays and so in, that are specially prepared in our own date dimension.... 

In this scenario, you can try generating the new date dim table from your regular date dim table and also be calculated automatically based on data in "Fact 1" and "Fact 2" table. The formula below is for your reference.

New Date Dim =
VAR minDate =
    IF (
        MIN ( 'Fact 1'[date_id] ) <= MIN ( 'Fact 1'[date_id] ),
        MIN ( 'Fact 1'[date_id] ),
        MIN ( 'Fact 2'[date_id] )
    )
VAR maxDate =
    IF (
        MAX ( 'Fact 1'[date_id] ) >= MAX ( 'Fact 1'[date_id] ),
        MAX ( 'Fact 1'[date_id] ),
        MAX ( 'Fact 2'[date_id] )
    )
RETURN
    FILTER (
        'Date dimension',
        'Date dimension'[date_id] >= minDate
            && 'Date dimension'[date_id] <= maxDate
    )

Note: Just relate your fact tables with the new created date dim table instead of your regular date dim table.

Regards

[MiKeZZa]

This is exactly what I'm looking for! This works very nice.