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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MBZA
Frequent Visitor

USERELATIONSHIP doesn't seem to be removing active relationship

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 DateQuote IDRevenueEffective DateProcess Start DateProcess End DateExpense
2017/01/01A1002017/01/012011/12/012011/12/15400
2017/04/01A1002017/01/012011/12/012011/12/15400
2017/07/01A1002017/01/012011/12/012011/12/15400
2017/10/01A1002017/01/012011/12/012011/12/15400
2018/01/01A1052017/01/012011/12/012011/12/15500
2018/04/01A1052017/01/012011/12/012011/12/15500
2018/07/01A1102017/01/012011/12/012011/12/15500
2018/10/01A1102017/01/012011/12/012011/12/15500
2019/01/01A1102017/01/012011/12/012011/12/15600
2019/04/01A1102017/01/012011/12/012011/12/15600
2019/07/01A1202017/01/012011/12/012011/12/15600
2019/10/01A1202017/01/012011/12/012011/12/15600
2018/01/01B2002017/11/302017/03/152017/04/10500
2018/04/01B2002017/11/302017/03/152017/04/10500
2018/07/01B2002017/11/302017/03/152017/04/10500
2018/10/01B2502017/11/302017/03/152017/04/10500
2019/01/01B2502017/11/302017/03/152017/04/10600
2019/04/01B2502017/11/302017/03/152017/04/10600
2019/07/01B2502017/11/302017/03/152017/04/10600
2019/10/01B2502017/11/302017/03/152017/04/10600
2018/04/01C502018/02/152017/12/152018/01/15500
2018/07/01C502018/02/152017/12/152018/01/15500
2018/10/01C502018/02/152017/12/152018/01/15500
2019/01/01C502018/02/152017/12/152018/01/15600
2019/04/01C702018/02/152017/12/152018/01/15600
2019/07/01C702018/02/152017/12/152018/01/15600
2019/10/01C702018/02/152017/12/152018/01/15600

 

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 RevenueTotal Quote Days
201780040
2018143031
201917200

 

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 RevenueTotal Quote Days
201780014
2018143031
201917200

 

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.

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

2 REPLIES 2
MBZA
Frequent Visitor

Thank you! This sorted me out.

OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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