Forum Discussion
How to display different scale in one matrix visual
Hi,
I have a table which contains week and date (not always the starting day of the week) as below
| ID | week | date |
| 001 | W428 | 7/10 |
| 002 | W429 | 7/15 |
| 003 | W430 | 7/24 |
| 004 | W431 | 7/29 |
And I have created a matrix, ID as row, week as column, count of ID as value.
| ID | W428 | W429 | W430 | W431 |
| 001 | 1 | |||
| 002 | 1 | |||
| 003 | 1 | |||
| 004 | 1 |
Now I want the matrix to display part of weeks, and the last week show in date form
| ID | W428 | W429 | W430 | 7/28 | 7/29 | 7/30 | 7/31 | 8/1 | 8/2 | 8/3 |
| 001 | ||||||||||
| ... |
Is it possible to do? Any answer will be helpful!
Anonymous , First create a date table
DateTable =
ADDCOLUMNS (
CALENDAR (DATE(2022, 7, 1), DATE(2022, 8, 3)),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"Day", DAY([Date]),
"Week", WEEKNUM([Date], 2) -- Week starts on Monday
)Create relationship with your table using date column
Then Create a matrix visual , Drag ID column to Rows, Week column form main table to Columns and ID column to values and set it to count
Drag the Date column from the DateTable to the Columns field in the matrix visual.
Ensure that the Date column is placed after the Week column in the Columns field.
1 Reply
- bhanu_gautam
Super User
Anonymous , First create a date table
DateTable =
ADDCOLUMNS (
CALENDAR (DATE(2022, 7, 1), DATE(2022, 8, 3)),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"Day", DAY([Date]),
"Week", WEEKNUM([Date], 2) -- Week starts on Monday
)Create relationship with your table using date column
Then Create a matrix visual , Drag ID column to Rows, Week column form main table to Columns and ID column to values and set it to count
Drag the Date column from the DateTable to the Columns field in the matrix visual.
Ensure that the Date column is placed after the Week column in the Columns field.