Slicer to display data from (defined) subsequent rows???

[mirknin]

Hello

Ok, so it's fairly straightforward to use the Slicer to pick up the information from a row of data it's related to but is is possible to use either PowerPivot or Excel to and display the results from the subsequent four rows?

I have a relatively simple data set where each row represents a week, each week has a number of data columns. Occassionally, however, we have treatments and I set up Excel with a column where the treatment is recorded as 1 - i.e. a treatment occurred that week, otherwise the cells are left blank.

I AddColumn in PowerPivot to generate a unique identifier (through the usual ampersand formula) to represent whether a treatment occurred in a specific week. Using the Slicer, I can PivotTable to see the data from the row where the treatment took place because all this info is related to that week, but seeing the data for following four weeks would let me see whether the treatment had made an effect.

I guess my query is whether the Slicer can be modified in such a way as to report the row it's associated with plus subsequent rows...

Great forum, by the way!

 

[powerpivotpro]

You can't do this with a slicer that is based on a column in your "main" table, nor can you do it with a slicer that is connected to your main table via a relationship.

But with a "disconnected" slicer and some clever use of the FILTER() function, you can basically do anything

Today's blog post shows a very simple example of this:

http://www.powerpivotpro.com/2012/01/comparing-scientific-and-other-data-across-trials/

Make sure to click through to the more advanced example of disconnected slicers (another blog post that is linked to from that post) to see an example of using slicers to select ranges of data.

But neither of those is precisely what you are looking for, merely "hints" as to how you would do this.

So I think I will use this question as the subject of Thursday's blog post

 

[mirknin]

Hi Rob

That sounds perfect, I'm eagerly awaiting your blog post.

Cheers

 

[powerpivotpro]

OK the post is up but I ultimately did not use the same technique I expected to.

http://www.powerpivotpro.com/2012/02/tracking-performance-after-an-event-or-treatment/

It dragged me into the EARLIER function which is one I am not super fond of

And the more "complete" version of this problem is one I am deferring to the Italians