Filter DIM table to records between DIM date slicer

[BC_TM]

I have a DIM details table that contains records with a start and end date column. I want to limit the records of this DIM details table to those that were "active" within the date period specified by my DIM date slicer. Logically, this is where the start date is <= the MAX of my DIM date slicer and the end date is >= the MIN of my DIM date slicer. To my knowledge it's not possible to create two-column relationships to achieve my desired output, so I've attempted to create a calculated table with the following:

DIM details filtered = CALCULATETABLE(
    'DIM details',
    'DIM details'[START_DATE] <= MAX('DIM date'[DATE]),
    'DIM details'[END_DATE] >= MIN('DIM date'[DATE])
)

However, when adjusting my date slicer, the Calculated Table is not filtered. Therefore the above approach does not work. The same is true when substituting the filter portion of the CALCULATETABLE function:

DIM details filtered = CALCULATETABLE(
    'DIM details',
    FILTER(
        'DIM details',
        'DIM details'[START_DATE] <= MAX('DIM date'[DATE]) &&
        'DIM details'[END_DATE] >= MIN('DIM date'[DATE])
    )
)

What's the right way to handle this? 

[v-rongtiep-msft · Accepted solution]

Hi  @marcelsmaglhaes ,

Thanks for your sharing.

Hi  @BC_TM ,

Calculated tables are computed during the data refresh process and are static; they do not dynamically update in response to changes in slicer values in your report.

Instead of a calculated table, create a measure that counts or filters records based on the slicer selection. For example:

Active Records = 
CALCULATE(
    COUNTROWS('DIM details'),
    FILTER(
        'DIM details',
        'DIM details'[START_DATE] <= MAX('DIM date'[DATE]) &&
        'DIM details'[END_DATE] >= MIN('DIM date'[DATE])
    )
)

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[BC_TM]

Hey @marcelsmaglhaes 

Thanks for your input but this doesn't quite work. Your DAX omitting the CALCULATETABLE function does not successfully limit the initial dataset to values per the FILTER expression (ie start date before the max of the slicer, end date after the min of the slicer).

Additionally I don't think this USERELATIONSHIP function works as you specified. For starters I can't simply use a relationship because I don't want only records where the start/end date were within the slicer, or else this produces records that started and ended within the date period, not that they were active. And USERELATIONSHIP will not accept an inactive relationship as a parameter - it will throw the following error:

USERELATIONSHIP function can only use the two columns references participating in relationship.

[marcelsmaglhaes]

Hey @BC_TM ,

The issue you're encountering is because the CALCULATETABLE function does not filter the table in the way you expect. Instead, it applies filters to the table, but does not remove rows from the table.

To achieve your desired outcome, you can use the FILTER function within the CALCULATETABLE function to create a new table with only the rows that meet your criteria. Here's how you can adjust your DAX expression:

DIM details filtered = FILTER( 'DIM details', 'DIM details'[START_DATE] <= MAX('DIM date'[DATE]) && 'DIM details'[END_DATE] >= MIN('DIM date'[DATE]) )

You will probably need to create a relationship between your new calculated table and your dim_data if you have one.

However, if I understood your needs correctly, when someone filters a range of dates, you want to present in the visual the data that is between your start date and end date. If you want to achieve that situation, you need to create a valid relationship between start_date and your dim date, and a disabled relationship between end_date and your dim date. After that, you can create a measure using the USERELATIONSHIP function.

For example: measure = CALCULATE (COUNTROWS ( YourTables ), USERELATIONSHIP(DimDate[Date], DIM details[EndDate])