Hi Sam,
Please on the above matter. I have 2 fact tables which are FactSalesInvoice and FactLeadMaster having relationship with DimDates. At the same time, FactSalesInvoice and FactLeadMaster also have relationship to analyse the sales status in the FactLeadmaster.
Any idea how to make the relationship active between FactLeadMaster or any DAX could help for this issue so I can have DimDates in both FactSalesInvoice and FactLeadMaster.
Thank you.
Dennis
Good Day Dennis,
Inactive relationship needs a special kind treatment to work and you can use UseRelationship Function in DAX.
Like
Example - Sum of Purchase = CALCULATE(
SUM(FactLeadMaster[ColumnToAggregateLikePurchase]),
USERELATIONSHIP(
FactLeadMaster[PurchaseDate],
DimDates[DateKey]
))
This measure calculates the sum of purchase by date in DimsDates. This is a really simple function to use.
Read the offical doc for more details: https://docs.microsoft.com/en-us/dax/userelationship-function-dax.
@Mohammed_ali is spot on about USERELATIONSHIP. I just wanted to highlight the two excellent videos below - the first focusing on USERELATIONSHIP and the second about physical (active and inactive) versus virtual relationships, that I think you’ll find useful relative to your model.
-Brian
Hi @dennistgc, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!
