Help with drillthrough filtering target table correctly

[Anonymous]

Hello

I have a page containing a table summarising some sales information by customer. For example, there is a Revenue column that simply sums all Revenue for that customer, and a "Deal Revenue" column that sums all Sales for that customer where the Sale Revenue was >= £1000:

I then have a page which I want to use as a drillthrough target, which lists the sales themselves and all the details about them. What I want to be able to do is drill-through contextually depending on which column the user highlights when drilling through; for example if the user right clicks the "Revenue" column I want all the sales to be returned. If the user right clicks the "Deal Revenue" column and clicks drillthrough, I want only the rows where the Sale Revenue is >= £1000 to be returned.

I could do this with a page-per-drillthrough-context (I.E. have a "Revenue" drillthrough page and a "Deal Revenue" drillthrough page) but that's clunky and annoying so I don't want to. Is there a better way to achieve that?

Query and raw data output if interested:

Spoiler

select
e.[Employee Key]
, e.Employee
, s.[Sale Key]
, s.[Invoice Date Key]
, c.[Customer Key]
, c.Customer
, sum(s.Quantity * s.[Unit Price]) Revenue
, iif(
sum(s.Quantity * s.[Unit Price]) >= 1000,
sum(s.Quantity * s.[Unit Price]),
null
) DealRevenue
from WideWorldImportersDW.Fact.Sale s
inner join WideWorldImportersDW.Dimension.Employee e
on s.[Salesperson Key] = e.[Employee Key]
inner join WideWorldImportersDW.Dimension.Customer c
on s.[Customer Key] = c.[Customer Key]
group by
e.[Employee Key]
, e.Employee
, s.[Sale Key]
, s.[Invoice Date Key]
, c.[Customer Key]
, c.Customer

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Glad to hear that!

To share more ideas for other people who have similar problem,

I make a test to achieve this.

If i click on "Deal Revenue" column and drill through to Page3,

When i add "Deal Revenue" column in the visual level filter(or page level filter) on Page 3, set"Advanced filter"->not blank,

It would show correctly as you expected.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Sorry about the daft working title of the post! I have fixed it.