1

In Power BI, I'm trying to use a measure to take the = MAX(CallDetails[Date]) that is left once I drill through on a visual.

To create a calculated table. That is a filtered version of an unrelated table AgentActivities by [Max Date].

My goal is to create 2 visuals side by side of the calls that came in on a select day with the agents that were available to take them.

I've tried quite a few vairants of this but this is essentially what i'm trying to do:

FilteredAgentActivity =
var MaxDate = [Max Date]
return
FILTER('AgentActivity',AgentActivity[Date] = MaxDate)

--This does not return any results

  if I hard code the date I get the desired result:

FilteredAgentActivity =
FILTER('AgentActivity',AgentActivity[Date] = DATEVALUE("2020/04/18"))

At this point, I'm beginning to think using a measure as a filter expression might just not compatible with what I'm trying to achieve. Does anyone know where I'm going wrong or a better way of doing this?

Thanks Ben

Hard coding date works

Measure variable fails

helvete
  • 2,455
  • 13
  • 33
  • 37
dvdccd
  • 13
  • 3

1 Answers1

0

A calculated table cannot be dynamically dependent on report filtering since these are only computed when the data is initially loaded into your model (or when you refresh this data) but not whenever you change a filter.

Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
  • Is there a way you can think of that it's achievable or do you think it's totally impossible? – dvdccd Dec 02 '20 at 15:23
  • That depends on what your ultimate goal is. If it's as narrow as creating a dynamic calculated table, then you are out of luck. However, it's very likely that you can solve the problem that you thought to use calculated tables for via a different method. – Alexis Olson Dec 02 '20 at 15:27
  • Thanks for your help Alexis. – dvdccd Dec 03 '20 at 15:32
  • If anyone else comes up against a similar issue. I was able to do this by: 1. Creating a calculated column in both the tables with the merged values I was interested in. 2. Then creating a many to many relationship between the two tables on that column in the direction I was trying to filter. – dvdccd Dec 03 '20 at 15:37