Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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:
Solved! Go to 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 ,
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))
That was it. I was missing the CROSSFILTER. Thank you both
Hi,
Do you want this. Download my PBI file from here.
Hope this helps.
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
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?
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)
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
82 | |
71 | |
49 |
User | Count |
---|---|
143 | |
121 | |
111 | |
59 | |
57 |