Forum Discussion
Helper IVMapping 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?
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
2 Replies
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
- Ashish_Mathur
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.
