Forum Discussion

PowerUP200's avatar
PowerUP200
Frequent Visitor
1 year ago
Solved

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 classific...
Need Help
rohit1991's avatar
rohit1991
Super User
1 year ago

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.