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
Aduhan
Employee
Employee

Issue with filtering (when having many to many relationships established in Data Model)

Description:

Table in Data Model:

Table 1: ‘State- CA’

Table 2: ‘State WA’

Table3:  ‘State OR’

Table 4: ‘Product Info’

Sample Data (Columns) in Table: State- CA, State- WA, State- OR.

  • Sales Amount- $500
  • Product Category – Furniture/Automobile
  • Product ID- 1234567
  • Product Subcategory – Chair, Table/ Bike, Car

Sample Data (columns) in Table: Product Info (Contains distinct combination of all column’s values)

  • Product Category - Furniture/Automobile
  • Product Subcategory – Chair, Table/ Bike, Car
  • Product ID- 1234567

Visuals

  • Visual 1: Pie chart taking values from Table 1: ‘State- CA’
  • Visual 2: Pie chart taking values from Table 2: ‘State- WA’
  • Visual 3: Pie chart taking values from Table 3: ‘State- OR’

 

 

All State Tables are connected with Table 4: ‘Product Info’ on Column:’ Product Category’ (using many to many relationship)

I am using all slicer values (Product Category, Product Parts) from Table 4: Product Info

When I change the values of slicer 1:  Product Category, all visuals respond. Having value selected in slicer 1: Product Category when I select the corresponding values from slicer 2: Product Parts, only first visual (it was first table that was connected with the Table 4: ‘Product Info’ ) responds and other two visuals (taking values from State-WA and State- OR) does not respond.

After observing the behavior, I switched the joining condition in many to many relationship from using column: Product Category to Product ID. After switching, I see that all the visuals are responding to all the slicers.

 

Question:

Why all the visuals are responding to the new joining condition: 'Product ID' and only one visual responds to earlier joining condition: 'Product Category'?

 

** In example above- Data has been very closely mimiced to the original due to Privacy

3 REPLIES 3
amitchandak
Super User
Super User

@Aduhan , Tables should be joined on product ID, if that is unique. Else Create a Unique column by concatenating  and join on that

Key = [Product Id] & [Product Category] & [Product Sub Category]

 

@amitchandak thanks for answering the question. This example would explain that question in a better way (I hope so).

 

Scenario 1:

Table in Data Model:

  1. Table: ‘VN2’
  2. Table: ‘Info’
  3. Table:  ‘Bridge’ (Unique values of ‘Project’ from both tables)

Filters (Consuming values from Table: Info):

  1. Project
  2. ImageName
  3. HWBuild

 

Visual (Table and Pie Chart) is consuming data from Table: VN2.

As seen in picture attached, Relationships are:

  1. Many:1 (Table:‘VN2’ to Table: ‘Bridge’)- Cross directional Filter set to Both
  2. Many:1 (Table:‘Info’ to Table: ‘Bridge’)- Cross directional Filter set to Both

 

Scenario 2:

Table in Data Model:

  1. Table: ‘VN2’
  2. Table: ‘Info’
  3. Table:  ‘Bridge’ (Unique values of ‘SerialNumber’ from both tables)

Filters (Consuming values from Table: Info):

  1. Project
  2. ImageName
  3. HWBuild

 

Visual (Table and Pie Chart) is consuming data from Table: VN2.

 

As seen in picture attached, Relationships are:

  1. Many:1 (Table:‘VN2’ to Table: ‘Bridge’)- Cross directional Filter set to Both
  2. Many:1 (Table:‘Info’ to Table: ‘Bridge’)- Cross directional Filter set to Both

 

Question:

Why the visuals (Table and Pie Chart) are responding to filter values when joining column used in Data Model is ‘SerialNumber' (Scenario-2) and does not respond to filter values when joining column used in Data Model is ‘Project' (Scenario-2)? Although in both cases, Bridge table has unique values from both the tables! 

 

Fact: SerialNumber is identifying the Product in more unique way. Project may have many SerialNumbers under it. But still, filters should work. Right?

 Data ModelData Model

@amitchandak 

parry2k
Super User
Super User

@Aduhan I would recommend to paste sample data in a table format, relationship diagram and expected output or explain what is not working, it is not clear what you are trying to do

 

Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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.