USERELATIONSHIP doesn't seem to be removing active relationship

[MBZA]

I have data like the following. It is the result of merging 3 different queries with different time granularities, hence the repetition. Financial Date and Revenue are from a quarterly revenue dataset split by client; Quote ID, Effective Date, and Process Start and End dates are from a Quote dataset that has one line per quote; and Expense is from an  a separate dataset that only has totals by year.

Financial Quarter Start Date	Quote ID	Revenue	Effective Date	Process Start Date	Process End Date	Expense
2017/01/01	A	100	2017/01/01	2011/12/01	2011/12/15	400
2017/04/01	A	100	2017/01/01	2011/12/01	2011/12/15	400
2017/07/01	A	100	2017/01/01	2011/12/01	2011/12/15	400
2017/10/01	A	100	2017/01/01	2011/12/01	2011/12/15	400
2018/01/01	A	105	2017/01/01	2011/12/01	2011/12/15	500
2018/04/01	A	105	2017/01/01	2011/12/01	2011/12/15	500
2018/07/01	A	110	2017/01/01	2011/12/01	2011/12/15	500
2018/10/01	A	110	2017/01/01	2011/12/01	2011/12/15	500
2019/01/01	A	110	2017/01/01	2011/12/01	2011/12/15	600
2019/04/01	A	110	2017/01/01	2011/12/01	2011/12/15	600
2019/07/01	A	120	2017/01/01	2011/12/01	2011/12/15	600
2019/10/01	A	120	2017/01/01	2011/12/01	2011/12/15	600
2018/01/01	B	200	2017/11/30	2017/03/15	2017/04/10	500
2018/04/01	B	200	2017/11/30	2017/03/15	2017/04/10	500
2018/07/01	B	200	2017/11/30	2017/03/15	2017/04/10	500
2018/10/01	B	250	2017/11/30	2017/03/15	2017/04/10	500
2019/01/01	B	250	2017/11/30	2017/03/15	2017/04/10	600
2019/04/01	B	250	2017/11/30	2017/03/15	2017/04/10	600
2019/07/01	B	250	2017/11/30	2017/03/15	2017/04/10	600
2019/10/01	B	250	2017/11/30	2017/03/15	2017/04/10	600
2018/04/01	C	50	2018/02/15	2017/12/15	2018/01/15	500
2018/07/01	C	50	2018/02/15	2017/12/15	2018/01/15	500
2018/10/01	C	50	2018/02/15	2017/12/15	2018/01/15	500
2019/01/01	C	50	2018/02/15	2017/12/15	2018/01/15	600
2019/04/01	C	70	2018/02/15	2017/12/15	2018/01/15	600
2019/07/01	C	70	2018/02/15	2017/12/15	2018/01/15	600
2019/10/01	C	70	2018/02/15	2017/12/15	2018/01/15	600

I have a calendar table with an active relationship between 'Data'[Quarter Start Date] and 'Calendar'[Date]. I also have an inactive relationship between 'Data'[Effective Date] and 'Calendar'[Date].

I am now trying to split the expenses for each year in proportion to the days spent on each quote with an Effective Date in the corresponding year, and show it alongside revenue. That is, the revenue column will be split by Financial Quarter Start Date, but number of days will be split by Effective Date. Using the sample data above I think this should look like:

	Total Revenue	Total Quote Days
2017	800	40
2018	1430	31
2019	1720	0

My measure for Total Quote Days is not working however. I am using USERELATIONSHIP to use the inactive relationship, but it still seems to be filtering based on Financial Date too - Quote B is getting excluded becaues it's first Financial date is not in the same year as Effective Date. That is I am getting:

	Total Revenue	Total Quote Days
2017	800	14
2018	1430	31
2019	1720	0

My measure is

     MEASURE 'Data'[test] = 
        VAR quotedays = 
            ADDCOLUMNS(
                SUMMARIZE('Data', 'Data'[Quote ID]),
                "val",
                CALCULATE(MINX('Data', IF(DATEDIFF('Data'[Process Start Date], 'Data'[Process End Date], DAY) < 1, 1, DATEDIFF('Data'[Process Start Date], 'Data'[Process End Date], DAY))),USERELATIONSHIP('Data'[Effective Date], 'Calendar'[Date])))
        VAR totaldays = CALCULATE(SUMX(quotedays, [val]))
        RETURN SUMX(quotedays, [val])

 What am I doing wrong? The inactive relationship is definitely set up correctly.

[MBZA]

Thank you! This sorted me out.

[OwenAuger · Accepted solution]

Hi @MBZA 

The main issue appears to be that the USERELATIONSHIP modifier needs to be applied to the overall calculation including SUMMARIZE, not just the innermost calculation.

Otherwise SUMMARIZE will return only Quote IDs based on the active relationship.

Here is what I would recommend (after a bit of editing):

test = 
CALCULATE (
    SUMX (
        SUMMARIZE ( 'Data', 'Data'[Quote ID] ),
        CALCULATE (
            MINX (
                'Data',
                MAX ( INT ( Data[Process End Date] - Data[Process Start Date] ), 1 )
            )
            
        )
    ),
    USERELATIONSHIP ( 'Data'[Effective Date], 'Calendar'[Date] )
)

Does this work for you?

Regards