Instead of clumsy bookmarks using drop down menu to switch dimension, created in PQ

[Applicable88]

Hello,

I once seen a report where someone uses a dropdown menu to switch the dimension between date, calendar week, month and year view of visuals. I find its a much more elegant way to do it, compare using bookmarks to switch the many overlapping visuals. 

I don't know if the preparation work needs to be done in PowerQuery first 

For example I have a date table and another table is joined in the datamodel how many orders were coming in per day. Then these can be displayed in a bar chart and I use the drop down of the dimension to switch between date, calendar week and month etc. 

The standard structure would be having a table1 dimension table and a table2 Facttable, which are connected in the datamodel later on :

Date	Calendarweek	Month
01.01.2021	53	Dec
02.01.2021	53	Dec
03.01.2021	53	Dec
04.01.2021	1	Jan
05.01.2021	2	Jan
06.01.2021	3	Jan
07.01.2021	4	Jan
08.01.2021	5	Jan
09.01.2021	6	Jan
10.01.2021	7	Jan
11.01.2021	8	Jan

Date		Ordersamount
01.01.2021		100
02.01.2021		288
03.01.2021		333
04.01.2021		555
05.01.2021		987
06.01.2021		888
07.01.2021		123
08.01.2021		444
09.01.2021		555
10.01.2021		345
11.01.2021		432

Thank you very much in advance.

Best.

[KNP · Accepted solution]

Hi @Applicable88,

I think this (youtube.com/watch?v=hilfglpKNRQ&ab_channel=BIElite) will give you what you're looking for.

And you're right, this is a much more elegant solution than bookmarks.

Kim

[Applicable88]

Hello @KNP thank you so much. That is the closes to what I'm looking for. The only problem with that method and is also displayed in the video, e.g. there seems to be a problem with calendar weeks. And another problem are the displays of the dimension, for example for dates you see correctly in date format, but when setting on "monthly" every bar in the chart will display the first of the month, if you do "yearly" you get the first of the year. Any workaround for that? 

Best. 

[KNP]

I don't have much time at the moment, so I'll post this link in the meantime in case it helps.

https://www.youtube.com/watch?v=lFd2hjkKz_Q&ab_channel=BIElite 

Not sure how easy it will be to incorporate both solutions.

If that doesn't solve the issue, if you have the time to throw together a sample PBIX I should be able to take a look at it later.