Visual filter breaking drill-through

[fraz]

I've got a drill-through page that isn't working when a particular visual filter is added and I don't know why. 

A simplified dataset where I've recreated the problem is here.

Data

• The IDs of customers are listed on a Customer table.
• Different marketing communications are on the Appeals table with an Appeal Code, grouped by a Campaign Code.
• The Communications table records the sending of marketing to a customer. Again, the Appeal Code and Campaign Code are included.
• Financial transactions are in the Transactions table, again with Appeal Code and Campaign Code.

Here's the data model:

Purpose of report

• While the Transaction table records an Appeal Code, they may have been due to marketing sent out with a different appeal code.
• I'm using a concept of attribution where I want to get the value of all Transactions where a Customer has received a Communication for a particular campaign.

Here's the DAX definition of the Attribution Transaction Value. It isn't actually relevant to the problem, but will help make sense of the visuals:

Attribution Transaction Value = CALCULATE ( [Transaction Value],
                                CROSSFILTER('Communications'[Customer ID], 'Customers'[Customer ID], Both),
                                CROSSFILTER('Appeals'[Appeal Code], 'Transactions'[Appeal Code], None))

Drill-through

I have a table showing the attributed counts and ultimately I want a tooltip to show the Appeal Code of the Communication that the Customer received. However, when I had problems I used a drill-through to give a bit more visibility to what's going on.

I've got a drill-through page with Customer ID as the filter and Keep all filters checked. The drill through page has the Appeal Code and Campaign Code from the Communication Table. I want the Customers[Customer ID] field in the table above and the Appeals[Campaign Code] from the slicer at the top of the page to filter the Communication table to show only the Appeal that the Customer received communication for.

I can get this working without any visual level filter. I get the following table when I drill through, which is what I want to see.

The filters shown are here:

Note - the Appeal Code = A10 and Campaign Code = C4 are on the Transaction table, so don't filter the Communication table.

Visual filter breaking drill-through

I have a further measure that counts rows on the Communication table:

Communication Count = COUNTROWS ('Communications')

Now when I filter my table to when Communication Count > 0 the drill-through returns no values:

This seems to be from the extra filter added due to the visual level filter:

The full text is Appeal Code, Campaign Code, Customer ID from visual. When I remove this filter the drill-through works again. Even more confusing, when I set this table up to filter from the same page I get the behaviour I want regardless of the visual filter:

Questions

• Why does this visual filter break the drill-through?
• What does that bottom filter added in the drill-through represent?
• Why does the drill-through filter differently from the visual interaction?

[johnt75]

When I ran the internal SUMMARIZECOLUMNS from the filter,

SUMMARIZECOLUMNS(
'Transactions'[Appeal Code],
'Transactions'[Campaign Code],
'Customers'[Customer ID],
__SQDS0FilterTable,
"Communication_Count", IGNORE('Measure Table'[Communication Count]),
"CountRowsTransactions", CALCULATE(COUNTROWS('Transactions'))
)

I got an empty table back. There don't appear to be any transactions which match campaign C1

[johnt75]

If you use Performance Analyzer to get the query generated for the visual on the drillthrough page and then look at that in DAX Studio you can see that the Appeals[Campaign Code] from the slicer on the main page is being used as a filter, and this results in an empty table. This is being triggered when you add the measure as a filter.

You could disable the keep all filters option and then add the fields which you do want to be kept as filters and I think that should clear the problem 

[fraz]

I wanted the Appeals[Campaign Code] to be brought through as a filter, so drilling through from the first line of the table filters to Customer S1 (from the table) and Campaign C1 (from the slicer) which does bring a result. Is that not correct?

Good call on using DAX Studio, the section highlighted in red below is what changes when the visual filter is added. Might take a while to pick through exactly what's happening here - any ideas?

DEFINE
VAR __DS0FilterTable = 
TREATAS({"S1"}, 'Customers'[Customer ID])

VAR __DS0FilterTable2 = 
TREATAS({"C1"}, 'Appeals'[Campaign Code])

VAR __DS0FilterTable3 = 
TREATAS({"A10"}, 'Transactions'[Appeal Code])

VAR __DS0FilterTable4 = 
TREATAS({"C4"}, 'Transactions'[Campaign Code])

VAR __SQDS0FilterTable = 
TREATAS({"C1"}, 'Appeals'[Campaign Code])

VAR __SQDS0Core = 
FILTER(
KEEPFILTERS(
SELECTCOLUMNS(
KEEPFILTERS(
FILTER(
KEEPFILTERS(
SUMMARIZECOLUMNS(
'Transactions'[Appeal Code],
'Transactions'[Campaign Code],
'Customers'[Customer ID],
__SQDS0FilterTable,
"Communication_Count", IGNORE('Measure Table'[Communication Count]),
"CountRowsTransactions", CALCULATE(COUNTROWS('Transactions'))
)
),
OR(
OR(
NOT(ISBLANK('Transactions'[Appeal Code])),
NOT(ISBLANK('Transactions'[Campaign Code]))
),
NOT(ISBLANK('Customers'[Customer ID]))
)
)
),
"'Transactions'[Appeal Code]", 'Transactions'[Appeal Code],
"'Transactions'[Campaign Code]", 'Transactions'[Campaign Code],
"'Customers'[Customer ID]", 'Customers'[Customer ID],
"Communication_Count", [Communication_Count]
)
),
[Communication_Count] > 0
)

VAR __DS0Core = 
CALCULATETABLE(
SUMMARIZE('Communications', 'Communications'[Appeal Code], 'Communications'[Campaign Code]),
KEEPFILTERS(__DS0FilterTable),
KEEPFILTERS(__DS0FilterTable2),
KEEPFILTERS(__DS0FilterTable3),
KEEPFILTERS(__DS0FilterTable4),
KEEPFILTERS(__SQDS0Core)
)

VAR __DS0PrimaryWindowed = 
TOPN(501, __DS0Core, 'Communications'[Appeal Code], 1, 'Communications'[Campaign Code], 1)

EVALUATE
__DS0PrimaryWindowed

ORDER BY
'Communications'[Appeal Code], 'Communications'[Campaign Code]