Tabular action *always* returns the All member

Coordinator
Jul 27, 2012 at 3:50 PM

MarkGStaceyToday at 10:07 AM

Using the code above, my action *always* returns the All member in Excel, i.e. it ignores both filter and row context.

Any ideas of how to fix?

Coordinator
Jul 27, 2012 at 3:51 PM

Can you post the full setup of your action? There's lots of code on the Tabular Actions Editor page, so I'm not sure which you're talking about. What type of action?

What's the MDX of your PivotTable where you're launching the action from. And which cell are you launching it from?

Jul 27, 2012 at 6:07 PM

Hi

Thanks for the prompt response.

So, this one:

Property Setting
Name DAX Query Rowset
Caption "Reseller Sales In " + [Sales Territory].[Sales Territory Region].CurrentMember.Name + " Sales Territory..."
Caption is MDX? Checked
Description This action will display a rowset with the Reseller Sales for this Sales Territory Region
Action Type Rowset
Target Type AttributeMembers
Target [Sales Territory].[Sales Territory Region]
Condition  
Expression

"
Evaluate(
CalculateTable(
'Reseller Sales'
,'Sales Territory'[Sales Territory Id] = "
+ {Existing [Sales Territory].[Sales Territory Id].[Sales Territory Id].Members}.Item(0).Name
+ "
)
)
"

Invocation

Interactive

 

In Excel, the MDX : {Existing [Sales Territory].[Sales Territory Id].[Sales Territory Id].Members}.Item(0).Name  always returns the ALL member.

 

I've tried several versions, including matching TargetType to also be attribute members (although I really want this to be an action on a measure), and in all variations I've played with, I get the all member back.

Jul 27, 2012 at 6:13 PM

Here's my DAX:

 

"Evaluate(CalculateTable(SUMMARIZE(    Balance,    Balance[LEDGER_KEY], Balance[PERIOD_NAME] )
,FILTER(Balance, 'Balance'[LEDGER_KEY] = " + {Existing {[Balance].[LEDGER_KEY].Members}.Item(0).member_name +"  )
))

"

 

 

Then this is the DAX issued if I check Profiler:

Evaluate(CalculateTable(filter(SUMMARIZE(    Balance,    Balance[LEDGER_KEY], Balance[PERIOD_NAME] ),'Balance'[LEDGER_KEY] = All
)
))

 

I've tried having Ledger Key in the filter and on the row, neither work

Coordinator
Jul 27, 2012 at 6:23 PM

Luckily, I think this is a pretty easy fix. Try changing:
{Existing {[Balance].[LEDGER_KEY].Members}.Item(0)

To:
{Existing {[Balance].[LEDGER_KEY].[LEDGER_KEY].Members}.Item(0)

The reason it's returning the All member is that [Balance].[LEDGER_KEY].Members includes the All member, and the All member always exists with the current member of your other hierarchy.

Jul 27, 2012 at 6:33 PM

*Feels like an idiot*
I should have picked that up. Going to blame the mixing of MDX / DAX :P

Thanks for the prompt help!