Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Cross Filtering calendar table

I have a calendar table that goes from 1901 up to 2020 and another tables have data from 2018 to 2020. When I add a slicer, it shows dates from the entire period in the calendar table. How do I displa...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    v-lili6-msft,

     

    Thanks for your comment. I found a much simpler solution. When you add a column to the values bucket, only the records with data in that column appears in the filter list.

     

    Thanks,

    Anton

v-lili6-msft's avatar
v-lili6-msft
Community Support
7 years ago

hi, Anonymous

It seems that your slicer is HierarchySlicer, so just do these as below:

Step1:

Add a Year Month Number column for both calendar table and data table

calendar table

Year Month Number = YEAR ( 'Date'[Date] ) * 100 + MONTH ( 'Date'[Date] )

data table

Year Month Number = YEAR ( Table1[Date] ) * 100 + MONTH ( Table1[Date] )

Step2:

Add a measure like below:

Measure = if(MAX('Date'[Year Month Number])>YEAR ( TODAY() ) * 100 + MONTH ( TODAY()) ||MAX('Date'[Year Month Number])<CALCULATE(MIN(Table1[Year Month Number]),ALL(Table1)),BLANK(),MAX('Date'[Date]))

Step3:

Drag the measure into Values field

 

Best Regards,

Lin

 

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

v-lili6-msft,

 

Thanks for your comment. I found a much simpler solution. When you add a column to the values bucket, only the records with data in that column appears in the filter list.

 

Thanks,

Anton