Multiple Date tables but only use 1 filter in filter pane

[elenton]

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?

[elenton · Accepted solution]

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 🙂

[amitchandak]

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

[Anonymous]

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]

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. 

[Anonymous]

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]

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?

[Anonymous]

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