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?
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 )
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?
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: