Forum Discussion

jakubk's avatar
jakubk
Icon for Helper I rankHelper I
2 years ago
Solved

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

  • 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's avatar
      jakubk
      Icon for Helper I rankHelper I

      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'