Model and DAX questions related to Inactive relationships and the USERELATIONSHIP function

[Anonymous]

Hi All,

v-easonf-msft was kind enough to give me a solution to a problem I was having with a selection on a Timeline slicer and seeing measure values for that selection for previous years in a bar chart. The recommendation was to go to the Model section and create a relationship set to Active between the Date table and the "Fact" table based on Month (not Date). This seemed to give me what I wanted. However in my current model I use the Date field (or a DateKey or some other field) as the active relationship as I have other charts, etc that need to see data in the conventional way.

I was looking to add the Month relationship as an Inactive relationship and use the DAX function USERELATIONSHIP to set this in my measure. Such as:

Total Sales =

CALCULATE(SUM(Sales[Sales]),USERELATIONSHIP('Date'[Month],Sales[Month]))

However this shows nothing in the chart. If I change the relationship to Active in the model it works. Any idea why this measure would not work here? I am attaching links to both pbix files (they are pretty straightforward). Thanks in advance

pbix - Timeline - Works 

pbix - Timeline - Doesn't Work 

[amitchandak · Accepted solution]

@Anonymous ,

I think @Ashish_Mathur , already given solution. But I am not sure why you want to join a month with a month. This had make it work

Total Sales = 
CALCULATE(SUM(Sales[Sales]),USERELATIONSHIP('Date'[Month],Sales[Month]),CROSSFILTER('Date'[Date],Sales[Date],None))

[Anonymous]

That was it. I was missing the CROSSFILTER. Thank you both

[Ashish_Mathur]

Hi,

Do you want this.  Download my PBI file from here.

Hope this helps.

[Anonymous]

No I would like it to have Years on the axis in the chart. The timeline slicer should have months for this current year only and the selection should be the sum of Sales for the selection (i.e if Mar and Apr are selected then give me the sum for those 2 months in each year) shown in each year on the chart

[Ashish_Mathur]

Hi,

I just do not understand your requirement.  If you select March-April 2020 in the slicer, then why should the viz show data for historical years?

[Anonymous]

Ashish...that is just the business requirement. They would like to see the selected period across historical years. (i.e. Sales for Jan-Apr are 'x' dollars - how did we do for the same month range last year, and the year before, etc)

[Anonymous]

Again this works when the relationship is directly set to Active. I would like to know why my DAX measure doesn't work when this relactionship is set to Inactive