Forum Discussion
pivot measures to rows using disconnected table - measures incorrectly ignoring filters
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?
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] )
2 Replies
- danextian
Super User
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] )