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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi v-lili6-msft,

 

Thanks for your comment. Attached is an image of my report. My matrix table is already showing only the dates with data. And I have multiple aggregated columns in it. 

What I'm trying to achieve is to show only dates with data in my filter visualization which is on the left. Hope this makes sense. 

Please let me know if you need any further details.

 

 

Thanks, Anton

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

hi, Anonymous

     After my test, you can do these as below:

Step1:

Use RELATED Function to add a column in date table that Which dates have data.

has data = RELATED(Table1[Date])

Step2:

Drag the new column has data into page level filter and filter blank value

Result:

 

Best Regards,

Lin

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi v-lili6-msft,

     

    Thanks for your comment. My Table1 (according to your model) has future dates. It's indeed a sales table and it has budget records that go till the end of the current fiscal year but I want to have dates only till the current date. And PowerBI does not let me do any visual level filters.
    So still my filter visual is showing dates future dates. Any suggestions?

     

    Thanks in advance.

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

      HI, Anonymous

      You can add a column to judge whether the date is before current date in date table

      before current = IF('Date'[Date]<=TODAY(),"Y","N")

      then drag it into page level filter to filter "Y"

       

      Best Regards,

      Lin

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi v-lili6-msft,

         

        Thanks for the swift reply. I just want to filter the filter visualization, not the entire page because it has calculation till the end of month or fiscal year. 

        PowerBI doesn't allow adding any column to the visual level filters.

        Hope this makes sense.

         

        Thanks,

        Anton