Forum Discussion

Majidbhatti's avatar
Majidbhatti
Advocate IV
6 years ago
Solved

Multiple table relationships

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 ...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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's avatar
MartynRamsden
Solution Sage
6 years ago

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