Forum Discussion
Simple bidirectional filtering between bar chart and matrix not working!
Hello,
for me it assumes like a very simple problem, but I do not know, why it is not working. I want to create a bar chart and a matrix that can be filtered interactively in both directions.
I have prepared a minimum example:
this is my initial table:
| ID | Attribut 1.1 | Attribut 1.2 | Attribut 1.3 | Attribut 1.4 | Attribut 2.1 | Attribut 2.2 | Attribut 2.3 |
| A | 1 | 0 | 0 | 1 | 1 | 0 | 1 |
| B | 1 | 1 | 0 | 0 | 1 | 1 | 0 |
| C | 1 | 0 | 1 | 1 | 1 | 0 | 0 |
I have two attributes wit different characteristics. Each ID either hast this characteristic ( value 1) or not (value 0)
I want to display two bar charts, one for each attribute. those bar charts schould be interactive.
In Power Query mode i created two aditional tables by depivoting the data:
Table 1, called "1Attribut":
| ID | Attribut | Value |
| A | Attribut 1.1 | 1 |
| A | Attribut 1.2 | 0 |
| A | Attribut 1.3 | 0 |
| A | Attribut 1.4 | 1 |
| B | Attribut 1.1 | 1 |
| B | Attribut 1.2 | 1 |
| B | Attribut 1.3 | 0 |
| B | Attribut 1.4 | 0 |
| C | Attribut 1.1 | 1 |
| C | Attribut 1.2 | 0 |
| C | Attribut 1.3 | 1 |
| C | Attribut 1.4 | 1 |
and Table 2, called "2Attribut":
| ID | Attribut | Value |
| A | Attribut 2.1 | 1 |
| A | Attribut 2.2 | 0 |
| A | Attribut 2.3 | 1 |
| B | Attribut 2.1 | 1 |
| B | Attribut 2.2 | 1 |
| B | Attribut 2.3 | 0 |
| C | Attribut 2.1 | 1 |
| C | Attribut 2.2 | 0 |
| C | Attribut 2.3 | 0 |
I created a relationship between the "initial table" and "1Attribute" as well as the the "initial table" and "2Attribute". In each case with the ID as a column for the relationship.
I have a cardinality of *:1 and cross filtering "both" directions.
Know I added two bar charts. One, one for Attribut1 and one for Attribut2. Each with the Attribut value on the x-Axis and the "Value" on the y-axis. My goal is, that if i am clicking on one bar, the bars in the other diagram are filtered. Unfortunately nothing is happening if I click on a bar.
What am I doing wrong?
I already checked the "Edit Interactions". This is set up correctly. It seems like the direction is only unidirectional (I added a table with the IDs from my initial table and if i click on a value (A,B, or C) the bar charts are filtered correctly)
Thanks for your help
Ok see if this works for you. Duplicate the single table and set up th emodel as follows.
Duplicate this table
Now create two measure to act as filters for each corresponding table:
Filter Person T1 = COUNTROWS ( CALCULATETABLE ( VALUES ( 'Dim ID'[dID] ), FILTER ( 'Table 2', SUM ( 'Table 2'[Wert] ) <> 0 ) ) )Filter Person T2 = COUNTROWS ( CALCULATETABLE ( VALUES ( 'Dim ID'[dID] ), FILTER ( 'Table 1', SUM ( 'Table 1'[Wert] ) <> 0 ) ) )Add the ID column from the ID dimenion table and set up each filter with its corresponding measure in the filter pane
and this is the final result
Sure, you can download it from here:
9 Replies
- bhanu_gautam
Super User
UvonW Ensure that the relationships between your tables are correctly set up. You should have a relationship between the "initial table" and "1Attribut" as well as between the "initial table" and "2Attribut" using the ID column. The relationships should be set to "Both" for cross-filter direction.
Verify that the interactions between the visuals are correctly set. You mentioned that you have already checked this, but it’s worth double-checking.
Yes I checked that:
2Attribut
and for 1Attribut
Unfortunately it is in GermanI created a minimal example and uploaded it on weTransfer:
weTransfer_minimalexample
PaulDBrownCommunity Champion
Is there a particular reason why you are setting up the model like that?
The convention would be to have a single fact table with dimension table for ID and Attribut
Like this:
You can then set up your graphs eith as one or in two separate clustered column charts and just filter the attributs in each
Here is the link to your file with the alternative model setup:
Thank you for your answer and also the example. Unfortunately this is not what I want to achieve. Since I want to have a filtering between the bar charts as well.
Assume Attribut1 is "Food" and Attribut2 are "Drinks" and the ID are personas.
I also want to have the opportunity to filter "How many personas that like Tomato also drink Juice"In this case 0. And i want to achieve that by interactively clicking on the barchart. Including the table with the ID (A, B, C), i also want to see "Which persona drinks Juice?" bei clicking on Juice in the barchart.
- PaulDBrown
Ok see if this works for you. Duplicate the single table and set up th emodel as follows.
Duplicate this table
Now create two measure to act as filters for each corresponding table:
Filter Person T1 = COUNTROWS ( CALCULATETABLE ( VALUES ( 'Dim ID'[dID] ), FILTER ( 'Table 2', SUM ( 'Table 2'[Wert] ) <> 0 ) ) )Filter Person T2 = COUNTROWS ( CALCULATETABLE ( VALUES ( 'Dim ID'[dID] ), FILTER ( 'Table 1', SUM ( 'Table 1'[Wert] ) <> 0 ) ) )Add the ID column from the ID dimenion table and set up each filter with its corresponding measure in the filter pane
and this is the final result
