Using USERELATIONSHIP function in Measures and Calculated Columns #dax #powerpivot #ssashttp://sqlblog.com/blogs/marco_russo/archive/2013/10/01/using-userelationship-function-in-measures-and-calculated-columns-dax-powerpivot-ssas.aspxYou can use the USERELATIONSHIP function in DAX to apply a non-active relationship in a particular DAX calculation. This approach is usually simple in a measure (just use USERELATIOSHIP in one of the filter arguments of CALCULATE) but as soon as you tryenCommunityServer 2.1 SP2 (Build: 61129.1)re: Using USERELATIONSHIP function in Measures and Calculated Columns #dax #powerpivot #ssashttp://sqlblog.com/blogs/marco_russo/archive/2013/10/01/using-userelationship-function-in-measures-and-calculated-columns-dax-powerpivot-ssas.aspx#51786Mon, 18 Nov 2013 09:33:12 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:51786Tommy<p>Hi Marco, </p>
<p>Just wonder why your formula mentioned in the "Userelationship in Calculated Column" article, doesn't seem to facing the same issue as mentioned in <a rel="nofollow" target="_new" href="http://javierguillen.wordpress.com/2012/03/05/userelationship-and-direction-of-context-propagation/">http://javierguillen.wordpress.com/2012/03/05/userelationship-and-direction-of-context-propagation/</a></p>
<p>As what I can see from your illustration, you are placing the formula in fact table and not the lookup table, right?</p>
re: Using USERELATIONSHIP function in Measures and Calculated Columns #dax #powerpivot #ssashttp://sqlblog.com/blogs/marco_russo/archive/2013/10/01/using-userelationship-function-in-measures-and-calculated-columns-dax-powerpivot-ssas.aspx#51817Tue, 19 Nov 2013 22:31:18 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:51817Marco Russo (SQLBI)<p>Tommy,</p>
<p>if you take a look at the formula I used in the article, I remove the existing extended filter by using ALL( Calendar ) in a CALCULATE statement that is applied to the internal CALCULATE that explicitly propagates the fact table into the filter context, so that extended filter uses the alternative relationship instead of the standard one.</p>
<p>Actually, this is a workaround to the issue described by Javier in his blog post.</p>
<p>Marco</p>
re: Using USERELATIONSHIP function in Measures and Calculated Columns #dax #powerpivot #ssashttp://sqlblog.com/blogs/marco_russo/archive/2013/10/01/using-userelationship-function-in-measures-and-calculated-columns-dax-powerpivot-ssas.aspx#51820Wed, 20 Nov 2013 04:35:25 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:51820Tommy<p>Thanks Marco. That's a brilliant idea !</p>
<p>Perhaps Javier shall create a link from his blog to your article for workaround reference ;)</p>
re: Using USERELATIONSHIP function in Measures and Calculated Columns #dax #powerpivot #ssashttp://sqlblog.com/blogs/marco_russo/archive/2013/10/01/using-userelationship-function-in-measures-and-calculated-columns-dax-powerpivot-ssas.aspx#51822Wed, 20 Nov 2013 06:46:45 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:51822Marco Russo (SQLBI)<p>Just added a link in his comments! :)</p>
re: Using USERELATIONSHIP function in Measures and Calculated Columns #dax #powerpivot #ssashttp://sqlblog.com/blogs/marco_russo/archive/2013/10/01/using-userelationship-function-in-measures-and-calculated-columns-dax-powerpivot-ssas.aspx#53424Tue, 01 Apr 2014 12:38:22 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:53424Namrata<p>Hi,</p>
<p>I am working on a tabular cube.Here i have two date columns separation date and resignation date in my fact table .I have two measures ,one that counts the number of employees based on the separation date and one that counts based on resignation date.My DimDate dimension has an active relationship with separation date column and inactive relationship wid resignation date column.</p>
<p>When i access my cube through excel ,both the measures show proper values .But,when i double click on the cell of the measure containing the count of employees based on resignation date(inactive relationship),I get even those rows that i have filtered in my measure.for eg My measure is </p>
<p>ResignedEmployees :=Calculate(Count(EmpID),userelationship(Employee[ResignDate],DimDate[Date]),MyFlag="True")</p>
<p>This should ideally give me only those rows that have "Myflag" column =true,But the drill through seems to ingnore the filter expression of the calculate function and returns more than it is actually showing.This behaviour is really weird and i cannot think of why this could be happening.</p>
<p>Please guide as to what i am missing out here.</p>
<p>Thanks.</p>
re: Using USERELATIONSHIP function in Measures and Calculated Columns #dax #powerpivot #ssashttp://sqlblog.com/blogs/marco_russo/archive/2013/10/01/using-userelationship-function-in-measures-and-calculated-columns-dax-powerpivot-ssas.aspx#53431Tue, 01 Apr 2014 18:22:41 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:53431Marco Russo (SQLBI)<p>Namrata, the drillthrough feature ignore any calculate/filter applied to the measure, and only display the initial filter context. This is a big issue also for YTD measures (you would only see the current selection, not all the days since the beginning of the year).</p>
<p>Unfortunately, there is no way to change the drillthrough behavior.</p>
re: Using USERELATIONSHIP function in Measures and Calculated Columns #dax #powerpivot #ssashttp://sqlblog.com/blogs/marco_russo/archive/2013/10/01/using-userelationship-function-in-measures-and-calculated-columns-dax-powerpivot-ssas.aspx#53437Wed, 02 Apr 2014 05:50:40 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:53437Namrata<p>Hi Marco,</p>
<p>Thanks.Just to be clear,Drill-through ignores the filter context only in case of userelationship function i.e inactive relationships or does it ignore it in general regardless of the relationship????</p>
<p>Because in the latter case(active relationship),the drill-through seems to have always worked for me (i haven't checked for YTD measures though).</p>
<p>Is there any work around for this ,like creating another date dimension connecting to the column used for inactive relationship ?? </p>
re: Using USERELATIONSHIP function in Measures and Calculated Columns #dax #powerpivot #ssashttp://sqlblog.com/blogs/marco_russo/archive/2013/10/01/using-userelationship-function-in-measures-and-calculated-columns-dax-powerpivot-ssas.aspx#53438Wed, 02 Apr 2014 06:07:41 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:53438Marco Russo (SQLBI)<p>Namrata - as I said, drillthrough uses the initial filter context, regardless of the expression you have in the measure. So it ignores the *changes* you apply to filter context in your calculate functions.</p>
re: Using USERELATIONSHIP function in Measures and Calculated Columns #dax #powerpivot #ssashttp://sqlblog.com/blogs/marco_russo/archive/2013/10/01/using-userelationship-function-in-measures-and-calculated-columns-dax-powerpivot-ssas.aspx#53439Wed, 02 Apr 2014 06:49:25 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:53439Namrata<p>Hi Marco,</p>
<p>I think i misunderstood initially,I was so stuck on that active-inactive relationship thingy in my mind.But now i get it as clear as day.</p>
<p>Thanks a lot. :)</p>