cancel
Showing results for
Did you mean:
Regular Visitor

## how to make inter Tables Relation from DAX Output

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?

4 REPLIES 4
Community Support Team

## Re: how to make inter Tables Relation from DAX Output

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

## Re: how to make inter Tables Relation from DAX Output

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?

Regular Visitor

## Re: how to make inter Tables Relation from DAX Output

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:

Regular Visitor

any idea?