Reply
Member
Posts: 66
Registered: ‎08-28-2016
Accepted Solution

Timeline Slicer with Calculated Tables

Hi there,
Today I found a behaviour which I'm not sure is expected or not:

I love the timeline slicer visual but today I noticed one of my visuals not updating when I use it. On further investigation I find that the data source for that particular visual (a single row Card) is from a calculated table so I did some experimentation.

 

I found that any time I use a data source from a calculated table the card is not sliced by the timeline, although it is sliced by the 'normal' slicer. As an experiment I took a measure which is sliced by the timeline and created a column in a calculated table based on that measure and put them both on a page. The measure is sliced by time but the calculated column is not. They are both sliced by the normal slicer. I have checked the visuals interactions and they are all set to filter. I'll try posting some screenshots to try and explain this:

 

2017-03-17 16_55_17-ProjectAnalysisConsole - Copy - Building New Summary - Power BI Desktop.png

My Measure which is being sliced by the time slicer. My Invoices table is table generated from the Query Editor.

 

 

2017-03-17 16_57_08-.png

My calculated column not being sliced by the time slicer

 

The line from the calculated table for the column: 
ProjectsSummaryAverages = SUMMARIZE(
         'Jobs: Project Register'
         ,'Jobs: Project Register'[Job Number]
         ,"Actual Costs", [Actual CostsM]

         ,...etc.

I'm curious to know whether this is expected behaviour and if so, how might I work around it? The Actual Costs value is not the actual one I'm trying to get to work, it was just a simpler example I could use to test the behaviour. The actual value I'm trying to get working will not seem to work as a measure - it works fine as a column in a calcualted table but when I convert it to a measure I get unexpected results (unexpected to a basic level user as myself that is) and I also don't know how to fix that.


Accepted Solutions
Super Contributor
Posts: 1,163
Registered: ‎07-03-2015

Re: Timeline Slicer with Calculated Tables

Calculated tables and calculate columns do not respond to slicers. If your slicer is related 1 to many to the calc table or table with a calc column the slicer will filter the table in the many side.  Does that answer your question?

View solution in original post

Super Contributor
Posts: 1,354
Registered: ‎11-29-2015

Re: Timeline Slicer with Calculated Tables

Hi @KarlinOz

 

Does the field you use in your timeline slicer come from a Date/Calendar table?  And have you made a relationship between your calculated table and your Date/Calendar table?

 

Just a thought.

View solution in original post

Member
Posts: 66
Registered: ‎08-28-2016

Re: Timeline Slicer with Calculated Tables

So for completeness I'll post what I did to get it to work.

 

I had to move my measure back to one of the source tables and to get the measure to work correctly I had to create two new columns in my source table - I gather I could have just written the code for the two columns into the measure but after a couple of hours of trying I gave up and created the new columns. 

 

This is probably a good object lesson in why creating new tables is not always the best solution.

View solution in original post

Super Contributor
Posts: 1,821
Registered: ‎07-17-2016

Re: Timeline Slicer with Calculated Tables

Hi @KarlinOz,

 

Great to hear the problem got resolved! Could you accept the helpful reply as solution to help others who may have similar issue easily find the answer and close this thread?Smiley Happy

 

Regards

View solution in original post


All Replies
Super Contributor
Posts: 1,163
Registered: ‎07-03-2015

Re: Timeline Slicer with Calculated Tables

Calculated tables and calculate columns do not respond to slicers. If your slicer is related 1 to many to the calc table or table with a calc column the slicer will filter the table in the many side.  Does that answer your question?

Super Contributor
Posts: 1,354
Registered: ‎11-29-2015

Re: Timeline Slicer with Calculated Tables

Hi @KarlinOz

 

Does the field you use in your timeline slicer come from a Date/Calendar table?  And have you made a relationship between your calculated table and your Date/Calendar table?

 

Just a thought.

Member
Posts: 66
Registered: ‎08-28-2016

Re: Timeline Slicer with Calculated Tables

Thanks for your response @MattAllington, it may do. I have a relationship between Project and the calculated table and it is slicing on that ok. But not on the Date - I don't have a date field in the calculated table. I'm just heading home for the week now so on Monday I'll take a look at that.

Member
Posts: 66
Registered: ‎08-28-2016

Re: Timeline Slicer with Calculated Tables

Thanks for your thought @Phil_Seamark, it may be the key - see my response to Matt. I'll try it out on Monday.

Member
Posts: 66
Registered: ‎08-28-2016

Re: Timeline Slicer with Calculated Tables

Ok, perhaps if I were able to add a date column it would solve this problem. However my calculated table is a summary built from measures and sums of columns so I have lost the individual rows (and hence their date entry). That makes it impossible to add a date column to this table. So to solve it I think I am going to have to build my calculated column back in the original table. I've been trying to do that however the calculated column needs to take into account other measures and columns from other tables and I'm getting quite unexpected (to me anyway) results.

Member
Posts: 66
Registered: ‎08-28-2016

Re: Timeline Slicer with Calculated Tables

So for completeness I'll post what I did to get it to work.

 

I had to move my measure back to one of the source tables and to get the measure to work correctly I had to create two new columns in my source table - I gather I could have just written the code for the two columns into the measure but after a couple of hours of trying I gave up and created the new columns. 

 

This is probably a good object lesson in why creating new tables is not always the best solution.

Super Contributor
Posts: 1,821
Registered: ‎07-17-2016

Re: Timeline Slicer with Calculated Tables

Hi @KarlinOz,

 

Great to hear the problem got resolved! Could you accept the helpful reply as solution to help others who may have similar issue easily find the answer and close this thread?Smiley Happy

 

Regards