Forum Discussion
Comparing row value with column value and returning a value if there is a match
Hi,
I have not been able to find my answers within the threads. Of note, LOOKUP doesn't seem to help with the matching and returning of values for me. This is quite tricky for me as real dates aren't being used but rather strings.
Here are the two datasets I am working with. The first table is called Projects.
| Project | Planning Start Time | Planning End Time | Execution Start Time | Execution End Time |
| Creating a Garden | Q1 WY22 | Q2 WY22 | Q2 WY22 | Q4 WY22 |
Renovating Bedroom | Q1 WY23 | Q3 WY23 | Q4 WY23 | Q1 WY26 |
| Final Year Research | Q3 WY23 | Q1 WY24 | Q2 WY24 | Q4 WY24 |
Next, I have a 'date' table called dimDate that contains all the Q/WY linked to dates:
| Date | Q_WY |
| 01/01/2022 | Q1 WY22 |
| 02/01/2022 | Q1 WY22 |
| 03/01/2022 | Q1 WY22 |
| ... | ... |
| 01/04/2022 | Q2 WY22 |
| ... | ... |
My aim is to create a matrix that is able to capture this info, similar to a Gantt Chart. Currently, I am using a matrix, and the rows is from Project['Project Name'], and the column values from dimDate['Q_WY']. If the Q/WY of the Project matches with the calendar Q/WY dates, the cell should return a value of 1, if not, returns 0. See below for example. What measure should I create for the Values column to enable this?
| Project Name | Sub-task | Q1 WY22 | Q2 WY22 | Q3 WY22 | Q4 WY22 | Q1 WY23 | ... |
| Creating a Garden | Planning | 1 | 1 | 0 | 0 | 0 | ... |
| Execution | 0 | 0 | 1 | 1 | 0 | ... | |
Renovating Bedroom | Planning | 0 | 0 | 0 | 0 | 1 | ... |
| Execution | 0 | 0 | 0 | 0 | 0 | ... |
| Final Year Research | Planning | 0 | 0 | 0 | 0 | 0 | ... |
| Execution | 0 | 0 | 0 | 0 | 0 | ... |
Thank you so much!
2 Replies
v-henryk-mstfCommunity Support
Hi mjairus ,
I can't reproduce this expected result table for the time being, can you further explain your requirements and provide test data so that I can answer for you as soon as possible.
Looking forward to your reply.
Best Regards,
Henry- Aburar_123
Solution Supplier
Hi mjairus ,
You can unpivote the Project table columns as below,
then join Value column with Q_WY column in the dimDate table.
Please accept it as a solution if it solves your problem. Thank you.
