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
bikerboy28
New Member

using multiple slicers for multiple visuals

Hi Community, I desparately need your help.

Here's what I have and what I'm trying to do (but can't figure out):

  • 2 visuals (line charts)
  • 2 slicers to filter the line charts based on 2 criterias - dealer & condition

bikerboy28_2-1592977780918.png

 

Here's my problem:

When using the slicer titled "Dealer", both line charts will correctly switch between Bob & Joe to correctly toggle between the corresponding dealers.  The slicer titled "Condition" will NOT do the same.  When I select any 1 of the 4 variables, only the "Units" line chart (top chart) correctly changes and it has no effect on the "Price" line chart (bottom chart).

bikerboy28_4-1592978431298.png

 

 

Here's my data and relationship setup:

I have 2 tables, each with 4 columns.  3 out of the 4 columns are identical.  The difference is, 1 table is showing unit sales while the other has unit price as shown below.  As you can also see, I've also established a relationship between the 2 tables via the column labeled "Dealer".  

bikerboy28_0-1592977406614.png  bikerboy28_1-1592977545642.png

 

Based on google and BI forum searches, I've tried creating a new table called "top" as you can see above that contains the "dealer" & "condition" columns but as you can see, I'm still only able to create 1 active relationship only.  I've also tried creating a new column using the concatenate function but that didn't work either.  Of course, I won't rule out that I very well could have been doing these incorrectly.

 

bikerboy28_3-1592978304092.png

 

How can I use the 2 slicers I have in my report to function correctly with the 2 line charts???????

Thank you in advance.

 

 

 

4 REPLIES 4
v-alq-msft
Community Support
Community Support

Hi, @bikerboy28 

 

We suggest you use a star schema like below.

a1.PNG

 

If you have a table pattern with loops like below. Cross filtering can create an ambiguous set of relationships. For instance, if you sum up a field from TableX and then choose to filter by a field on TableY, then it’s not clear how the filter should travel, through the top table or the bottom table.

a2.PNG

 

 

We don't recommend you relate many-to-many dimension-type tables directly. When you have a many-to-many relationship between dimension-type tables, we provide the following guidance:

  • Add each many-to-many related entity as a model table, ensuring it has a unique identifier (ID) column
  • Add a bridging table to store associated entities
  • Create one-to-many relationships between the three tables
  • Configure one bi-directional relationship to allow filter propagation to continue to the fact-type tables
  • When it isn't appropriate to have missing ID values, set the Is Nullable property of ID columns to FALSE—data refresh will then fail if missing values are sourced
  • Hide the bridging table (unless it contains additional columns or measures required for reporting)

 

For further information, you may refer to Create and manage relationships in Power BI Desktop and Many-to-many relationship guidance .

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

amitchandak
Super User
Super User

@bikerboy28 , You join to have a problem. Make them a single direction and try.

If possible try to avoid one of the two joins top to price or sales to price

@amitchandak i've tried single joins but it doesn't work.

Hello

the idea of the data model is to try to make what you want to measure (fact table or fact table) surrounded by the attributes of the objects that have the measure (dimension table) and in or possible the dimension table only by pointing in direction to the fact table.

In your model it has address on both sides, and from what you review the PBI does not let you use only one address. This is generally because there is no single union (a fact table field equal to the dimension table).

I give you an example, here I have 2 tables, that to relate them you must not only use the sku or the center, but both concatenated, otherwise the combinations are multiplied

Captura.PNG

After concatenation I perform the union by this new field. and I can already get an address in the relationship, because the union field has no repeat rows. If you joined it only by sku (assuming that the sku 1 exists in center A and B) then it could not be related from 1 to 1, but from * to * (many to a lot) because row values are repeated by the union field.

Seeing your model may be the problem, whether you don't have unique values for the union field so the relationship from many to many is the only viable one.

You tell me and we check.

Best regards.

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.