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
Anonymous
Not applicable

Date Table cannot be filtered by visual level slicer in some reports

Ok...I'm unsure whether this is a bug or just me (probably the latter!)

 

The date table shown below will not be sliced by a visual level slicer. However, it can be filtered using the right-hand 'filters' pane. The date table is generated using CALENDARAUTO(). I've tried using CALENDAR(), and manually setting the date range, but the symptoms still occur. 

 

 

Date table cannot be filtered by visual level slicer.png

After I re-ran CALENDARAUTO() to 'refresh' the date table, the dates can now be filtered-but only if they are in a table. As demonstrated below, a line chart using data from one of the 3 datasources will show all years generated in the date table - even when a visual level slicer is applied. When 'Edit Interactions' is displayed, the slicer is shown as filtering the chart.

 

line chart years still not filtered after regenerating date table.png

I've noticed that I cannot recreate this behaviour outside of a few select reports, so presumably there's some weird setting etc going on? These reports total a good few months of work, so I am keen to get this fixed. Thanks!

2 REPLIES 2
MFelix
Super User
Super User

Hi @Anonymous ,

 

Not really sure if I understand your question, are all of the visualizations ( table and Line chart and slicer) based on the same column?

 

How do you have the relationships between the date table and the fact table?

 

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hi MFelix, apologies if I was unclear. I appear to have resolved this, and an explanation is below. 

 

Unfortunately I couldn't find a way to attach a .pbix file to this reply, but the issue was as follows:

 

A table, Table1, contains 2 columns, 'Date' and 'Value'. The 'Value' column contains a series of integer values.

A centralised 'Date Table' is used for the x axis of a graph. It has a single column, 'Date', generated by CALENDARAUTO().

 

The 'Date' column in Table1 and the 'Date' column in Date Table are linked with a bidirectional relationship. 

 

The 'Date' column in Date Table is used as the X axis for a line chart. The 'Value' column from Table 1 is used as the Y axis. 

 

The 'Date' column from Date Table is also assigned to a 'between' style slicer (user picks date range by selecting start and end date). 

 

Using this approach, the slicer filters the chart in a normal way. 

 

But suppose a measure, 'Total', is used to summarise the data:

Total = COALESCE(SUM(Table 1[Value]),0)

 If 'Total' is assigned to the Y axis of the graph, rather than the raw data within the 'Value' column, then the chart will always show all dates, regardless of those specified by the slicer. Only the Y values will change. 

 

The cause seems to be something to do with COALESCE(). This function (I believe) requires that a substitute value be present eg ('0') whenever no value is returned by its first argument (which is SUM(Table 1[Value]). So wherever no value was present (eg for 1990), the measure returned 0 rather than null. However, I'm not sure why this would cause the year to be still visible when the slicer specifies that it should be excluded. 

 

The issue can be avoided by omitting COALESCE() from the measure concerned. Expressions like '+0' should also be avoided from what I can see. 

 

 

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.