Forum Discussion

dombarg's avatar
dombarg
Icon for Helper II rankHelper II
8 years ago

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 t...
Need Help
v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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 )

how_to_make_inter_Tables_Relation_from_DAX_Output

 

Best Regards,

Dale

  • dombarg's avatar
    dombarg
    Icon for Helper II rankHelper II
    8 years ago

    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?

     

  • dombarg's avatar
    dombarg
    Icon for Helper II rankHelper II
    8 years ago

    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: