3 tables with 2 bridge/map tables, How to display all 3 tables in 1 visual

[vw_golf_mk3]

hello @jdbuchanan71 and other PowerBiers,

I am having a model shown as below. 

With the help of existing 2 mapping tables, T1_id and T2_id can be displayed in 1 visual, T2_id and T3_id can be displayed in another visual. They both filter on each other's selection.

The challenge here is how can I put T1_id, T2_id and T3_id all in 1 visual.

Attached is dummy data:

T1_id

T1_001

T1_002

T1_003

T1_004

T2_id

T2_001

T2_002

T2_003

T2_004

T3_id

T3_001

T3_002

T3_003

T3_004

T1_id      T2_id

T1_001	T2_001
T1_001	T2_002
T1_001	T2_003
T1_004	T2_004
T1_002	T2_001

T2_id      T3_id

T2_001	T3_001
T2_002	T3_002
T2_002	T3_003
T2_003	T4_004
T2_001	T4_004

Thanks in advance,

Brian

[TomMartens · Accepted solution]

Hey,

I call the tables T1 and T3 remote tables as they are not connected by a "fact" table (this will not change if the filter direction will be changed to both).
In contrast to this I call the T1 and T2 "these are tabels are neighboring", the "hood" is given by the table "B1_2".

So if you have a simple measure like this:

Measure = 
COUNTROWS('T2')

It's possible to use columns from remote tables in the same visual:

As you can see this in a way "multiplies" the possible combinations.

This means

• the measure has to be added to the visual to establish some kind of relationship (to avoid the error, it's necessary to add the measure to the visual before a remote column is added, to remember this can become difficult for the occasional user)
• it can become difficult to write a measure that returns a reasonable value and otherwise BLANK()

Hopefully this provides some additional insights.

Regards,

Tom

[vw_golf_mk3 · Accepted solution]

Now, after problem being solved. I found a similar pattern from stackflow in case any one is interested.

https://stackoverflow.com/questions/43439014/erd-3-tables-in-1-relationship

[vw_golf_mk3]

@TomMartens 

I have to say your reply does give me some insights. Although it is adding in the measure brings t1 t2 and t3 illuminated, all combinations do not comply with provided mapping/bridge tables. 

Your print screen gave me an idea of creating a full outer join of b1_2 and b2_3 - b1_2_3, entailing a full mappping as shown below.

And finally, with the help of b1_2_3, t1 t2 and t3 can be brought into one table preserving provided mappings as shown below. 

* some new lines have been added for testing purposes.

[TomMartens · Accepted solution]

Hey,

I call the tables T1 and T3 remote tables as they are not connected by a "fact" table (this will not change if the filter direction will be changed to both).
In contrast to this I call the T1 and T2 "these are tabels are neighboring", the "hood" is given by the table "B1_2".

So if you have a simple measure like this:

Measure = 
COUNTROWS('T2')

It's possible to use columns from remote tables in the same visual:

As you can see this in a way "multiplies" the possible combinations.

This means

• the measure has to be added to the visual to establish some kind of relationship (to avoid the error, it's necessary to add the measure to the visual before a remote column is added, to remember this can become difficult for the occasional user)
• it can become difficult to write a measure that returns a reasonable value and otherwise BLANK()

Hopefully this provides some additional insights.

Regards,

Tom

[Ashish_Mathur]

Hi,

In the Query Editor, append Table1,3 and 5.

[vw_golf_mk3]

Hi,@Ashish_Mathur 

Apending all 3 tables looks like above print screen. However, relationship between table 1 and table 2 or table 2 and table three are not preserved. 

Currently, the relationship looks like this. 

 

To show associated entities to table2, visals have to be separated into 2 as shown below.

To re-iterate the challenge here is table visual does not render properly when T1, T2 and T3 id are brought into 1 visual. Error is shown below.

Hope this clarifies the issue.

[tex628]

You need to apply crossfilter direction both to all tables. So that they filter all the way throug.

[vw_golf_mk3]

Unfortunately, having filtering propogation set to both does not solve this issue.