Changing the LOOKUPVALUE based off the value of the selected Slicer

[Unorthodox]

Hi, 

I am trying to find a dynamic solution for the following issue that is best illustrated with some helpful images. 

I am trying change the value that is being used in the measure 'Capacity: Points Represented' based on the value of the release, in this case it would be the release '2022.1'. 

Each release has a different release capacity value that for instance is stored in an excel table on Sharepoint like:

Team	Current Release	Release Capacity	Next Release	Next Capacity
Awesome	2022.1	100	2022.2	200

Currently, the measure's formula is this as follows:

Capacity: Points Represented (Awesome) = DIVIDE(

    SUM('Features'[Effort]),

    LOOKUPVALUE('Release Capacities'[Release Capacity],'Release Capacities'[Team],"Awesome")

What I am struggling to find out is if there's a way to store, in a variable, the values of the currrent context Power BI is filtering on.

In this case I imagine it would be solved using:

Capacity: Points Represented (Awesome) =

VAR Release = currentRelease
DIVIDE(

    SUM('Features'[Effort]),

    LOOKUPVALUE('Release Capacities'[Release Capacity] = currentRelease ,'Release Capacities'[Team],"Awesome")

Then it could dynamically change the value being being used to divide the summation of Effort Points by 200 instead of 100 to match when someone wants to view the status of the next release.

I'd love to hear if I am missing something quite obvious for measures, thanks!

[lbendlin]

"store, in a variable, the values of the currrent context Power BI is filtering on." - What for?  to use later when the context has changed?

[Unorthodox]

Pretty much. Depending on the current context, I'd like to change the release capacity I'm dividing by. Our release capacity is 100 for 2022.1 and 200 for 2022.2. From what I understand I cannot select the field 'Features'[Release Planned] because it refers to many rows of data, when really I know they all are going to be "2022.1". That way as the releases progress over time I never have to update the formula.

I came up with this as a solution that is not elegant because I'd like to not have to update the value in the formula when our next release come out but this is the best I've gotten to. If only I could store the the value of what release is selected I can replace "2022.1" below with a variable.

Measure  =

Capacity: Points Represented (Awesome) = IF( SELECTEDVALUE('Features'[Release Planned]) == "2022.1", DIVIDE(

    SUM('Features'[Effort]),

    LOOKUPVALUE('Release Capacities'[ReleaseCapacity], 'Release Capacities'[CurrentRelease],VALUE(SELECTEDVALUE('Features'[Release Planned])),'Release Capacities'[Team], SELECTEDVALUE('Features'[Team])

)), DIVIDE(

    SUM('Features'[Effort]),

    LOOKUPVALUE('Release Capacities'[NextCapacity], 'Release Capacities'[NextRelease],VALUE(SELECTEDVALUE('Features'[Release Planned])),'Release Capacities'[Team], SELECTEDVALUE('Features'[Team]))))

[lbendlin]

Power BI has no memory. You need to do that in the upstream system. For example you can keep your "variable" in a text file on a sharepoint that you then read in as part of your dataset refresh.

[Unorthodox]

I understand that it can not have memory but how do functions like this line above work? SELECTEDVALUE('Features'[Release Planned]) == "2022.1"

That can recognize the value of the variable by comparison but can you not individually refer to it by reference? Like 'Features'[Release Planned][0] as if you were selecting an array?

[lbendlin · Accepted solution]

Correct. Power BI also does not have a concept of row numbers or column numbers.  When you ask it to give you the "previous row value" all you get back is a blank stare.  It is entirely up to you and your filter context to define what "previous" means in that particular situation.

What you can do is use TOPN, MAXX, MINX etc.  But it is much better to have either a reliable index column (or a timestamp) or an upstream indicator.

And don't get me started with SELECTEDVALUE.  That's a whole 'nother can of worms.