Forum Discussion

Libin7963's avatar
Libin7963
Helper II
1 year ago
Solved

Help with table relationship

My relationship looks as below I want to calculate the sum of amount in "Amount" column of Fact table 2 using the "Date Received" in  Fact table 2 using "Date" from Date table in slicer. Since there ...
Need Help
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    Hi Libin7963 - Good to know that, you can use a combination of CALCULATE and INTERSECT or filtering functions that ensure only matching records between the two fact tables are included.

     

    SumAmountRelatedCasesFT2 =
    CALCULATE(
    SUM('Fact Table 2'[Amount]),
    FILTER(
    'Fact Table 2',
    NOT(ISBLANK(LOOKUPVALUE(
    'Fact Table 1'[KeyColumn],
    'Fact Table 1'[KeyColumn], 'Fact Table 2'[KeyColumn]
    )))
    ),
    USERELATIONSHIP('Date'[Date], 'Fact Table 2'[Date Received])
    )

     

    Hope this helps.

Libin7963's avatar
Libin7963
Helper II
1 year ago

thanks, it worked but but I want to sum(Amount) for only cases where FT 1 and FT 2 are related or FT2 has a related case in FT1.  

rajendraongole1's avatar
rajendraongole1
Super User
1 year ago

Hi Libin7963 - Good to know that, you can use a combination of CALCULATE and INTERSECT or filtering functions that ensure only matching records between the two fact tables are included.

 

SumAmountRelatedCasesFT2 =
CALCULATE(
SUM('Fact Table 2'[Amount]),
FILTER(
'Fact Table 2',
NOT(ISBLANK(LOOKUPVALUE(
'Fact Table 1'[KeyColumn],
'Fact Table 1'[KeyColumn], 'Fact Table 2'[KeyColumn]
)))
),
USERELATIONSHIP('Date'[Date], 'Fact Table 2'[Date Received])
)

 

Hope this helps.