Complicated many-to-many relationships

[zoloturu]

Hi All,

Could anyone suggest an idea how to build below relationships. 

I have two data tables - vwJournal and Details. 

Both have 5 similar fields: 

* Posted date time

* Approved date

* Department

* PostedBy

* ApprovedBy

I need to have five different slicers on report per each of these five fields. All these slicers should interact with each of two data tables (each of two are just different aggregations of initial data).

Example,

I filter on Posted Date and then it affects vwJournal and Details. And other 4 filters as well. For instance filter PostedBy should see only values which are related to dates I've selected in Posted Date.

Here is working diagram below. But it is not filtering me PostedBy based on PostedDate.

many-to-many

[v-yuezhe-msft]

@zoloturu,

Do you mean that when you select value in PostBy slicer, the values in Posted date time slicer is not filterd? If so, this is because that the cross filter direction of relationship between vwJournal and PostDate table is single.

When you  set cross filter direction of relationship between vwJournal and PostDate table from  “Single”  to “Both”, Posted date time slicer will be filtered.

However, once you change the cross filter direction for vwJournal and PostDate, you are not able to change cross filter direction of relationship among other tables as you will get the following warning.


Regards,
Lydia

[zoloturu]

@v-yuezhe-msft,

I understand that. I'm flexible in solution to achieve below two options:

1) I need to have 5 filters which will automatically apply to each of two independent sources

2) When I select date period in PostedDate then PostedBy automatically fit to list of people who was on that period. 

Example about #2,

If I filter on PostedDate between Jan and Apr then:

* ApprovedDate should show  24-Jan-18, 14-Mar-18, 28-Apr-18

* Department should show only Dept 1

* PostedBy should show only John, Jane and James

* ApprovedBy should show only Maryna, May, Marta

PostedDate	ApprovedDate	Department	PostedBy	ApprovedBy
1-Jan-18	24-Jan-18	Dept 1	John	Maryna
18-Feb-18	14-Mar-18	Dept 1	Jane	May
7-Apr-18	28-Apr-18	Dept 1	James	Marta
25-May-18	28-Aug-18	Dept 1	Jackob	Max
12-Jul-18	19-Oct-18	Dept 2	Joel	Maria
29-Aug-18	25-Sep-18	Dept 2	Jordan	Margo

Let me know if this not clear.