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

 

Need Help

4 Replies

  • 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: