Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Slicer vs Table Behaviour - different availability of dates

Hi everyone,
 
I am experiencing behaviours in Power BI Desktop I'm struggling to understand. In Power BI, two visuals have 'access' to different dates, and I'm unsure why.
 
The data I'm working with has a FACT table displaying status changes of projects over time, connected to a DIM_Date table, which is connected to a DIM_Academic_Year table: FACT - DIM_Date - DIM_Academic_Year. See the model below.
 
In the below Power BI screenshot, the date range slicer is showing dates up to and including 05/07/2022. This is the Week_begin_date of the latest date in my DIM_Date table. My table visual however is only showing up to and including the week of 13/12/2021, and skipping dates until the 27/09/2021, etc. There is a measure in the report counting how many projects have a status change on a given week. There are status changes on the dates displayed in the table, meaning the table is displaying only those dates, whereas the slicer is showing all dates. This is the behaviour I don't understand. Both visuals below only have fields from DIM_Date (this is a page for testing, not for showing to users), and there are no other filters on the report or page in Power BI.
 
The date slicer seems to be able to 'access' the full range of dates from DIM_Date, whereas the table is being limited by a measure that isn't present on this page or in the table visual.

 

This is my model in Visual Studio (analysis services tabular project):

There is SQL in each table's query editor. DIM_Academic_Year is being limited by Academic_Year_Current_Year_Desc 0 to -10, so dim_date and the FACT are being limited to datas in the last ten academic years. Each table is LEFT JOINED onto the table to its left.

 

I think I understand that by keeping the cross-filtering on as bi-directional in my model, and having LEFT JOINS across the tables, DIM_Date is being filtered by the FACT to only display dates from the FACT table, but what I don't understand is why that isn't the case for the date range slicer. Why does the date range slicer have 'access' to all dates, but the table doesn't? Is this hidden behaviour of the native slicer visual I'm unaware of?

 

Many thanks for any help offered.

Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    Hi Anonymous ,

     

    Be aware that the slicer altough is a visualization has any other works in a different way than a chart or a table, certain filterings cannot be applied to the slicer and also depending on the type of data you place on the slicer you can have different results.

     

    In the case of date be aware, that Power BI, can be using the auto-date time feature meaning that all date columns will be "changed to a calendar"  meaning showing all dates from the frist on your model to the last one.

     

    Can you check if on your file you have the auto date-time turn on?

     

     

3 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    5 years ago

    Hi Anonymous ,

     

    Believe your issue is related with the fact that you are using bidirectionality on both relationship you have on your model so the information get's filtered out in both ways and you may filter out information by mistake.

     

    Try to make the relationship filter to single side and check if everything works properly.

     

    If this does not help can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

    If the information is sensitive please share it trough private message.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks for the reply MFelix ,

       

      If that was true (and changing the filter direction to one-way does solve a different issue I had in the report), it'd be true for both the slicer and the table surely? As it is, the bi-directional relationship seems to be affecting the table but not the slicer, or the slicer would only show up to 13/12/21 as well. My confusion is about why the slicer isn't behaving the same as the table, as if the slicer can ignore the relationship in the model and call on the full range of dates. If I change the slicer visual to a table or card, it then only shows dates up to the 13/12/21. Changing it back to a slicer adds in more dates. Is as if there's hidden behaviour in the slicer.

       

      Any ideas?

       

      I'll try mock up and upload a model all the same.

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        5 years ago

        Hi Anonymous ,

         

        Be aware that the slicer altough is a visualization has any other works in a different way than a chart or a table, certain filterings cannot be applied to the slicer and also depending on the type of data you place on the slicer you can have different results.

         

        In the case of date be aware, that Power BI, can be using the auto-date time feature meaning that all date columns will be "changed to a calendar"  meaning showing all dates from the frist on your model to the last one.

         

        Can you check if on your file you have the auto date-time turn on?