Measure to return a value (from a column) based on another measure

[sshokri89]

Hi everyone,

I need help with this measure; I have a column in  Query1[StartDate] table. Based on that, I have created 3 scenarios and created 3 EndDate columns.

It's actually not important to explain my scenarios but they "standard contract" , "Only 3Y contract" , and "Max 3Y contract".  To sum up, I have created one EndDate based on each scenario: "EndDate_standardcontract" , "EndDate_Only3Ycontract" , and "EndDate_Max3Ycontract". 

Now I got stuck a step further, where I have brought my 3 scenarios as filters on the page, and I need to show create ONLY ONE column for my EndDate. And that column obviously needs to work in sync with the filter selection. e.g. If the user selects "Only 3Y contract", then my EndDate column needs to show the value from that column, and likewise for the other 3 scenarios.

I tried a few times but the core part of it was the switch function below (that I couldnt make it work):

End Date Dynamic = SWITCH(
       TRUE(),
       SELECTEDVALUE(Scenario[Options])="Standard contract",Values(Query1[EndDate_standardcontract]),
       SELECTEDVALUE(Scenario[Options])="Only 3Y contract",VALUES(Query1[EndDate_Only3Ycontract]),
       SELECTEDVALUE(Scenario[Options])="Max 3Y contract",VALUES(Query1[EndDate_Max3Ycontract]),
       0
)

 Thanks in advance for your help.

[sshokri89]

Thanks a lot. I see what I was missing now 🙂

[ebeery · Accepted solution]

@sshokri89 the VALUES function returns a table not a scalar value, so you won't be able to use it in a measure in the way you're showing above.  I'm guessing you got an error like below:

Depending on the context in which you are using this measure (if it's a table/matrix?), consider using SELECTEDVALUE instead of VALUES.

Below is a very simple example with some dummy data where I'm switching between a few different dates (similar to your "scenarios").

SelectedDate = 
VAR _SelectedDate = SELECTEDVALUE('Table'[SelectedDate])
Return
SWITCH(
    TRUE(),
    _SelectedDate = "DueDate", SELECTEDVALUE(Sales[DueDate]),
    _SelectedDate = "OrderDate",SELECTEDVALUE(Sales[OrderDate]),
    _SelectedDate = "ShipDate", SELECTEDVALUE(Sales[ShipDate])
)