pivot measures to rows using disconnected table - measures incorrectly ignoring filters

[jakubk]

I'm trying to display my measures in a table listing the measures as rows and 'current', prior year', 'change' as columns

eg

| Measure | Current | Prior Period | Change |
|---------|---------|--------------|--------|
|rescues  |       10|             5|       5|
|live     |        1|             3|       2|
|survival |      10%|           60%|     ...|

each of those values is a separate measure in my model (i'm assuming DAX still doesn't have the MDX generic time intelligence utility dimension capabilities?)

I thought I could just create a disconnected table with rescues, live, survival in column1 and use a switch statement for current, prior period and change, but the values that come out don't change when i select filters (eg fiscal year)

Current = switch(
    true(),
    'Table'[custom_measure] = "rescues",[Rescues],
    'Table'[custom_measure] = "live",[Live release],
    'Table'[custom_measure] = "survival",[% survival rate]
)

Prior year = switch(
    true(),
    'Table'[custom_measure] = "rescues",[Rescues (prior year)],
    'Table'[custom_measure] = "live",[Live release (prior year)],
    'Table'[custom_measure] = "survival",[% survival rate (prior year)]
)

the table in the top left is done via the new measures table but the totals are the unfiltered grand totals (it's ignoring the date filter)

What am I doing wrong?

[danextian · Accepted solution]

Hi @jakubk 

Is 'Table'[custom_measure] a column? It appears that your DAX formula is that of a calculated column. Measures in a calculated column do not response to a slicer selection and which value do not refresh until it's been modified or the underlying data has been refreshed. Try either of these as a measure

Current =
SWITCH (
    SELECTEDVALUE ( 'Table'[custom_measure] ),
    "rescues", [Rescues],
    "live", [Live release],
    "survival", [% survival rate]
)


Current =
SWITCH (
    TRUE (),
    SELECTEDVALUE ( 'Table'[custom_measure] ) = "rescues", [Rescues],
    SELECTEDVALUE ( 'Table'[custom_measure] ) = "live", [Live release],
    SELECTEDVALUE ( 'Table'[custom_measure] ) = "survival", [% survival rate]
)

[jakubk]

Hi @danextian thanks for the reply - that worked!

Sorry I should have mentioned - I made a disconnected table using the 'enter data' approach. custom_measure'