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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Composed_MS
Helper I
Helper I

Confusion in table connection for showing Trend

Hello,


I need to show the trend of previous 12 months data from the MonthYear selected in the slicer.

So, for this I have created duplicate of Calendar table (named Calendar2) and then created the connection as below with my fact table and main Calendar table.
Notice that the connection between Calendar table and Calendar2 table is inactive.

Composed_MS_0-1680205918141.png


I am using below measure to show trend->

 

Trend =
var previous_dates=DATESINPERIOD(Calendar2[Date],MAX(Calendar[Date]),-12,MONTH)

return
 CALCULATE([Measure],
    REMOVEFILTERS('Calendar'),
    KEEPFILTERS(previous_dates),
    USERELATIONSHIP('Calendar'[Date],Calendar2[Date]))
 

This is showing the correct trend (I am keeping MonthYear from Calendar2 table in X axis), but I am getting confused that why we are not creating a direct active connection between fact table and Calendar2 table as well, here if you see there's no connection between fact table and Calendar2 table.

Why can't we do that? When will I face the issue if I do this?

Can anyone please explain?

2 REPLIES 2
amitchandak
Super User
Super User

@Composed_MS , if you want show trend based on selected value, then calendar 2 should not join with any table

 

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -12) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

 

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

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

Hello @amitchandak 

I need to select MonthYear from Calendar table date slicer and in trend I am showing Calendar2 dates, so for that I am connecting my Calendar table with Calendar2 table inactively and then using Userrelationship function.
That thing is not creating any issue as it is giving me the correct trend from the "Trend" measure I wrote above.
My confusion is why do we not create the direct relationship of Calendar2 with fact table?

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.