topic Re: measure with different offsets on date, depending on data coming from 2 facts table in DAX Commands and Tips

measure with different offsets on date, depending on data coming from 2 facts table

arq52 — Wed, 28 Oct 2020 22:33:35 GMT

Hello,
I have a task table, with due dates and quantities:

A table of resources requested per task, different steps with the unit load and a number of days in advance of the due date:

Current connections:

I'm trying to calculate the workload by resource, per weeks generated by these tasks, thanks to the offset from the due date.

* total load = UN_load * quantity
* each of these loads positioned at ending date + offset
Expected "results" :

I would know how to do this with powerquery, but with my actual datas, it would make me generate a results file of over 2 million records. I guess that it may be feasable with dax with 10 to 20 times less recordings.
Thank you in advance for your help.

Re: measure with different offsets on date, depending on data coming from 2 facts table

arq52 — Thu, 05 Nov 2020 23:21:54 GMT

Hi,

I have tryed

total load :=
SUMX (
'schedule';
'schedule'[quantity]
    * CALCULATE (
      VALUES ( 'ressource_per_tasks'[UN_load] );
      FILTER (
        'ressource_per_tasks';
        'ressource_per_tasks'[tasks] = 'schedule'[tasks]
      );
      FILTER (
        'Calendar';
        'Calendar'[date] = 'schedule'[ending date] + MAX('ressource_per_tasks'[offset])
    )
)
)

With "MAX", I have "remove" an error about "not unique value for offset column".

But when I try to use the mesure in a pivot table, I get a "calculation error with the measure : a table with several values have been provided, but a unique value was expected".

Do you have an idee to solve that ?

Best regards

Re: measure with different offsets on date, depending on data coming from 2 facts table

wdx223_Daniel — Fri, 06 Nov 2020 00:52:22 GMT

arq52 i got the result you expected

Load_Date:=IF(COUNTROWS('resource_per_task')*COUNTROWS('schedule'),MAX('schedule'[ending date])+MAX('resource_per_task'[offset])) **************************************** Total_Load:=IF(COUNTROWS('resource_per_task')*COUNTROWS('schedule'),MAX('schedule'[quantity])*MAX('resource_per_task'[UN_load]))

Re: measure with different offsets on date, depending on data coming from 2 facts table

arq52 — Wed, 11 Nov 2020 14:41:09 GMT

Thank you Daniel,

Not far from what I'm expecting, but I want to be able to do pivot table and link the load to the calendar date. With this measure, the load is linked to the ending date and not to the "load date":

Re: measure with different offsets on date, depending on data coming from 2 facts table

wdx223_Daniel — Thu, 12 Nov 2020 03:10:12 GMT

arq52 is this what you want?

Total Load := SUMX ( CROSSJOIN ( VALUES ( task[tasks] ), VALUES ( resource_per_task[resource] ), VALUES ( 'Calendar'[Date] ) ), VAR _CurrentDate = 'Calendar'[Date] VAR _EndingDate = CALCULATE ( MIN ( schedule[ending date] ), 'Calendar'[Date] >= _CurrentDate ) VAR _Offset = CALCULATE ( SUM ( resource_per_task[offset] ) ) VAR _Load = CALCULATE ( SUM ( schedule[quantity] ), 'Calendar'[Date] = _EndingDate ) * CALCULATE ( SUM ( resource_per_task[UN_load] ) ) RETURN IF ( _CurrentDate = _EndingDate + _Offset, _Load ) )

Re: measure with different offsets on date, depending on data coming from 2 facts table

arq52 — Thu, 12 Nov 2020 07:45:15 GMT

Hello Daniel,

it's perfect!

Now just to check the performance point of vue with my real datas.

Thanks again.