Forum Discussion

Majidbhatti's avatar
Majidbhatti
Icon for Advocate IV rankAdvocate 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.

Majidbhatti's avatar
Majidbhatti
Icon for Advocate IV rankAdvocate IV
6 years ago

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's avatar
Majidbhatti
Icon for Advocate IV rankAdvocate IV
6 years ago

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's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    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.