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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Diego-mx
Advocate I
Advocate I

Switch between weekly and monthly Views

I have a Calendar table, which has at least three columns:  a_date, week_date, month_date; the last two corresponding to the beginning of the corresponding week and month.  

 

So far, I've showed my reports grouping by month_date.  And then I duplicate the page, to show them via week_date.  


However, I have two main type of reports:  KPIs and Historical.  Thus duplicating starts multiplying the effort of modification.  It would be better if I could just add a slider that would choose what to group by.  

 

In any case, I add one indicator table with "week" and "month" values to choose from. 

 

Solution 1. 

Ideally I could link the slider to the corresponding column and then whenever I choose one all the rest groups accordingly. 

Solution 2. 
I create a secondary grouping calendar table with "long" format (as opposed to wide format), with columns "b_date" and "period" that looks like this: 

b_date      period 
7/1/2018   "month"
8/1/2018   "month"
9/1/2018   "month" 
... 
7/1/2018   "week"
7/8/2018   "week"
7/15/2018  "week" 
....



and this becomes easier to link but then I don't know how to create this table as a union of two tables with DAX syntax. 

If anyone can help me with any of the solutions I'll appreciate it.  

Thanks. 

 

 

1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
Employee

Hi @Diego-mx,

 



Solution 2. 
I create a secondary grouping calendar table with "long" format (as opposed to wide format), with columns "b_date" and "period" that looks like this: 

b_date      period 
7/1/2018   "month"
8/1/2018   "month"
9/1/2018   "month" 
... 
7/1/2018   "week"
7/8/2018   "week"
7/15/2018  "week" 
....

and this becomes easier to link but then I don't know how to create this table as a union of two tables with DAX syntax. 


You can create a calculated table as below:

CalendarTable2 =
UNION (
    SELECTCOLUMNS (
        CalendarTable1,
        "b_date", CalendarTable1[week_date],
        "period", "Week"
    ),
    SELECTCOLUMNS (
        CalendarTable1,
        "b_date", CalendarTable1[month_date],
        "period", "Month"
    )
)

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-yulgu-msft
Employee
Employee

Hi @Diego-mx,

 



Solution 2. 
I create a secondary grouping calendar table with "long" format (as opposed to wide format), with columns "b_date" and "period" that looks like this: 

b_date      period 
7/1/2018   "month"
8/1/2018   "month"
9/1/2018   "month" 
... 
7/1/2018   "week"
7/8/2018   "week"
7/15/2018  "week" 
....

and this becomes easier to link but then I don't know how to create this table as a union of two tables with DAX syntax. 


You can create a calculated table as below:

CalendarTable2 =
UNION (
    SELECTCOLUMNS (
        CalendarTable1,
        "b_date", CalendarTable1[week_date],
        "period", "Week"
    ),
    SELECTCOLUMNS (
        CalendarTable1,
        "b_date", CalendarTable1[month_date],
        "period", "Month"
    )
)

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yulgu-msft 

Can you please explain what the advantage of the calculated table is? Bookmarks is easier, isn't it... 

Thanks, 
Marc

Hi @Diego-mx

You can create a hierachie in your visual. Add both week and month on the axis, and there will be a small icon in the visual header to change the context to week or month. Unfortunatly this will only impact that specific visual. 

An other solution could be using bookmarks. So first add week and month at all visuals like explained above. Then open the bookmarks pane in Power BI desktop. Create a bookmark with all visuals on weekly base and create a second bookmark with all visuals based on month. Then you can add a button (see upper bar) which you can assign to the created bookmarks. Now the user can switch dynamicly to week and month themselves. 

Will this work for you? 

- Marc

Thanks. That's a third solution.   

This is one solution I was avoiding because I feel suspicious of the hierarchies, and haven't really understood how they work. 
But this is one good reason to try them.  
I'll come back to upvote this.  

Thanks. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.