Timeline Slicer in values

[JasTatla]

hello all - what i am trying to do is take the values from a timeline slicer and use them in an IF statement.

the formula would look somnthing like this in excel =if((Date)=(Slicer_Datekey),True,False)

the slicer_datekey could contain multiple dates. any ideas on how i would approach this?

thanks

 

[scottsen]

Assuming you only need to worry about *1* value out of the slicer, you can do... pretty much any aggregation function you want on the table that feeds data to the slicer.

=IF (MAX(MyDatesTableThatFeedsSlicer[Date]) > "1/1/2010", "Newish", "Oldish")

 

[JasTatla]

thanks for the reply, i should prehaps elaborate on what i am trying to achive.

i have 2 tables, Date and Active. Active contains multiple date fields that have relationships with the date table, these are Logged, Resolved, Closed, Aborted, reopened. the report i am running displays logged, Resolved and Aborted based on the date Timeline the report is sliced on.
what i need to be able to report on is for the given date period the report is sliced on the number of records that the reopened and resolved date are in that period.
example

if report was sliced on a single day 18/01/2014

[TABLE="width: 501"]
<colgroup><col><col span="4"></colgroup><tbody>[TR]
[TD]Ref [/TD]
[TD]Logged[/TD]
[TD]Resolved [/TD]
[TD]Aborted[/TD]
[TD]ReOpened[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]15/01/2014[/TD]
[TD]18/01/2014[/TD]
[TD]N/A[/TD]
[TD]18/01/2014[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]20/01/2014[/TD]
[TD]20/01/2014[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]30/02/2014[/TD]
[TD]18/03/2014[/TD]
[TD][/TD]
[TD]19/03/2014[/TD]
[/TR]
</tbody>[/TABLE]

in the above example ref 1 would be counted

example 2

if report was sliced on multiple days 18/03/2014-22/03/2014

[TABLE="width: 501"]
<colgroup><col><col span="4"></colgroup><tbody>[TR]
[TD]Ref [/TD]
[TD]Logged[/TD]
[TD]Resolved [/TD]
[TD]Aborted[/TD]
[TD]ReOpened[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]15/01/2014[/TD]
[TD]18/01/2014[/TD]
[TD]N/A[/TD]
[TD]18/01/2014[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]20/01/2014[/TD]
[TD]20/01/2014[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]03/02/2015[/TD]
[TD]18/03/2014[/TD]
[TD][/TD]
[TD]19/03/2014[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]03/02/2015[/TD]
[TD]18/03/2014[/TD]
[TD][/TD]
[TD]20/03/2014[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]03/02/2015[/TD]
[TD]18/03/2014[/TD]
[TD][/TD]
[TD]22/03/2014[/TD]
[/TR]
</tbody>[/TABLE]

ref 3,4,5 would be counted


hope this makes more sense
thanks

 

[scottsen]

I assume only one of the date relationships are "active", since...well, you can't really do it another way. Though, I suppose you could have multiple date tables linked to your multiple date fields, ... I'll ignore that

I'm not super sure how you want the slicer to work. I am assuming you want it like... if March 18th is sliced, you want it to show rows that were aborted on that date, or they were re-opened on that date, ... or they were resolved on that date...

Assuming so, it might be interesting to unpivot the data (such that you get a row for each date, instead of a column for each date). In general... things always go better in power pivot if you do that

Otherwise, I think you are looking at having all the relationships inactive, so the slicer doesn't actually do anything.

Then, have calculations that counts by each of the data types.

ResolvedCount := CALCULATE(COUNTROWS(MyTable), USERELATIONSHIP(MyTable[ResolvedDate], Date[Date]))
(AbortedCount...)
(ReopenedCount...)
AllDateCount = [ResolvedCount] + [AbortedCount] + [ReopenedCount]

And you are going to end up filtering based on that AllDateCount, by straight excel techniques. Setup a Value Filter on the pivot table to only show rows where AllDateCount > 0.