cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

## Mapping between 2 table with Many to many relationship in POWERBI

hI

I have 2 table 1 and 2 as belows. They are connecting with each other by many-to-many relationship

So, I wanna create a Output table with logic:

Mapping table 1 and 2 , in which the 1st posting date will be the 1st Running date.

Is there any way to do it with Dax measure?

1 ACCEPTED SOLUTION
Community Support

Hi @ngocnguyen ,

I created a sample pbix file(see the attachment), please check if that is what you want. You can create two measures as below to get it:

``````NRun date =
VAR _dealer =
SELECTEDVALUE ( 'Table 1'[Dealer] )
VAR _postdate =
SELECTEDVALUE ( 'Table 1'[Post date] )
VAR _prepdate =
CALCULATE (
MAX ( 'Table 1'[Post date] ),
FILTER ( ALLSELECTED ( 'Table 1' ), 'Table 1'[Dealer]=_dealer&&'Table 1'[Post date] <_postdate )
)
RETURN
CALCULATE (
MIN ( 'Table 2'[Run date] ),
FILTER (
'Table 2',
'Table 2'[Run date] < _postdate
&& 'Table 2'[Run date] > _prepdate
)
)``````
``````NAmount =
CALCULATE (
SUM ( 'Table 2'[Amount] ),
FILTER ( 'Table 2', 'Table 2'[Run date] = [NRun date] )
)``````

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Super User

Hi,

Write this calculated column formula in Table 2

Post date = calculate(min('Table 1'[Post date]),filter('Table 1','Table 1'[Dealer]=earlier('Table 2'[Dealer])&&'Table 1'[Post date]>earlier('Table 2'[Run date])))

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

Hi @ngocnguyen ,

I created a sample pbix file(see the attachment), please check if that is what you want. You can create two measures as below to get it:

``````NRun date =
VAR _dealer =
SELECTEDVALUE ( 'Table 1'[Dealer] )
VAR _postdate =
SELECTEDVALUE ( 'Table 1'[Post date] )
VAR _prepdate =
CALCULATE (
MAX ( 'Table 1'[Post date] ),
FILTER ( ALLSELECTED ( 'Table 1' ), 'Table 1'[Dealer]=_dealer&&'Table 1'[Post date] <_postdate )
)
RETURN
CALCULATE (
MIN ( 'Table 2'[Run date] ),
FILTER (
'Table 2',
'Table 2'[Run date] < _postdate
&& 'Table 2'[Run date] > _prepdate
)
)``````
``````NAmount =
CALCULATE (
SUM ( 'Table 2'[Amount] ),
FILTER ( 'Table 2', 'Table 2'[Run date] = [NRun date] )
)``````

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.