Slicer with timestamps in 2 seperate tables

[greyniall]

Hi all,

For some reason I can't find out why my simple charts are not working. The only way the slicer works is if I use it to select all blank values. I created an simplistic example, as follows:

I created two tables: Table1 and Table2

Next I create a date dimension table (DimTable) as follows (all the way to 31/12/2022)

Next I create the required relationship:

Finally I create the two graphs and a slicer based on DimTable[Date]. So far so good. 

Now if I use the slicer to select data, I either get all the data or none. If I use a different type of slicer, I see that there is a blank field which is where all the data is in. 

Can anyone point me in the right direction? 

Thanks!

[Shaurya]

Hi @greyniall,

I created 3 tables similar to yours and I am able to select part of the data using date from the dimension table as slicer.

Example:

The most probable reason why this is not happening in your report is because you are using bidirectional relationships which is not recommended while joining facts and dimensions.

Try this:

Mark this post as a solution if that works for you!

[greyniall]

Thanks for your quick reply Shaurya! 

I tried changing the dimensions to one-way and it doesn't seem to help. I must be making a very stupid mistake somewhere along the way. Could you share your Pbx with me please? 

[Shaurya]

Hi @greyniall,

Hope you can download it from this link:

https://drive.google.com/file/d/1QNPmjk5yKrJyHdjwHCK8Jh5RrsQ03Pef/view?usp=sharing

Works for you? Mark this post as a solution if it does!

[greyniall]

Thanks again Shaurya! 

The difference is: my data tables have times included whereas yours only have dates with 00:00:00 as time. That's why they match. It seems I may have to seperate the time and date to be able to filter them. Except, I want to have the time included on the xaxis in case one zooms in a lot. 

[Shaurya · Accepted solution]

Hi @greyniall,

I guess you should do it. I really don't see the relevance of using a timestamp as a slicer. While you may have values for the same date, it is highly unlikely that you'll get matching values for the same timestamp. Also, how would you have every second of the day in your Date Dimension? That would 86,400 rows for one day. Imagine a dimension for 4-5 years🙂.

Did I answer your question? Mark this post as a solution if I did!

[greyniall]

Thanks again. There are other ways I have found. Basicely you create a date and time dimension table seperately and link them. And then e.g. use two sliders. But for now, converting timestamps to just dates works OK I guess. Cheers!