USERELATIONSHIP not working on many to 1

[Matt22365]

Hi all

I hope someone can help

I have 2 inactive relationships between:

• Table1 - Start Date & Table2 - Date
• Table1 - End Date & Table2 - Date

The purpose of this is to plot values from Table1 overtime on a line chart against the start and end dates. 

Table1 has multiple dates vs Table2 being a date table so these need to be a many : 1 relationship

The additional challenge is that I need the values from table 1 to accumulate as the dates go on. This is the bit which does not work with the many : 1 relationship (the formula works if I make a simpiliar version with a 1:1 relationship)

These are the formula:

Running Total End = CALCULATE(SUM('Table1'[Value]),USERELATIONSHIP(Table2[Date],'Table1'[EndDate]), FILTER(ALLSELECTED('Table1'),'Table1'[EndDate] <=MAX('Table1'[EndDate])))

Running Total Start = CALCULATE(SUM('Table1'[Value]),USERELATIONSHIP(Table2[Date],'Table1'[StartDate]), FILTER(ALLSELECTED('Table1'),'Table1'[StartDate] <=MAX('Table1'[StartDate])

I do not get an error with this formula, the issue is that the accumulation aspects stop working and I just get a normal values line chart

Any help would be greatly appreciated

Thanks

MAtt 

[v-yingjl · Accepted solution]

Hi @Matt22365 ,

To calculate the accumulated value, the table in the filter should be table2 not table1:

Running Total Start = 
CALCULATE (
    SUM ( 'Table1'[Value] ),
    USERELATIONSHIP ( Table2[Date], 'Table1'[Start Date] ),
    FILTER (
        ALL ( 'Table2'),
        'Table2'[Date] <= MAX('Table2'[Date])
    )
)

Running Total End = 
CALCULATE (
    SUM ( 'Table1'[Value] ),
    USERELATIONSHIP ( Table2[Date], 'Table1'[End Date] ),
    FILTER(
        ALL(Table2),
        'Table2'[Date] <= MAX('Table2'[Date])
    )
)

Attached the sample file that hopes to help you: USERELATIONSHIP not working on many to 1.pbix

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[v-yingjl · Accepted solution]

Hi @Matt22365 ,

To calculate the accumulated value, the table in the filter should be table2 not table1:

Running Total Start = 
CALCULATE (
    SUM ( 'Table1'[Value] ),
    USERELATIONSHIP ( Table2[Date], 'Table1'[Start Date] ),
    FILTER (
        ALL ( 'Table2'),
        'Table2'[Date] <= MAX('Table2'[Date])
    )
)

Running Total End = 
CALCULATE (
    SUM ( 'Table1'[Value] ),
    USERELATIONSHIP ( Table2[Date], 'Table1'[End Date] ),
    FILTER(
        ALL(Table2),
        'Table2'[Date] <= MAX('Table2'[Date])
    )
)

Attached the sample file that hopes to help you: USERELATIONSHIP not working on many to 1.pbix

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Matt22365 · Accepted solution]

Hi

Thank you very much that worked great. I could not open your example file but the formula update worked.

I has made it so the measure doesnt accumulate in a table visualisation but I can live with that, the trend line was my main priority

Matt 

[amitchandak]

@Matt22365 , should have worked

try like

CALCULATE(SUM('Table1'[Value]),USERELATIONSHIP(Table2[Date],'Table1'[StartDate]), FILTER(ALLSELECTED('Date'),'Date'[Date] <=MAX('Date'[Date])))

CALCULATE(SUM('Table1'[Value]),USERELATIONSHIP(Table2[Date],'Table1'[StartDate]), FILTER(ALLSELECTED('Table1'),'Table1'[StartDate] <=MAX('Table1'[StartDate])))