Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
v-haibl-msft
Employee
Employee

@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
v-haibl-msft
Employee
Employee

@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

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

 

 

ankitpatira
Community Champion
Community Champion

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.