Slice with Calendar added column

[Anonymous]

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

[v-juanli-msft]

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]

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]

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

[Anonymous]

Hi Maggie.

This is a bug and I'm pretty sure of it. I tested with other colleagues as well.

There are several DIM tables. When a filter for a particular one is added, the dates stop showing on filters. They only show when they have measures/columns to go with it.

I think this should be marked as a bug.

Thanks.

[Anonymous]

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