Unable to create a working slicer due to date hierarchy.

[SK__]

I have Two Tables each having a date column which is in long date format DD:MM:YY HH:MM:SS 

Before relationship between the two, both are having date hierarchies. 

Now, post creating date hierarchy, one of the tables is losing it's date hierarchies. 

When I insert a slicer with the date column. Then one of the visuals (who has the date hierarchy present) is responding properly to the slicer. But the other one (whose date hierarchy went missing) is going blank. 

I also tried to create a new table with just the dates and then made relationship with other two. But in that both the tables end up losing the date hierarchy and then not respond to the date slicer.

Is there any solution through which I can create a relationship between the tables and both the visuals will react to the same date slicer. 

[SK__]

I created this table and created a relationship with date of the other two tables. 

These are the formats of the dates.

Relationship is active.

Till the time I create a slicer everything is visible. But once I create a slicer for dates 

like this and move the slicer. The visual stops responding and goes blank. 

I have even tried with the option 2 of auto data/time.

[Anonymous · Accepted solution]

Hi @SK__ 

After testing, because your date contains time, so you need to extract the date first.

e.g Sample data 

1.Create a new column in the table

Column = 'Table'[Column1].[Date]

 2.then create the calendar table and create the relationship between the column created above the the date column calendar table.

Table 2 = CALENDAR(DATE(2023,11,1),TODAY())

3.Then put the date column of calendar table to the slicer, then it can work.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SK__]

Thank you. It worked. 

[Anonymous]

Hi @SK__ 

The problem you mentionded that create date hierarchy, one of the tables is losing it's date hierarchies when thay have relationships, the structure is set up in such a way that when two tables have a relationship, only one table can have a hierarchy.

And after testing, when you have relationship among tables, it can work. Please refer to the following steps to check if your creation is right.

1.It is better that create a calendar table, then create a one-many relationhip between tables.

Sample table.

a.Create a calendar table

b.Create relationship between tables.

c.Put the date column of calendar table  to the slicer. and put the date field to a table visual for test. it should work.

Output

2. Or you can consider not to use the date hierarchies. Close it at the option setting.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.