cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Microsoft
Microsoft

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
Super User IV
Super User IV

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

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@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 

Super User III
Super User III

@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






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
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors