Forum Discussion

Matty's avatar
Matty
Helper II
9 years ago

Using a slicer based on a joined table...

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

Need Help

3 Replies

  • opticshrew's avatar
    opticshrew
    Resolver II
    9 years ago

    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

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

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

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    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