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

    When you drag date field and other fields into a visual, if the field is aggregated, it will show data only the date that has data.

and if it is not aggregated, you can add a measure like 

Measure = CALCULATE(MAX(Table3[Material]))

then drag it into visual level filter and set filter blank

Result:

I have a calendar table that goes from 2015 up to 2018

and another table has data from 2016 to 2018

Before

It has all the date

After 

 

Best Regards,

Lin

 

  • 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.

    • Aruljoy's avatar
      Aruljoy
      Helper II
      5 years ago

      Hi Afernado,

       

      Can you please provide me the sample PBIX file which you have in the image. I need to genrate simillar report? 

       

      Thanks,

      Arul Arockiam