Forum Discussion

RachelN's avatar
RachelN
Regular Visitor
6 years ago
Solved

Group by Week for multiple tables

Hi guys,

 

I hope you are all safe during this time.

I'm a new Power BI user and hope that you can help me with the best approach to solve this.

 

I have three table name Timesheet (include Staff Name, working date, working hours per date); Trello Work (includes Staff Name, working date, completed task items, hours per task item); Non-Trello work (includes Staff Name, working date, Additional ask types, hours per tasks).

 

 

 

I want to create a visualization to show Timesheet hours, Trello Work hours, Non-Trello work hours every week.

 

My relationship setting is Timesheet table is joined with Trello Work and Non-Trello work table by Staff name.

 

I created a new column "Week of Year" in Timesheet table; then use matrix table in visualization to show Timesheet hours, Trello Work hours, Non-Trello work hours every week. The issue is the Week of year of works for Timesheet hours, and not the other tables (Week 6 & 9 are the same for Trello hours, Week 6 & 7 are the same for Non-trello hours)

 
 

Please let me know if you need additional information. Thank you in advance!

 

Best regards,

 

 

 

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi RachelN ,

     

    We can try to create a calendar dim-table to connect other tables and use WeekNum column of it as the row of visual:

     

    CalendarTable =
ADDCOLUMNS ( CALENDARAUTO (), "WeekNum", WEEKNUM ( [Date] ) )

     

    We can also Create Staff dim-table by using following dax:

     

    Staff =
DISTINCT (
    UNION (
        DISTINCT ( 'Timesheet'[Staff Name] ),
        DISTINCT ( 'Trello Work'[Staff Name] ),
        DISTINCT ( 'Non-Trello work'[Staff Name] )
    )
)

     


    Best regards,

     

  • RachelN's avatar
    RachelN
    6 years ago

    Hi v-lid-msft amitchandak, thank you so much for your kind support! I've achieved the outcome that I want for my data. I really appreciate your help!

3 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi RachelN ,

     

    We can try to create a calendar dim-table to connect other tables and use WeekNum column of it as the row of visual:

     

    CalendarTable =
ADDCOLUMNS ( CALENDARAUTO (), "WeekNum", WEEKNUM ( [Date] ) )

     

    We can also Create Staff dim-table by using following dax:

     

    Staff =
DISTINCT (
    UNION (
        DISTINCT ( 'Timesheet'[Staff Name] ),
        DISTINCT ( 'Trello Work'[Staff Name] ),
        DISTINCT ( 'Non-Trello work'[Staff Name] )
    )
)

     


    Best regards,

     

    • RachelN's avatar
      RachelN
      Regular Visitor
      6 years ago

      Hi v-lid-msft amitchandak, thank you so much for your kind support! I've achieved the outcome that I want for my data. I really appreciate your help!