how to make inter Tables Relation from DAX Output

[dombarg]

hello.

i amd dealing with many to many relation in my model.

there are many Tables i need to divide them to each other based on single code.

how can i write DAX  with "Group By" and etc in a way that we create One Table with DAX and then relate them to another Many Table?

have a look please at this example:

 

in above pic,i need to create a relationship between two tabels.left one shuold be the "One Table" with account filter = 112 and then be relate with the right table .by ID column.i wo'nt to use Bridge Table.

i Think i can do this by such DAX:

ertebat =
DIVIDE (
    CALCULATE (
        SUM (left[amount] ),
        'left[account] = 112,
        GROUPBY (
            'left',
            'left'[year],
            'left'[ID],
            'left'[city],
            'left'[amount]
        ),
        CROSSFILTER ( 'right'[ID ], 'left'[ID], BOTH )
    ),
    CALCULATE (
       SUM (right[amount] ),
       'right[account] = 8520,
)

what is wrong with that?is it true?is there any way?

[dombarg]

any idea?

[v-jiascu-msft]

Hi @dombarg,

Check out the demo in the attachment, please. Maybe the following measure could help.

Measure =
VAR leftIDs =
    CALCULATETABLE ( VALUES ( 'left'[ID] ) )
VAR leftTotal =
    SUM ( 'left'[amount] )
VAR rightTotal =
    CALCULATE ( SUM ( 'right'[amount] ), 'right'[ID] IN leftIDs )
RETURN
    DIVIDE ( leftTotal, rightTotal, 0 )

Best Regards,

Dale

[dombarg]

also, please have a look at the following measure I used in your sample :

Measure 2 = 
DIVIDE (
    CALCULATE ( SUM ( 'left'[amount] ), 'left'[account] = 112 ),
    CALCULATE ( SUM ( 'right'[amount] ), 'right'[account] = 8520 )
)

it returns wrong asnwer:

[dombarg]

thank u vaery much.it is awesome.

but a few question:

1-How you deal with this many to many ID columns? both ID columns in both tables have many value which are not uniqe.

2-i want to first : filter left table based on  acount and then relate its ID to the right table.(All with DAX).does your measres do such function??would you please explain some more?