USERELATIONSHIP + slicer selecting?

[sherifffruitfly]

hi all,

from googling, i see that i have the classic use-case for userelationship - multiple date cols (requesteddate, shipdate, deliverdate), and then product info joined to it. my typical pivottable with this data has dates as rows, products as cols, and quanties as their intersections.

what i would like, beyond the userelationship method of swapping, is a slicer, whose selections are Requested, Shipped, Delivered, and then somehow the dates in the table (& associated measures) update to reflect which relationship the user selected with the slicer.

is this possible?

thanks much for any tips,
sff

 

[scottsen]

Should be possible with a "disconnected slicer" (or maybe called "disconnected table"). I would google for those terms... I know there are easily 30 posts on PowerPivotPro.com that discusses those techniques.

The quicky version...

1. create (with just copy/paste) a dummy table in power pivot with the values: Requested, Shipped, Delivered
2. write a measure... say [SelectedDateType] := LASTNONBLANK(DateTypeDummyTable) --- this measure is just use to grab the currently selected value out of the slicer.
3. use switch or IF() to change a measure based on the selection...
IF ([SeletedDateType] = "Shipped", [MeasureThatUsesShippedDateViaUseRelationship]",
IF ([SelectedDateType] = "Delivered", ..., ...)