Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

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
 
 
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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))

View solution in original post

7 REPLIES 7
amitchandak
Super User
Super User

@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
Not applicable

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

Ashish_Mathur
Super User
Super User

Hi,

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

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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

 

Capture.JPG

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?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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
Not applicable

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors