Using a slicer based on a joined table...

[Matty]

Hi,

I've got two tables: one is 'sales' data with a single date column; one is 'date' data containing ISO weeks, periods and years, etc.

I've linked the two tables so that I can filter my sales data using the various date information held in the date table.  It works well, but the slicers linked to the date table show ALL the values from that table, even though there is not necessarily sales date for all of the dates.

Is there a way to limit what is shown in the date slicers so that only dates relevent to the data in the sales table are shown?

Cheers,

Matty

[v-huizhn-msft]

Hi @Matty,

You can create a calculated column in 'date' table using the formula.

column=RELATED(sales[sale])

Create a New Table based on the column above.

New Table=FILTER('date','date'[column]<>BLANK())

Finally, create a slicer using the field in New Table.

Best Regards,
Angelia

[Greg_Deckler]

Sure, just add a filter to the slicer to filter out dates with no sales data.

[opticshrew]

Hi there,

You could create a seperate data table using the calendar function.

Date =CALENDAR (DATE(2000;1;1); DATE(2025;12;31))

Here you could set the maximum date to the latest date in your sales column.

Additionally you could create a slicer that is based off of the sales data. This will only show values that exist within this table.

Thanks,

J