Forum Discussion
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 is no direct relationship from "Date Table" to FT 2 it is not respecting the received date in FT2. Currenlty the date slicer is slicing by the date in FT 1 where it is related to. Can anyone advise, thanks
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.
4 Replies
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.
Hi Libin7963 ,
May I ask if you have gotten this issue resolved?
If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster .
Best Regards,
Dengliang Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
