Forum Discussion
Help with table relationship
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.
Hi Libin7963 -In Power BI, if you want to filter Fact Table 2 (FT2) by a date slicer from the Date Table without a direct relationship between them, you can use DAX to create a measure that respects the slicer selection
TREATAS can apply the filter from the Date Table to Fact Table 2 without needing a direct relationship.
Total Amount Received =
CALCULATE(
SUM('Fact Table 2'[Amount]),
TREATAS(
VALUES('Date Table'[Date]),
'Fact Table 2'[Date Received]
)
)
Use this measure in your visuals, and it will respond to the date slicer based on Date Table.
another way is using bridge table creation and also you can try with USERRELATIONSHIP() function too..
Hope it helps and let me know if any
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.
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.
