Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
any idea?
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
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:
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |