Hey guys
I understand the function above and the difference between using the treatas and userelationship.
My model is a litle messy and producing wrong results. My model has 3 fact tables to connect to dates table. I’ve decided use only USERELATIONSHIP function and put all 3 connections as inactive relationship. So, now EVERY “simple” measure that i have to do, like SUM, i will have to do something like this:
T Prev Time (hr) =
CALCULATE (
SUM ( ‘Tab Project’[Preview Time (Hr)] ),
USERELATIONSHIP ( ‘Tab Dates’[Date], ‘Tab Project’[Date] )
)
First question : how about this measure ? I mean, measures that use others measures that already have a RELATIONSHIP function in it?
% Time Completed =
DIVIDE ( [Ts Hours], [T Prev Time (hr)], 0 )
So if understand correctly than are you trying to say that you’ve to mention USERELATIONSHIP() or TREATAS() only once and in the subsequent measures you don’t have to use this function. If that’s the case, then you’re definitely on the right path.
This functions are only required to b used while writing the primary/base measures once you start building the secondary measures i.e. Measure Branching we’ll not be required to use this function again and again because the base/primary measure already contains this function.
So let’s for example you’ve 3 date fields in the Sales Fact Table i.e. Order Date, Shipping Date and Delivery Date. So in order to calculate the Sales Figure by Order Date we’ll write or measure like this -
Total Sales by Order Date =
CALCULATE( SUMX(
SalesFactTable , SalesFactTable[Price] * SalesFactTable[Quantity] ) ,
USERELATIONSHIP( SalesFactTable[Date] , DatesTable[Date] ) )
Now, based on this measure you want to calculate the Sales Figure for Last Year by Order Date. So now, we need not use the USERELATIONSHIP() function again to calculate the Total Sales Last Year by Order Date. We’ll simply write the formula as follows -
Total Sales LY by Order Date =
CALCULATE( [Total Sales by Order Date] ,
DATEADD( Dates , -1 , YEAR ) )
Conclusion: Whenever we write the base/primary measures only at that time USERELATIONSHIP() or TREATAS() function will be put into use. Once we start building subsequent or secondary measures the measure branching technique will automatically incorporate those functions and it will take care of.
