Forum Discussion
Helper ISlicing a table by Month and Year From Same Date Table
Hi,
I have a date table and another table. Both have YEAR,MONTH columns. But because there is only 1 active connection i can only slice YEAR or MONTH.I don't want to multiply the date table to use 2 different slicers. I want to be able to slice them separately with 2 slicers by only 1 date table. How can i achieve this?
Thanks,
see attached
14 Replies
- lbendlin
Super User
Add a derived date to your fact table (for example first day of the month) and link that to your date table.
dexter00000Hi i'm not sure how will that work. I tried first day of the month to first day of the month way,i tried Date Hierarchy to first day of the month. But none of them work. Am i supposed to have only 1 connection? YEAR and MONTH will be 2 different slicers that are in the same table.
- lbendlin
If you want these slicers to work independently then you need separate year and month dimension tables that control the Dates table. If they should interact, use the year and month columns from the Dates table. Do not base the slicer on the fact table .
- dexter00000
so this is how it looks with additional columns.
I only have 1 dates table and no other dimension tables year will slice the fact table and month will too they can be used together. But also separately too. Is this achievable?
- lbendlin
You don't have a Dates table yet. The Date column is missing. For Time Intelligence functions to work that date column must be contiguous (no gaps) and covering (including the result of the time intelligence).
Please show a sanitized version of your data model.
- dexter00000
I didn't include them in the picture but it's like this.