cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
strainer85
New Member

Using 2 slicers on 1 report for multiple tables

I am working on a report in which the tables are relational and I would like to have 2 slicers on the same report.  I have 4 queries that contain different infromation each of which have a Month and CustomerID field.  I have created 2 tables, 1 has the 12 months of the year (MONTHS) and the other has a cross reference of CustomerID to CustomerName (CUSTOMER).  I am able to create relationships from the MONTH table to the 4 tables joining the Month fields together.  However when I try to join the CUSTOMER table to the 4 tables on the CustomerID field, I am only able to have 1 active with the other 3 inactive.  I get the error message "You can't create a direct active relationship between TABLE2 and CUSTOMER  because that would introduce  ambiguity between tables MONTHS and CUSTOMER.  I have put some fake data below to outline the structure a bit.

 

TABLE1- Transaction Information (10 columns), Month, CustomerID (Multiple records per CustomerID)

TABLE2- Transaction Information (15 columns), Month, CustomerID (Multiple records per CustomerID)

TABLE3- Transaction Information (12 columns), Month, CustomerID (Multiple records per CustomerID)

TABLE4- Transaction Information (8 columns), Month, CustomerID (Multiple records per CustomerID)

 

MONTH- Month

CUSTOMER- CustomerID, CustomerName

 

I would like a user to select a Month then select a CustomerName and have select fields from each of the 4 tables returned returned based on those critera.  Currently the values from each table return properly when a month is selected (all active relationship), however when a CustomerName is selected I can only have 1 active relationship.

 

I have tried several different methodlogies, however none have worked!  Any assistance would be creatly appreciated.  

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-haibl-msft
Microsoft

Re: Using 2 slicers on 1 report for multiple tables

@strainer85

 

Please try to change the Cross filter direction to Single as ankitpatira said.

Using 2 slicers on 1 report for multiple tables_1.jpg

 

Best Regards,

Herbert

View solution in original post

3 REPLIES 3
ankitpatira Community Champion
Community Champion

Re: Using 2 slicers on 1 report for multiple tables

@strainer85 have you ensured that all your active relationships have cross-filter direction as single. i think if you change cross-filter direction on active relations from Both to Single then you will be able to create other relationships active.

Microsoft v-haibl-msft
Microsoft

Re: Using 2 slicers on 1 report for multiple tables

@strainer85

 

Please try to change the Cross filter direction to Single as ankitpatira said.

Using 2 slicers on 1 report for multiple tables_1.jpg

 

Best Regards,

Herbert

View solution in original post

strainer85
New Member

Re: Using 2 slicers on 1 report for multiple tables

That resolved my issue... thank-you to everyone who responded I really appreciate it!

 

 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors