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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Matty
Helper II
Helper II

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

3 REPLIES 3
v-huizhn-msft
Employee
Employee

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
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
opticshrew
Resolver II
Resolver II

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.