Why isn't my SELECTEDVALUE Variable returning a number that I can then use as a constant?

[Carmichael01]

I am trying to use SELECTEDVALUE as a variable to get a fixed number value based on a slicer selection and then use that value in another measure.

What I am looking to accomplish; I want to 'catch' the value of a measure [Adjusted Aveage Cost/SqFt (Global):] based on the [City] value that is selected in a diconnected slicer.

 The above is a mock up of how I would like it to work: 

1. The City Selected is Perth

2. Perth has an Adjusted Average Cost/SqFt of 96.42

3. Use 96.42 as the baseline value to calculate the variance.

My Problem: I cant get the [Baseline Value] to dynamically reference the Adjusted Average Cost dependent on the city selected. As can be seen below the [Baseline Value:] ends up being the same as the [Adjusted Average Cost/SqFt (Global):] instead os the value for Perth = 96.42 

My DAX

• Create the disconnected slicer for city from existing Location Tbl

City Slicer = DISTINCT(Location[City])​

• Create the [Selected City (Baseline:)] measure to catch the correct city as per the value in the [City Slicer]

Selected City (Baseline): = 
VAR _adjustedCost = [Adjusted Average Cost/SqFt (Global):]
VAR _city = 
SELECTEDVALUE(
    'City Slicer'[City], 
    "Please select only one Baseline City"
)
RETURN

IF(
    ISBLANK(_adjustedCost), BLANK(),
    _city
)​

• Create the [Baseline Value:] measure using the [Selected City (Baseline)] as a variable. Note: Location[City] is what is being displayed in the rows of the tble. 

Baseline Value: = 
VAR _AdjustedCost = [Adjusted Average Cost/SqFt (Global):]
VAR _city = [Selected City (Baseline):]
VAR _Baseline = 
CALCULATE(
    [Adjusted Average Cost/SqFt (Global):],
    REMOVEFILTERS(Location[City]),
    Location[City] = _City
)

RETURN
IF(
    ISBLANK(_AdjustedCost),
        blank(),
        _Baseline
)​

Note sure what is going on with the above but if I swap out _city and hard code "Perth" then it works as expected (The first screenshot).  Below is the DAX with hardcoded value that works.

Baseline Value: = 
VAR _AdjustedCost = [Adjusted Average Cost/SqFt (Global):]
VAR _city = [Selected City (Baseline):]
VAR _Baseline = 
CALCULATE(
    [Adjusted Average Cost/SqFt (Global):],
    REMOVEFILTERS(Location[City]),
    Location[City] = "Perth"
)

RETURN
IF(
    ISBLANK(_AdjustedCost),
        blank(),
        _Baseline
)

What I am looking for is to get the value based on the dyanmic filter applied.

Any help much appreciated.

[siwic]

Hi Carmichael01, 

I know that this is an old post, but thank you so much for posting your question and finding a solution to it yourself! It is wierd, but your solution is the only solution that helped me as well, which is simply resetting the slicer to default and then it starts to work without over complicating the measures or the model. Not sure how robust the solution is, but I implemented it as it works as expected.

Thanks again! 

[sturlaws]

Hi @Carmichael01 

I am not quite sure I understand how your model is set up. The slicer is using the 'City slicer'-table, while the table-visual is using the 'Location'-table, right?

Then I don't understand what you are doing here

Selected City (Baseline): = 
VAR _adjustedCost = [Adjusted Average Cost/SqFt (Global):]

Does the report contain data you are able to share? It will be easier to help you if you share your report

Cheers,
Sturla

[Carmichael01]

@sturlaws - thanks for your reply.  I can certainly share but I would like to limit who it is shared with... there is some sensitivity but let me know if I can send to you.

I am showing the perspective of of the tables in the model that have lineage to any of the measures referenced.

As for the what I am doing here..

Selected City (Baseline): = 
VAR _adjustedCost = [Adjusted Average Cost/SqFt (Global):]

The [Adjusted Average Cost/SqFt (Global):] is the selected value that I want to GRAB but in this measure I am just using it as a way to filter out rows in the table that dont have an associated value against it.  Otherwise all the cities show up when no all cities have an Adjusted Average Cost/Sqft Value. 

The measure itself has a few steps to get to it which are referenced below.... I hope the following makes sense.  I am just putting it in one code block but these are all seperate measures that build up to make the Adjusted Average Cost/SqFt value

Adjusted Average Cost/SqFt (Global): = 
DIVIDE(
    [Adjusted Cost/SqFt (Global):],
    [No. of Projects:]
)
//[No. of Projects] is a simple count of all projects.  I have included the measure below

Adjusted Cost/SqFt (Global): = 
DIVIDE(
    [Adjusted Total Cost (Global Currency):], 
    [Project Size (SqFt):]
)

// Global Currency - calculation to derive a normalised cost per sqft rate for comparison purposes on base global currency.  Project size is just a simple reference to a project size 

Adjusted Total Cost (Global Currency): = 
VAR FPglobal = [Selected Scenario Cost (Global Currency):] 
VAR qualitySpec = SELECTEDVALUE('Quality Specification'[Quality Level Impact])
VAR result = FPglobal * qualitySpec
RETURN

result

//This is the fully adjusted cost in Global Currency.  
//FPGlobal - User has ability to select a different scenario which changes the qtys that are used to build up a cost.
//qualitySpec - the user has ability to select a multiplyer to apply to over all cost based ona quality expectation

Selected Scenario Cost (Global Currency): = 
SWITCH(TRUE(),
    [selectedScenarioNo:] = 1, [Total Scenario 1 (Global Currency):],
    [selectedScenarioNo:] = 2, [Total Scenario 2 (Global Currency):],
    [selectedScenarioNo:] = 3, [Total Scenario 3 (Global Currency):],
    [selectedScenarioNo:] = 4, [Total Scenario 4 (Global Currency):],
    [selectedScenarioNo:] = 5, [Total Scenario 5 (Global Currency):],
    [selectedScenarioNo:] = 6, [Total Scenario 6 (Global Currency):],
//  [selectedScenarioNo:] = 7, [Total Scenario 7 (Global Currency):],
//  [selectedScenarioNo:] = 8, [Total Scenario 8 (Global Currency):],
//  [selectedScenarioNo:] = 9, [Total Scenario 9 (Global Currency):],
//  [selectedScenarioNo:] = 10, [Total Scenario 10 (Global Currency):],
    0
)

//Refernces the disconnected floorplate specification slicer and returns the 'sort' value which is equivalent to the scenario.  This requires a manual update when new scenarios are added.  10 placeholders created. 

 
The first step (and where it finally utilises actaul values in columns) are the scenarios as per the Switch statement.  As an example of one scenario

Total Scenario 1 (Global Currency): = 

SUMX(
    Costs, 
    Costs[Rate (Global)] * Costs[Scenario - 1]
)

Again let me know if sharing the file would help.  And again appreciate you (& others) looking at this

[sturlaws]

Sorry, I did not realize that it was a measure, which I should have.

Trying to recreate the issue, but it work fine on my very simple data set. You can share it with me by private message, if you like. Or create a sample report with issue reproduced.

[Carmichael01 · Accepted solution]

The issue was not with the measures but rather the use of a chiclet slicer I was using on the report.  For what ever reason when I deleted the chiclet and replaced the slicers with the standard options it 'worked' as expected and the selected - no change to the DAX was required.