cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
zoloturu
Advisor

Complicated many-to-many relationships

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.

 

relationships.PNGmany-to-many

2 REPLIES 2
Moderator v-yuezhe-msft
Moderator

Re: Compicated many-to-many relationships

@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.
1.JPG


Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
zoloturu
Advisor

Re: Compicated many-to-many relationships

@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

PostedDateApprovedDateDepartmentPostedByApprovedBy
1-Jan-1824-Jan-18Dept 1JohnMaryna
18-Feb-1814-Mar-18Dept 1JaneMay
7-Apr-1828-Apr-18Dept 1JamesMarta
25-May-1828-Aug-18Dept 1JackobMax
12-Jul-1819-Oct-18Dept 2JoelMaria
29-Aug-1825-Sep-18Dept 2JordanMargo

 

Let me know if this not clear.

 

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 66 members 1,251 guests
Please welcome our newest community members: