Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
KarlinOz
Advocate III
Advocate III

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.

4 ACCEPTED SOLUTIONS

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?



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

Phil_Seamark
Employee
Employee

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.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

KarlinOz
Advocate III
Advocate III

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

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

7 REPLIES 7
KarlinOz
Advocate III
Advocate III

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.

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

KarlinOz
Advocate III
Advocate III

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.

Phil_Seamark
Employee
Employee

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.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

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?



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.