Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Applicable88
Impactful Individual
Impactful Individual

Bookmarks, always select todays week in slicer

Hello,

we all know the bookmarks function can become tricky very often. 

 

I have a report where one bookmark is set the page filter filter "todays" numbers.

In case the user want to see the history data I have another button where it make one date slicer appear and reset the "todays panel filter to last 3 month. 

"Data" in both bookmarks is selected. 

In the slicer options I forced the "only select one" option. 

What I want is that if the user clicking on "History", that the slicer always selected the newest date by default. 

But unfortunately it will only select the date which the bookmark is last updated with. 

Is there a workaround for this? 

Best. 

1 ACCEPTED SOLUTION
speedramps
Super User
Super User

This is a classic mistake we all make when we first start with Power BI!

We create a report on 21/11/2021 with a dropdown set to 21/11/2021 and expect it to move the next day. It won’t!

The workaround is to create a pick list with "Current day", “Current week", “Previous week" etc then save those as book marks.

Then in your Power Query refresh the pick list with the up-to-date values.

Then use relationships to convert "Current day or “Current week" to dd/mm/yyyy keys on your fact tables.

 

There are some videos on Youtube how to do this.

 

Step-by-step instructions:-

 

In Power Query create a Calendar table and exact copy Hidden calendar.

 

Then in Power BI click New Table and ceate a Period Pick List with Dax (see below example).

 

Then create a Period Pick List (1:M) Calendar relationship with Cross Filetr direction = both

 

Then create a Calendar  (1:M) Fact tables Filter direction = single

 

Then add the Period Pick List value to a dropdown or filter box.

 

Save your book marks with the dropdown or filter box set to "Current day".

 

ADDCOLUMNS(

CALCULATETABLE(

VALUES('Hidden calendar'[Date])

),

"Period", "All dates"

)

 

,ADDCOLUMNS(

CALCULATETABLE(

VALUES('Hidden calendar'[Date]),

'Hidden calendar'[DayOffset]=0

),

"Period", "Current day"

)

 

,ADDCOLUMNS(

CALCULATETABLE(

VALUES('Hidden calendar'[Date]),

'Hidden calendar'[WeekOffset]=0

),

"Period", "All current week"

)

 

,ADDCOLUMNS(

CALCULATETABLE(

VALUES('Hidden calendar'[Date]),

'Hidden calendar'[WeekOffset]=-1

),

"Period", "Previous week"

)

 

etc, etc, etc

If you want the options to display is nice equence then consider adding a sort key to the Period Slicer

 

 

Capture.JPGCapture1.JPG

View solution in original post

2 REPLIES 2
speedramps
Super User
Super User

This is a classic mistake we all make when we first start with Power BI!

We create a report on 21/11/2021 with a dropdown set to 21/11/2021 and expect it to move the next day. It won’t!

The workaround is to create a pick list with "Current day", “Current week", “Previous week" etc then save those as book marks.

Then in your Power Query refresh the pick list with the up-to-date values.

Then use relationships to convert "Current day or “Current week" to dd/mm/yyyy keys on your fact tables.

 

There are some videos on Youtube how to do this.

 

Step-by-step instructions:-

 

In Power Query create a Calendar table and exact copy Hidden calendar.

 

Then in Power BI click New Table and ceate a Period Pick List with Dax (see below example).

 

Then create a Period Pick List (1:M) Calendar relationship with Cross Filetr direction = both

 

Then create a Calendar  (1:M) Fact tables Filter direction = single

 

Then add the Period Pick List value to a dropdown or filter box.

 

Save your book marks with the dropdown or filter box set to "Current day".

 

ADDCOLUMNS(

CALCULATETABLE(

VALUES('Hidden calendar'[Date])

),

"Period", "All dates"

)

 

,ADDCOLUMNS(

CALCULATETABLE(

VALUES('Hidden calendar'[Date]),

'Hidden calendar'[DayOffset]=0

),

"Period", "Current day"

)

 

,ADDCOLUMNS(

CALCULATETABLE(

VALUES('Hidden calendar'[Date]),

'Hidden calendar'[WeekOffset]=0

),

"Period", "All current week"

)

 

,ADDCOLUMNS(

CALCULATETABLE(

VALUES('Hidden calendar'[Date]),

'Hidden calendar'[WeekOffset]=-1

),

"Period", "Previous week"

)

 

etc, etc, etc

If you want the options to display is nice equence then consider adding a sort key to the Period Slicer

 

 

Capture.JPGCapture1.JPG

Hi @speedramps ,

nice ! You put it into "ready" selectable categories.

In the meantime I found something very simple fix point where my report can switch to by default.

 

Of course a mastercalendar is needed, then I make an additional calculated column in that calendar like:

Today Column = if ( Mastercalendar [Date] = Today(), then "Today",  Mastercalendar[Date]) 

That is my new dimension column for that slicer. The good thing is...I can select "Today" as my new preselected Date. And it will always start on that if I had save the report so. 

 

Thank you very much, of the ideas to make other categories. They come up much more handy if I need preselected timeframes without the need to click so many filters.

Best regards.

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.