Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Slice with Calendar added column

Hi all.

I have a model with a Calendar table, and I use it to filter, slice, and context.

I can use slicers with the automatically added Date Hierarchy, (Year, Quarter, Month, Day), and it works good (slices all the visuals on the report, for example). But when I add a different Calendar column to a slicer (for example a week number column I added on the Calendar table), the slicer returns all blank and I can't slice the report by week. Also, other added columns on the Calendar table don't work (like Month Name, etc.), only the Date Hierarchy works for slicing.

Any ideas?

Thanks in advance, cheers!

EDIT:

May I add that, for example, the week column shows on a matrix, as long as I have some measures from the fact table(s).

Need Help

5 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Anonymous 

    Do you mean this problem?

    Because Date slicer is from 2019/1/1-2019/1/19, week slicer is from week 45 -57

    but weeknum for 2019/1/1-2019/1/19 is from 1-3,

    the table with with "Date" (not hierarchy) should be impacted by two slicers together.

     

    You could edit interactions for slicers, 

    Learn how to "use Visual interactions to change how visualizations on a report page impact each other"

     

    As for "the week column shows on a matrix", it should be possible, if you have any question, please give an example data or picture.

     

    Best Regards

    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      v-juanli-msft 

      Hi and thanks for the help.

      Not really though. I recreated what you are showing on a simple sample model with 2 small tables (fact and calendar) and it works, I can slice with every calendar table column:

       

       

       

       

       

       

       

       

      However, in my model, which is unfortunately a bit complex, I can't. I have removed all other date filters from the page and still, if I create a slicer with any calendar column except the automatically created hierarchy date, I can't slice.

       

       

       

      This particular page uses a fact table that has one active relationship to the calendar table and 2 other inactive ones for specific measures.

      Any idea on why this is occuring?

      Thanks again.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      I tried something with no success as well:

      I removed the "auto date time" feature from the file options to get rid of the automatically created hierarchies.

      Now I can not slice with any of the calendar columns. At all. Even for the "page level filters"... It just doesn't work.

      I've never seen anything like this, I hope the file or model are not broken.

      Cheers.

      • v-juanli-msft's avatar
        v-juanli-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        Hi Anonymous 

        What relationships between fact table and calendar table?

        Please click on the visual "date range from fact table", then select from mune "format"->"edit interactions",

        you will see how week slicer from calendar table interact with the visual "date range from fact table".

        please share the screenshots above here if possible.

         

        Best Regards

        Maggie