Forum Discussion

Matt22365's avatar
Matt22365
Icon for Resolver III rankResolver III
6 years ago

Aggregate measure stopped working after USERELATIONSHIP feature added

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 ad...
Need Help
Matt22365's avatar
Matt22365
Icon for Resolver III rankResolver III
6 years ago

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's avatar
dax
Icon for Community Support rankCommunity Support
6 years ago

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's avatar
    Matt22365
    Icon for Resolver III rankResolver III
    6 years ago

    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 NameTaskFixedCostTaskFinishDateTaskBaselineFinishDate
    Task 1£           1,000.0003/01/202012/11/2019
    Task 2£               500.0015/04/202005/02/2020
    Task 3£                     -  18/05/202016/02/2020
    Task 4£           2,000.0001/08/202003/06/2020
    Task 5£           5,000.0009/09/202001/08/2020
    Task 6£         10,000.0011/12/202010/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's avatar
      Matt22365
      Icon for Resolver III rankResolver III
      6 years ago

      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