Year Filter should show only Years with available Data

[lkshck]

Hi,

I'm currently having a Date table which contains data from 2010 to 2050. On another table I'm having cost data which is available from 2016-2021.

I want to place a filter at a page which can filter by year, but there is now a selection from 2010 to 2050 as the Date table has all those years available. Is there an option to only show years in this filter where I have Cost data available (2016-2021)?

best regards,

Lukas

[PaulDBrown · Accepted solution]

If the tables have a relationship, you can use the following to filter the values in a slicer:

Filter slicer = COUNTROWS (RELATEDTABLE (Cost table))

Add this measure to the filters on the slicer in the filter pane and set the value to greater or equal to 1

[lkshck]

Maybe another question to this one. This works fine on Pages which only contains data from the Date table and this one Cost table.

If I'm having a page which contains data from the Date table, CostTable1 and CostTable2 and both are having a relationship to the Date table. Is there any way to filter the slicer to contain the min and max Date of both Cost tables?

[PaulDBrown]

Maybe:

Filter Dates =
VAR C1 =
    VALUES ( 'Cost1'[Date] )
VAR C2 =
    VALUES ( 'Cost2'[Date] )
VAR _Dates =
    DISTINCT ( UNION ( C1, C2 ) )
RETURN
    COUNTROWS ( INTERSECT ( VALUES ( 'Date Table'[Date] ), _Dates ) )

And add the measure to the filter pane for the date table setting the value to 1

[lkshck]

This worked fine, thanks a lot!

[amitchandak]

@lkshck , You need to have a common date/year table. join to both tables and use that to filter both the tables

Bridge Table: https://www.youtube.com/watch?v=Bkf35Roman8&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=19

[lkshck]

@amitchandak thanks for your feedback. Currently I'm using my Date table as a central Date table which has relationships to about 10 other tables. Do I have to create a bridge table for every of those?