Forum Discussion
Measure to return same value over multiple rows in table visual
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.
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
4 Replies
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.
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.
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
Thanks FBergamaschi, worked perfectly! The 2099 is unfortunately a quirk of the system, but I got your solution to work with it just fine.
