Forum Discussion
Helper IIData Connection Issues when working with >2 tables
Table 1: Dax Calender
- Full Date: All Dates in Format DD,MM,YYYY (from 2017 - 2020)
- Days (Short): Days associated with all dates, in format Mon, Tue, Wed, etc
Table 2: Library Archive
- Author ID: Strings, around 5k rows
- Creation Date: Date Format DD,MM,YYYY (from 2018 - 2019)
- Interacted With = IF ( ISBLANK ( COUNTROWS ( RELATEDTABLE ( 'Interactions' ) ) ), "Not Found", "Found" )
(Interacted With is a custom Column)
Table 3: Interactions
- Review ID: Strings, 10 rows total (each containing a unique and exact copy of an Author ID cell)
- Submission Date: Dates in Format DD,MM,YYYY (from 2019 - 2019)
This is what happens:
- Create a relationship from Review ID (One) to Author ID (One):
- Interacted With works as expected. 10 "Founds" present on the table.
- Then make 2 cards on the canvas:
- Card 1: Count of Author ID (= over 5k)
- Card 2: Copy that card and Visual level filter it using Interacted With = Found
- This new card correctly outputs 10.
[Remove all Relationships]
- Create a new relationship from Full Date (One) to Creation Date (Many):
- Days (Short) slicer correctly filters the card containing Count of Author ID
- From over 5k, to i.e. 500 on all Wednesdays, etc
- Naturally, the Interacted With Visual level filtered card no longer works, without its Relationship.
- Thus, Card 2 outputs the same as Card 1.
- From over 5k, to i.e. 500 on all Wednesdays, etc
- Days (Short) slicer correctly filters the card containing Count of Author ID
[Remove all Relationships]
- Create Bothways relationships from Full Date (One) to Creation Date (Many), & also from Full Date (One) to Submission Date (Many):
- Now all three tables are linked through the Dax Calender table.
- Previously, using the Relations view, this let all of the above interactions work as described.
- Now with the Model view... I just must not understand the connection type anymore, because;
- Days (Short) still filters the card containing the count of Author ID
- Interacted With now only displays Not Found within the table. Even though there are 10 values in the other table that indeed match.
Someone, please help me! I would really like to submit this model soon, but I have no idea what's going wrong. This is how I built all of my relationships last year, and now this new Model view just won't work how I expect it to :/
Can anyone figure out what's going wrong here?
3 Replies
- MFelix
Super User
Hi StefanM ,
The new Model view didn't change anything in the way relationships work, they are just displayed in a different way, however the basic of the view that concerns the link is still the same:
(1) - Table with relation of One
(*) - Table with Relation of Many
> - Filtering from a single side view
<> - Filtering both ways
Are you sure that the previous models you made were with bothways filter?
Can you share an example of your data and expected result?
Regards,
MFelix
StefanMHiya MFelix! Ofc, no problem. I just created a mock report using some random figures following the example above for you.
The most curious note. It works when everything is a 1 < > 1 relationship.
When Library & Dax are 1 <>1, it all works fine.So perhaps it's the custom column that isn't working then?
- StefanM
I was so sure this always worked for me in the past, but I fully take MFelixs word as gospel when he says nothing has changed re: relationships. So now I need to find some way to cross filter these tables, so that the values of each is reflected in any other.
I am thinking something like creating a live modular key that updates as the spreadsheet is populated.
- Custom Column =
- 1 (aka True)
- Data in this table, On this row, In column 123,
- Matches data in table X, in any row in Column 456.
- 0 (aka false)
- Data in this table, On this row, In column 123,
- Does not match data in table X, in any row in Column 456.
- 1 (aka True)
The previous system linked in the OP works for me with a direct relationship between two tables.
But it does not work with a relationship through another table.
Can you guys think of anything else?
- Custom Column =
