Can I use USERELATIONSHIP between YEAR and QUARTER columns in one table...

[Chanise89]

Good Evening ,

I'm not sure if this is possible but lets see...

I have 2 tables :

1. GL Acct Data 

2. Calendar table

I need to filter 'Actual Amount' by Quarter and Year. (Literally by using a dropdown filter).

I have created calculation to use the inactive relationship between Quarter and Year from the 'GL Acct Data' table to the "Quarter" and "Year" columns in the 'Calendar' table: 

This works great as expected. 

However, when I create two seperate dropdown filters using the "Quarter" and "Year" from the Calendar table, I need it to filter the 'Actual Amount' field. 

They work seperately but I need one 'Actual Amount' column...

Please help!

[Chanise89]

@Ashish_Mathur 

will this work?

Year & Quarter = 'GL Acct Data'[N_YEAR] & "," &'GL Acct Data'[QUARTER]

[Ashish_Mathur · Accepted solution]

No.  It will not.  You need an actual date column.  If the Quarter column has number 1,2,3,4, then write this calculated column formula to create a Date column

Date = date('GL Acct Data'[N_YEAR],3*'GL Acct Data'[QUARTER],1)

Hope this helps.

[Ashish_Mathur]

Hi,

You should not have 2 seperate measures.  Assuming there is a Date field in the GL Acct Data table, the inactive relationship should be from the Date field in this table to the Date field in the Calendar Table.  In the USERELATIONSHIP() function, the inputs should be of the Date column (not year column).

[Chanise89]

HI @Ashish_Mathur ,

Unforturnely there isn't a Date field in the GL Acct data table - there is only Quarter and Year. 

I am working off a query that only pulls in Quarter and Year. ..

Do you think its impossible to do what I am asking?

[Ashish_Mathur]

Hi,

From the Quarter and Year fields, create a calculated column formula to get the Date.  Then follow mu suggestion mentioned in the previous post.