cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
elenton Regular Visitor
Regular Visitor

Multiple Date tables but only use 1 filter in filter pane

I have 2 date tables that each link to a separate table. My issue is that i want to be able to show data from both these tables by date and have a date in the filter pane. The issue im having is i cant use the date from either of the 2 dates tables as it wont filter the data in the other correct.

 

Below is how i ideally would like the structure to work so i could pick a date from _date All but as i cant have 2 active links this isnt an option.

Data Links:

Orders -> Date Orders (links on Order Date)

Sales - > date Sales (Links on Sale Closed Date)

Sales - > Orders (Links on OrderID)

 

I could have orders in the orders table not in the sales table so dont want to just link from Orders to Sales.

 

Is there a way to achieve what i would like or will i have to rethink the filters maybe just have to have multiple date filters to select from?

 

Capture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
elenton Regular Visitor
Regular Visitor

Re: Multiple Date tables but only use 1 filter in filter pane

So i have managed to get this working now. I ended making the link between orders and sales inactive and using the date hierarchy structure in the picture. I figures if I need to reference any orders that link directly to sales I can then use the USERELATIONSHIP in the measure. Thanks for all the help though Smiley Happy

View solution in original post

7 REPLIES 7
AWA Regular Visitor
Regular Visitor

Re: Multiple Date tables but only use 1 filter in filter pane

Hi

If I have understood your question correctly, you basically want to use a date column from one of the tables as a slicer, and you want this slicer to filter all the tables?

In that case, this is definitely possible as long as the tables are joined with a relationship (which is the case here). Head over to Manage Relationships and edit the relationships. Here you can adjust the cross filter direction to your needs.

Does this help?

Kind regards

amitchandak Super Contributor
Super Contributor

Re: Multiple Date tables but only use 1 filter in filter pane

Can you example what is needed? Also, one of you join is inactive, seems like sales might not filter.

 

Only Suggestion: you can connect sales and order directly to the date/time(Date all) dimension. That should filter both

In case you need a filter on sales date or order date you can have slicer on those they will not filter each other.

 

Date All - filter Both

Sales date - Filter Sales

Order date - Filter Orders

AWA Regular Visitor
Regular Visitor

Re: Multiple Date tables but only use 1 filter in filter pane

Hi

You don't need all the date tables. As long as your Orders and Sales tables are connected, and one of them contains a date, then you can use that date to filter both tables. So you don't need the following tables: Date All, Date Orders and Dates Sales
If neither your Orders or Sales table contain a date, then you can keep one of the tables Date Orders or Dates Sales, and use that as a slicer.

My point is, as long as your Orders and Sales tables are connected, you can use either of the dates to filter both of them, depending on how you have defined the Cross Filter Direction. So for example, if you want to use your date from Orders table as a slicer, then go to Manage Relationships -> Edit Relationship -> Cross filter direction then choose Single (Orders filters Sales).

Does this make sense?

elenton Regular Visitor
Regular Visitor

Re: Multiple Date tables but only use 1 filter in filter pane

My orders and sales table have to link on the order id though not on the date? So If have the flow as

Date -> Orders -> Sales

 

Then this will filter the data from orders correct, but then dosent know what date to use in the sales table. 

AWA Regular Visitor
Regular Visitor

Re: Multiple Date tables but only use 1 filter in filter pane

Hi

It is fine that they are linked on order_id, they do not have to be linked on date. Here is the logic:

- Sales and Orders tables are connected through Order_id
- You filter some Order Dates, so that the Orders table will be filtered

- Because the Order table is connected to the Sales table, the Order table will filter the Sales table to only show the rows that it finds a match for, and seeing that you have filtered the Orders tables, only Sales records with that Order date will show aswell.

 

elenton Regular Visitor
Regular Visitor

Re: Multiple Date tables but only use 1 filter in filter pane

Ok but wouldnt this filter out sales if the orer had been placed earlier than the date i was looking for?

 

What i want to be able to see if all orders based on order date, and then all sales on sales date. Some sales could have been completed weeks after the inital order so filtering on the date in the orders table wouldnt then pick up this sale if im understanding correct how you are suggesting linking the data?

elenton Regular Visitor
Regular Visitor

Re: Multiple Date tables but only use 1 filter in filter pane

So i have managed to get this working now. I ended making the link between orders and sales inactive and using the date hierarchy structure in the picture. I figures if I need to reference any orders that link directly to sales I can then use the USERELATIONSHIP in the measure. Thanks for all the help though Smiley Happy

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 124 members 2,618 guests
Please welcome our newest community members: