skip to Main Content

Note from Rob

Post is late today NOT because of Chris, but because of me.  Chris had this to me last week in fact, so don’t go thinking that he was slow or something Smile

I am particularly interested in this post because I have personally not yet used USERELATIONSHIP() yet.  OK, take it away Chris…

I couldn’t stop there…

I was feeling pretty good with the result from Tuesday…I helped my customer out. I got a new DAX tool for my toolbox. Life was good. But then I got thinking, “But what if they want to know how many promotions started or ended in May?” “I need that relationship back!”

To tackle this, I created two relationships between DimDate and DimPromotion. One on the StartDate of DimPromotion and one on the EndDate. The key to doing this without breaking my previous measure was to make these relationships inactive.

clip_image016

In PowerPivot, tables may have more than one relationship to each other but only one may be active at any time (inactive relationships are denoted by a dotted line). All DAX expressions use the active relationship by default. However, you can change this behavior with the USERELATIONSHIP function. The USERELATIONSHIP function is a filter function that must be used in conjunction with another function that takes a filter as an argument like CALCULATE.

To answer my “how many promotions started/ended?” question, I created two new measures using the USERELATIONSHIP function:

Promotions Started=
CALCULATE(COUNTROWS(DimPromotion),
          USERELATIONSHIP(DimPromotion[StartDate],
                          DimDate[Datekey]                         )
         )

Promotions Ended=
CALCULATE(COUNTROWS(DimPromotion),
          USERELATIONSHIP(DimPromotion[EndDate],
                          DimDate[Datekey]                         )
         )

Slicing my pivot table by the calendar and using these three measures gives me:

clip_image018

Checking these numbers against the data in the Promotion table shows that each of the columns gives the correct values. The items on the “(blank)” row are because there are Promotion rows that have dates that are outside the range in the Calendar table.

Including both the Active Promotions measure and the Promotions Started and Ended measures lets us do some interesting things like compare the number of promotions starting and ending in a time period to the total that are active in that time period and then even plot those values against a current sales trend.

clip_image020

Hopefully this helps someone out. Removing the relationships from a model isn’t always the right solution but it is in some cases. The USERELATIONSHIP function coupled with inactive relationships gives you the ability to do a lot more with your models and calculations.

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 6 Comments
  1. Sorry, you’re calling these measures, but are these calculated columns? Trying to do this in my tables just gives me an error, because it can’t determine the value for my DimDate[Datekey]

  2. Measures are different from calculated columns in a table. Calculated columns only operate on a row in a table while measures can operate across multiple rows.

    If you’re using Excel 2010, you can create a measure by going to the PowerPivot ribbon and clicking the “New Measure” button.

    If you’re in Excel 2013, measures are now called “Calculated Fields”.

  3. Hi
    I am not that grate om Pewerpito. I got all to work, but I can not get it all right.Your college wrote
    “This assumes that there will always be a start date and end date for every record. In many cases, end dates are left empty for an item that is still active. Changing the expression to include an IF function to check for that should be an easy matter”. how do i get it work?

    Pleas help me…

    Robin

  4. Hi folks,

    first of all, let me thank you for all the great work you do *thumbs up*!
    I encounter a weird problem when using the USERELATIOSHIP function in Powerpivot 2010. I use the following formula to create a measure referencing an inactive relationship:

    Anzahl_Abschluesse:=CALCULATE(DISTINCTCOUNT(ANGEBOTE_VERTRAEGE[VERTRAGSABSCHLUSS]);USERELATIONSHIP(ANGEBOTE_VERTRAEGE[VERTRAGSABSCHLUSS];Kalender_DBR[Date]))

    The weird thing is, when creating the measure, everything is fine and I get the result in the measure area of the powerpivot window. However, when using it in a pivot table without any filters etc. I get an error saying the formula is invalid. When I go back to powerpivot, click in the formula (without changing a thing) and hit enter, I suddenly get the following error (translated from German):
    “Wrong syntax near ‘;’ ”

    This can be reproduced by just deleting one letter of the formula, putting it back in and hitting enter –> formula is calculated without any errors. –> clicking in the formula and and hitting enter –> there we go with the error…

    I really dont get it and I hope you guys can help me.

    Thanks in advance,

    Nico

  5. Hi, Rob.

    I have a question regarding USERELATIONSHIP

    I have two columns in a table Trade Receivable, both related to calendar. Columns are “payment date” and “reporting date”.

    Also I have a disconnected measure – [Selected Actual Base Date] which is driven by slicer.

    I want to filter trade receivable by two filters which relate to payment date and reporting date like:

    Filter only rows in Trade Receivables where Reporting Date = [Selected Actual Base Date] AND Payment date > [Selected Actual Base Date]. In other words I want to filter future payments at the selected reporting date.

    I try to write some kind of measure with USERELATIONSHIP and CALCULATETABLE:

    CashFlow:=CALCULATE(SUM(TradeAR_CB[UAH]);
    CALCULATETABLE(dCalendar;FILTER(dCalendar;dCalendar[Date]=[Selected Actual Base Date]);USERELATIONSHIP(TradeAR_CB[Reporting Period];dCalendar[Date]));
    CALCULATETABLE(dCalendar;FILTER(dCalendar;dCalendar[Date]>[Selected Actual Base Date]);USERELATIONSHIP(TradeAR_CB[Pmnt date];dCalendar[Date])))

    The first CALCULATETABLE works fine, however after applying second filter (CALCULATETABLE) the measure returns blank results.

    Is there a solution since I have to filter manually [Reporting Period] by Slicer now.

  6. Is it possible that inactive relationships can increase memory consumption of the model a lot? I have been struggling with one of the models with “out of memory error” and after a lot of testing and troubleshooting beginning to feel that removing all (only had two) inactive relationships makes things work better. Could my hypothesis be true?

Leave a Comment or Question