Role-Playing Dimensions with two dates

[ElliotPBI]

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

[danextian · Accepted solution]

Hi @ElliotPBI ,

You can have two approaches:

Dates table not having a relationship to fact

Dates table having an active and inactive relationship to fact.

Please see sample pbix.

[ElliotPBI]

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!

[TomMartens]

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