Multiple table relationships

[Majidbhatti]

Hi,

I have 3 tables and relations as shown below. When i do the visuals by adding all 3 tables on the page; 

Upon selecting COMPANY, DEPT and TRANS are filtered but upon selecting DEPT, it does not filter TRANS. I have tried all the possible relationship types but none seems to be working. Please help

[v-juanli-msft]

Hi @Majidbhatti 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie

[v-juanli-msft]

Hi @Majidbhatti 

I can reproduce the error.

If you do some aggregation on some fields, it should work.

If you don't like any aggregation, you just want to show all values of some columns from three tables,

you could create a new table.

Table =
CROSSJOIN (
    company,
    SELECTCOLUMNS ( dept, "id1", [area id], "code", [code] ),
    SELECTCOLUMNS ( trans, "id2", [area id], "group code", [group code] )
)

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Majidbhatti]

@parry2k I tired the way u mentioned, but it works only on single filter selection. If i select Company it will filter other tables and similarly if i only select DEPT then other tables get filtered. My visual has Company totals and then Dept totals. Company filter should slice Dept and Dept should slice Trans.

@MartynRamsden I am inclined to see if combining Company and Dept would work. Will let you know.

@v-juanli-msft You solution works but since i am not using measures and want to use the visual filtering option. this is not working for me.

[Majidbhatti]

@v-juanli-msft I have changed the data in the sample file under your working. the data iin your file was not in the way it should be so i have modified the file explaining my problem. the total are all wrong and filtering is not working.

You can download the .pbix file here

[v-juanli-msft · Accepted solution]

Hi @Majidbhatti 

First, create the relationship as below:

Since the total for all [Amount] in the [trans] table is 302, so the measure grand total should be 302,

Create measures

Measure = SUM(trans[Amount])

Measure 2 =
IF (
    ISINSCOPE ( company[name] ),
    IF ( ISINSCOPE ( dept[desc] ), [Measure], SUMX ( dept, [Measure] ) ),
    SUMX ( company, [Measure] )
)

If you want the grand total to be the sum of all the subtotals, you could try the measure below

Measure 3 =
VAR c =
    CALCULATE ( DISTINCTCOUNT ( dept[desc] ), ALLSELECTED ( dept ) )
RETURN
    IF (
        ISINSCOPE ( company[name] ),
        IF ( ISINSCOPE ( dept[desc] ), [Measure], SUMX ( dept, [Measure] ) ),
        c * SUMX ( company, [Measure] )
    )

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MartynRamsden]

Hi @Majidbhatti 

It's almost always best to avoid bi-directional relationships in your model.

If it's possible, I would recommend that you denormalize the Department table into the Company table.
That way, you'll have a much simpler data model (two tables and a single many to one relationship) and your filters should work perfectly.

If this isn't an option, your next best solution is to use CROSSFILTER in your measure to temporarily enable bi-directional filtering for the life of the calculation.

Best regards,
Martyn

[parry2k]

@Majidbhatti usually you write a measure for crossfilter but quickly change cross filter between Departmet and COmpany to both direction, to do so, clicking on relationship line between dept and company and lower right corner change direction to both and this will do it.