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 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
Need Help

5 Replies

  • dax's avatar
    dax
    Icon for Community Support rankCommunity Support
    6 years ago

    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'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.