Measure to return same value over multiple rows in table visual

[PowerUP200]

I'm trying to create a measure to use in a table visual. 

I have the below table which lists 2 employee's history. I want to add two measure which list what their Acting and Substantive classifications are depending on what date is selected on an separate slicer based on a Date table. 

Person	Start date	End Date	Classification	Area	Placement Type
A	1-Jul-24	1-Aug-24	6	Stats Team	Acting
A	16-May-23	31-Dec-99	5	Data Team	Substantive
A	1-Jan-23	15-May-23	4	Data Team	Substantive
B	1-Jan-24	31-Dec-99	4	Stats Team	Substantive

The way the placements work is at any one time, each employee by default has only one substantive employment placement. However, they can sometimes act up in senior positions, in which case the system assigns them a second placement, 'Acting'. At these times, they have 2 active placements, until their acting period ends. 

So for example, if I selected the date 1 December 2023, only one row of data would return a value as person A was the only one working, and we would only get a substantive value as A was not acting. 

Person	Start date	End Date	Classification	Area	Placement Type	Substantive Placement Classification	Acting Placement Classification
A	1-Jul-24	1-Aug-24	6	Stats Team	Acting
A	16-May-23	31-Dec-99	5	Data Team	Substantive	5
A	1-Jan-23	15-May-23	4	Data Team	Substantive
B	1-Jan-24	31-Dec-99	4	Stats Team	Substantive

However, if I selected the date 1 July 2024, we would get 3 rows of data. However, this is where my second requirement comes in:

Is it possible, where someone is acting, to allocate the same classifications across both rows? I've tried with various ALL and REMOVEFILTER combinations but I can't seem to break the row context properly to apply it to both rows. 

Person	Start date	End Date	Classification	Area	Placement Type	Substantive Placement Classification	Acting Placement Classification
A	1-Jul-24	1-Aug-24	6	Stats Team	Acting	5	6
A	16-May-23	31-Dec-99	5	Data Team	Substantive	5	6
A	1-Jan-23	15-May-23	4	Data Team	Substantive
B	1-Jan-24	31-Dec-99	4	Stats Team	Substantive	4

Thanks in advance for any guidance. Happy to provide more info if needed.

[FBergamaschi · Accepted solution]

This is the result I got, I have to say it was tough to get it (so thank you!)

I just left blank the end date instead of putting year 2099 for best practice (we can fix this to work also with 2099 in case you cannot remove that value)

If that is what you were looking for, here is my solution (pbix)

https://drive.google.com/drive/folders/1Rmd2KqfCMOw-N3o190NzlDjP_7psU_jV?usp=sharing

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[PowerUP200]

Thanks @FBergamaschi, worked perfectly! The 2099 is unfortunately a quirk of the system, but I got your solution to work with it just fine. 

[rohit1991]

Hi @PowerUP200 ,

To show the same value (e.g., classification) across multiple rows for each employee and date selection, you need to calculate the value ignoring row context and just based on person and selected date.

You can use a measure like this:

SubstantivePlacement =
VAR selDate = SELECTEDVALUE('Date'[Date])
VAR selPerson = SELECTEDVALUE('YourTable'[Person])
RETURN
CALCULATE(
    MAX('YourTable'[Classification]),
    FILTER(
        ALL('YourTable'),
        'YourTable'[Person] = selPerson &&
        'YourTable'[Placement Type] = "Substantive" &&
        'YourTable'[PersonStart date] <= selDate &&
        'YourTable'[End Date] >= selDate
    )
)

And do the same for "Acting" by changing the placement type in the filter.

[Aburar_123]

Hi @PowerUP200 ,

Could you pls provide more clarity on your existing table structure and the expected result that you want to see and what result that you have derived so far.