Aggregate measure stopped working after USERELATIONSHIP feature added

[Matt22365]

Hi

I have a measure which aggregates all the values in my TaskFixedCost column over a selected period of time. The measure works perfectly until I add a USERELATIONSHIP feature which allows to me add 2 value lines to a chart using the same value column but different date columns.

Once this feature is added the costs stop aggregating and just appear as the value in the column

Is there something from the measure i need to add to compensate for the USERELATIONSHIP field?

This is one of the 2 measures:

Running Total Actual = CALCULATE(SUM('Tasks & Baselines'[TaskFixedCost]),USERELATIONSHIP('Tasks & Baselines'[TaskFinishDate],TimeSet[TimeByDay]),FILTER(ALLSELECTED('Tasks & Baselines'),'Tasks & Baselines'[TaskFinishDate] <=MAX('Tasks & Baselines'[TaskFinishDate])))

Thanks

Matt

[dax]

Hi @Matt22365 ,

I am not clear about your requirement, if possible could you please inform me more detailed information(such as your expected output and your sample data )? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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

[Matt22365]

morning @dax 

Thanks for getting back to me

I am trying to plot a cost value against time as a line chart. However I want the costs to aggregate over time to show the trend of spend.

I made a measure to do this aggregation which worked great. 

Running Total Actual = CALCULATE(SUM('Tasks & Baselines'[TaskFixedCost]), FILTER(ALLSELECTED('Tasks & Baselines'),'Tasks & Baselines'[TaskFinishDate] <=MAX('Tasks & Baselines'[TaskFinishDate])))

However I wanted to add an additional line to the chart which showed the same cost value but based on a different date column. I followed some advice to create a table which is used for the date and 2 inactive relationships between this new table and the 2 date columns in my original table. Then add the userelationship feature in the measure.

When I add the userelationship the data nolonger aggregates and the graph just shows the individual costs rather than the aggregated ones

Running Total Actual = CALCULATE(SUM('Tasks & Baselines'[TaskFixedCost]),USERELATIONSHIP(TimeSet[TimeByDay],'Tasks & Baselines'[TaskFinishDate]), FILTER(ALLSELECTED('Tasks & Baselines'),'Tasks & Baselines'[TaskFinishDate] <=MAX('Tasks & Baselines'[TaskFinishDate])))

The output I would like is 1 line on a chart showing the expected spend based on a date column which represents the original view of the timeline and 1 line representing the current expected spends

I am using MS Project so its hard to send existing data, however essentially it is 1 table with "Cost", "Task FinishDate" and "TaskBaselineFinishDate". Then another Date table "Timeset"

Thanks for your help

Matt

[dax]

Hi @Matt22365 , 

If you want to show two tables' value in the same visual, I think you could create relationship on date table or other column which exist in two tables. Then based date column create measure. By the way, I don't know your data structure and expected output, so if possible, could you please inform me your sample datas(you could use virtual data instead of real data)? Then I will try to test this in my enviornment.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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

[Matt22365]

Good morning @dax 

I cannot link to a data set but i have made a simplifed table below which represents the key columns in my table - Tasks & Baselines

Task Name	TaskFixedCost	TaskFinishDate	TaskBaselineFinishDate
Task 1	£           1,000.00	03/01/2020	12/11/2019
Task 2	£               500.00	15/04/2020	05/02/2020
Task 3	£                     -	18/05/2020	16/02/2020
Task 4	£           2,000.00	01/08/2020	03/06/2020
Task 5	£           5,000.00	09/09/2020	01/08/2020
Task 6	£         10,000.00	11/12/2020	10/09/2020

What I would like to do is plot a running total of my TaskFixedCost in a linechart where 1 line represents the TaskFinishDate and 1 line represents my TaskBaselineFinishDate.

simple version from excel here:

The measure I created - 

Running Total baseline = CALCULATE(SUM('Tasks & Baselines'[TaskFixedCost]),FILTER(ALLSELECTED('Tasks & Baselines'),'Tasks & Baselines'[TaskBaselines.TaskBaselineFinishDate] <=MAX('Tasks & Baselines'[TaskBaselines.TaskBaselineFinishDate])))

works great if I only want 1 of the 2 lines on the graph

to get the 2nd line I was recommended to make a date table ("Timeset" in my calculation) and use 2 inactive relationships to link TaskFinish and TaskBaselineFinish to this date table. When I add in a USERELATIONSHIP step into the above measure the running total aspect stops working and i get a graph of just the TaskFixedCost individual lines rather than the running total

This is the new measure (example is only for baseline):

Running Total baseline = CALCULATE(SUM('Tasks & Baselines'[TaskFixedCost]),USERELATIONSHIP(TimeSet[TimeByDay],'Tasks & Baselines'[TaskBaselines.TaskBaselineFinishDate]), FILTER(ALLSELECTED('Tasks & Baselines'),'Tasks & Baselines'[TaskBaselines.TaskBaselineFinishDate] <=MAX('Tasks & Baselines'[TaskBaselines.TaskBaselineFinishDate])))

This is my relationship

Any help would be greatly appreciated

Thanks

Matt

[Matt22365]

Hi @dax 

It seems that the issue is the type of connection between the 2 tables. If I simplify the data so I can have a 1:1 relationship the formula works, however my actual data is more complex so I need a many:1 relationship which causes it to stop working

Is there a way to fix this issue?

Thanks

Matt