cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dombarg Regular Visitor
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:

 many.JPG

 

 

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

how_to_make_inter_Tables_Relation_from_DAX_Output

 

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.
dombarg Regular Visitor
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?

 

dombarg Regular Visitor
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:problem.JPG

 

 

 

dombarg Regular Visitor
Regular Visitor

Re: how to make inter Tables Relation from DAX Output

any idea?