topic Re: Trying to display the most recent role for a given date range. in DAX Commands and Tips

Trying to display the most recent role for a given date range.

Oberon — Tue, 19 Mar 2024 22:58:08 GMT

Hi,

I've got the following table:

In a visuilization I'm using an unrelated date table to select a date and want a table that returns the latest 'Role' for each 'Agent Name'. (or 'Employee ID')

On the visualisation I'm using date slicers and then measures to try and achieve the result.

The measure I'm using to get the latest 'Role' for the selected date is:

Current role = var _selectedDateRangeEnd=LASTDATE('Date'[Date]) var _selectedDateRangeStart=FIRSTDATE('Date'[Date]) var _EmploymentsBeforeDateRange= FILTER( EmploymentHistory, EmploymentHistory[Date]<=_selectedDateRangeEnd) var _LastRoleChangeDate=MAXX( _EmploymentsBeforeDateRange, EmploymentHistory[date]) var _lastRole=LOOKUPVALUE( EmploymentHistory[Role], EmploymentHistory[Date], _LastRoleChangeDate) return if( COUNTROWS(VALUES(EmploymentHistory[agent Name]))=1, _lastRole )

For some reason it works fine if there is more than 1 result returned for any given agent name however I get

"A table of multiple values was supplied where a single value was expected" as soon as there's only a single result returned.

I've tried debugging and am fairly sure the issue is the lookupvalues part of the formula but can't work out why or how to fix it. (possibly something about returning the first value?) I tried FIRSTNONBLANK after some searching but either can't figure out the syntax or it won't work in this case.

Happy to try anything which will work even if the method is completely different!

Re: Trying to display the most recent role for a given date range.

123abc — Wed, 20 Mar 2024 05:18:30 GMT

It seems like you're encountering an issue when there's only one result returned for a given agent name. This could be due to the LOOKUPVALUE function expecting a single value but receiving multiple values in certain cases.

To address this issue, you can modify your DAX measure to handle cases where there's only one result differently. One approach could be to check if there's only one result for the agent name and if so, directly return that result, otherwise proceed with your existing logic to find the latest role.

Here's how you can adjust your DAX measure:

Current role =
VAR _selectedDateRangeEnd = LASTDATE('Date'[Date])
VAR _selectedDateRangeStart = FIRSTDATE('Date'[Date])
VAR _EmploymentsBeforeDateRange =
FILTER(
EmploymentHistory,
EmploymentHistory[Date] <= _selectedDateRangeEnd
)
VAR _LastRoleChangeDate =
MAXX(
_EmploymentsBeforeDateRange,
EmploymentHistory[Date]
)
VAR _lastRole =
LOOKUPVALUE(
EmploymentHistory[Role],
EmploymentHistory[Date],
_LastRoleChangeDate
)
RETURN
IF(
COUNTROWS(VALUES(EmploymentHistory[agent Name])) = 1,
MAX(EmploymentHistory[Role]), // Return the single role directly
_lastRole // Use the existing logic to find the latest role
)

In this modification, if there's only one result for the agent name, it directly returns that role using the MAX function. Otherwise, it proceeds with your existing logic to find the latest role.

This adjustment should resolve the issue you're encountering when there's only one result for a given agent name.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Re: Trying to display the most recent role for a given date range.

Oberon — Wed, 20 Mar 2024 21:46:11 GMT

Thanks 123ABC,

That certainly made a difference and doesn't return the multiple rows error, but using that code I'm now finding that the current role doesn't change as I select months. For example: Employee C returns Team Leader regardless of whether I select April, May or June even though Employee C should be showing as Secondment from May onwards.

And apologies, I would load a PBIX file in the thread but I don't have the ability to do so due to being relatively new to the forum.

Re: Trying to display the most recent role for a given date range.

Anonymous — Tue, 26 Mar 2024 06:44:48 GMT

Hi Oberon

If I understand correctly, you need to show the most recent post for each Agent name in the corresponding date range. Here is the test I did.

My two slicer tables sample:

Create a measure as follow

Measure = VAR _MaxDate = CALCULATE(MAX([Date]), FILTER('EmploymentHistory', [Date] <= MAX('Date'[Date]) && [End Date] > MAX('Date'[Date]) || [Date] <= MAX('Date'[Date]) && [End Date] = BLANK())) RETURN IF(SELECTEDVALUE(EmployeeID[Employee ID]) = BLANK() && MAX([Date]) = _MaxDate, 1, IF(MAX([Date]) = _MaxDate && MAX([Employee ID]) = SELECTEDVALUE(EmployeeID[Employee ID]), 1, 0))

Put the measure into the visual-level filters, set up show items when the value is 1.

Result:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Trying to display the most recent role for a given date range.

Oberon — Tue, 26 Mar 2024 21:47:18 GMT

Yes, this works exactly how I wanted! Thank you I knew there had to be a way but it was driving me insane trying to work it out!