cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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.

Top Solution Authors
Top Kudoed Authors