Forum Discussion
Role-Playing Dimensions with two dates
Hey all,
I've done a bunch of research and I have been watching Guy in a Cube with Role-Playing Dimensions - though I'm a little stuck.
I'm trying to figure out how many activities were started and ended on a particular day, and work with a single date slicer accordingly.
I'm using a matrix table with the following:
- X-axis - Activity Type
- Y-axis - Count of Activity Type
- Legend - Status
Simplified, I just want it to show where I pick February 2024 as a month in my date slicer, it'll show:
| Date | Started | Closed |
| 02/01/2024 | 6 | 0 |
| 02/03/2024 | 0 | 6 |
But if I pick 3 February 2024 specifically in the slicer, it'll show:
| Date | Started | Closed |
| 02/03/2024 | 0 | 6 |
What I've done
My main data source is called 'Data' and I've created two date tables using the following:
- StartDate = CALENDAR(MIN('Data'[Start Date]), MAX('Data'[Start Date]))
- EndDate = CALENDAR(MIN('Data'[End Date]), MAX('Data'[End Date]))
I've then created the necessary relationships, though I'm stuck on getting the figures to match or make the relationships work.
Guy in a Cube used 'Shipped Sales Amount = CALCULATE(SUM('Internet Sales'[SalesAmount]), USERELATIONSHIP('OrderDate'[Date], 'Internet Sales'[Ship Date]))' for his method, and even when I've tried to recreate this, I can't make it match.
Have I just lost my train of thought each time or is what I'm doing possible?
My data sample is below -
Start Date | End Date | Status | Activity Type | User |
| 1/01/2021 | 31/01/2021 | Done | 1111 | User 1 |
| 1/02/2021 | 28/02/2021 | In Progress | 2222 | User 2 |
| 1/03/2021 | 31/03/2021 | Unallocated | 3333 | User 3 |
| 1/04/2021 | 30/04/2021 | Pending | 4444 | User 4 |
| 1/05/2021 | 31/05/2021 | Done | 1111 | User 1 |
| 1/06/2021 | 30/06/2021 | In Progress | 2222 | User 2 |
| 1/07/2021 | 31/07/2021 | Unallocated | 3333 | User 3 |
| 1/08/2021 | Pending | 4444 | User 4 | |
| 1/09/2021 | 30/09/2021 | Done | 1111 | User 1 |
| 1/10/2021 | 31/10/2021 | In Progress | 2222 | User 2 |
| 1/11/2021 | 30/11/2021 | Unallocated | 3333 | User 3 |
| 1/12/2021 | 31/12/2021 | Pending | 4444 | User 4 |
| 1/01/2022 | 31/01/2022 | Done | 1111 | User 1 |
| 1/02/2022 | 28/02/2022 | In Progress | 2222 | User 2 |
| 1/03/2022 | 31/03/2022 | Unallocated | 3333 | User 3 |
| 1/04/2022 | 30/04/2022 | Pending | 4444 | User 4 |
| 1/05/2022 | 31/05/2022 | Done | 1111 | User 1 |
| 1/06/2022 | 30/06/2022 | In Progress | 2222 | User 2 |
| 1/07/2022 | 31/07/2022 | Unallocated | 3333 | User 3 |
| 1/08/2022 | 31/08/2022 | Pending | 4444 | User 4 |
| 1/09/2022 | 30/09/2022 | Done | 1111 | User 1 |
| 1/10/2022 | 31/10/2022 | In Progress | 2222 | User 2 |
| 1/11/2022 | 30/11/2022 | Unallocated | 3333 | User 3 |
| 1/12/2022 | 31/12/2022 | Pending | 4444 | User 4 |
| 1/01/2023 | Done | 1111 | User 1 | |
| 1/02/2023 | 28/02/2023 | In Progress | 2222 | User 2 |
| 1/03/2023 | 31/03/2023 | Unallocated | 3333 | User 3 |
| 1/04/2023 | 30/04/2023 | Pending | 4444 | User 4 |
| 1/05/2023 | 31/05/2023 | Done | 1111 | User 1 |
| 1/06/2023 | 30/06/2023 | In Progress | 2222 | User 2 |
| 1/07/2023 | 31/07/2023 | Unallocated | 3333 | User 3 |
| 1/08/2023 | Pending | 4444 | User 4 | |
| 1/09/2023 | Done | 1111 | User 1 | |
| 1/10/2023 | 31/10/2023 | In Progress | 2222 | User 2 |
| 1/11/2023 | 30/11/2023 | Unallocated | 3333 | User 3 |
| 1/12/2023 | 31/12/2023 | Pending | 4444 | User 4 |
| 1/02/2024 | 3/02/2024 | Done | 1111 | User 4 |
| 1/02/2024 | 3/02/2024 | Done | 1111 | User 4 |
| 1/02/2024 | 3/02/2024 | Done | 1111 | User 4 |
| 1/02/2024 | 3/02/2024 | Done | 1111 | User 4 |
| 1/02/2024 | 3/02/2024 | Done | 1111 | User 4 |
| 1/02/2024 | 3/02/2024 | Done | 1111 | User 4 |
Thanks in advance
3 Replies
This worked for my scenario - I was so close in what I had experimented with! Though that really helped and I was able to translate it into my own report - thanks!
Hey ElliotPBI ,
you can not control the values of two different columns with a single slicer.
Role Playing dimensions are shape shifter, or actors in disguise, for this reason they can never appear on the stage at the same time.If you need to select two values from two different columns (start and end) at the same time, then you need two date tables, two "active" relationships, and two slicers.
My friend Nikola Ilic blogged about it here: https://data-mozart.com/welcome-to-powerbi-thetare-role-playing-dimensions/
He calls it "two roles - two actors".
Hopefully, this helps to tackle your challenge.
Regards,
Tom
