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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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))
Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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))
Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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