Forum Discussion
How to display different content on a table / matrix based on different date is selected?
Hi, I am new to Power BI. I have different sets of data as follows:
1. Item Price Table
| Item | Price £ | Date (Year Month) |
| Green Apple | 10 | 2024-01 |
| Ice Cream | 6 | 2024-01 |
| MacBook | 800 | 2024-01 |
| Red Apple | 5 | 2024-01 |
| Trousers | 50 | 2024-01 |
| TV | 1200 | 2024-01 |
| Green Apple | 8 | 2024-02 |
| Ice Cream | 600 | 2024-02 |
| MacBook | 1000 | 2024-02 |
| Red Apple | 5 | 2024-02 |
| TV | 1000 | 2024-02 |
| Chips | 3 | 2024-02 |
| T-shirt | 50 | 2024-02 |
| Flip-flops | 7 | 2024-02 |
2. Mapping Table
| Item | Category | Sub Category |
| Green Apple | Food | Fruit |
| Ice Cream | Food | Frozen Food |
| MacBook | Appliances | Laptop |
| Red Apple | Food | Fruit |
| Trousers | Clothes | Bottom |
| TV | Appliances | TV |
| Chips | Food | Snacks |
| T-shirt | Clothes | Top |
| Flip-flops | Clothes | Shoes |
3. Category Table
| Category | Sub Category 1 | Sub Category 2 | Sub Category 3 |
| Food | Fruit | Frozen Food | Snacks |
| Clothes | Top | Bottom | Shoes |
| Appliances | Laptop | TV | Vacuum |
4. Colour & Order of both Category & Sub Cat. by Date
| Attribute | Content | Order | Colour | Date (Year Month) |
| Category | Food | 1 | G | 2024-01 |
| Sub Category 1 | Fruit | 2 | G | 2024-01 |
| Sub Category 2 | Frozen Food | 3 | G | 2024-01 |
| Sub Category 3 | Snacks | 4 | Y | 2024-01 |
| Category | Clothes | 5 | Y | 2024-01 |
| Sub Category 1 | Top | 6 | Y | 2024-01 |
| Sub Category 2 | Bottom | 7 | G | 2024-01 |
| Sub Category 3 | Shoes | 8 | Y | 2024-01 |
| Category | Appliances | 9 | R | 2024-01 |
| Sub Category 1 | Laptop | 10 | R | 2024-01 |
| Sub Category 2 | TV | 11 | Y | 2024-01 |
| Sub Category 3 | Vacuum | 12 | R | 2024-01 |
| Category | Clothes | 13 | G | 2024-02 |
| Sub Category 1 | Top | 14 | Y | 2024-02 |
| Sub Category 2 | Bottom | 15 | G | 2024-02 |
| Sub Category 3 | Shoes | 16 | G | 2024-02 |
| Category | Appliances | 17 | R | 2024-02 |
| Sub Category 1 | Laptop | 18 | R | 2024-02 |
| Sub Category 2 | TV | 19 | R | 2024-02 |
| Sub Category 3 | Vacuum | 20 | Y | 2024-02 |
| Category | Food | 21 | R | 2024-02 |
| Sub Category 1 | Fruit | 22 | R | 2024-02 |
| Sub Category 2 | Frozen Food | 23 | R | 2024-02 |
| Sub Category 3 | Snacks | 24 | R | 2024-02 |
Expected outcome shown in the Power BI report
The combination of the Category & Sub Categories are always the SAME, but the colours & the order will be different based on different date.
Problems I am facing
I am unable to make such table / matrix to display the expected outcome.
I have difficulites to create relationship between the tables because there are circulation.
I don't know how to do conditional formatting based on different date.
I also have no idea on how to create the matrix with the different content & order.
Do I need to create another data source table & unpivot other columns something like that? Many thanks for your help!
2 Replies
- lbendlin
Super User
Repeating the same data in the same visual is a design red flag. Using SubCategory1, SubCategory2 and SubCategory3 will paint you into a corner - just imagine the maintenance effort when SubCategory4 comes along.
What are the color coding rules?
Deeply sorry for my late response. Thank you very much for your help.
I used some other ways to complete this.
