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.

v-juanli-msft's avatar
v-juanli-msft
Community Support
6 years ago

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's avatar
    Majidbhatti
    Advocate 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
      Advocate 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
        Community 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.