Forum Discussion
IgorKaradzic
Helper I
2 years agoCan' assign table elements to variables
Hi guys, i am trying to assign for example VAR test = 'Query1'[Field] but it doesn' work. Erorr is can't find name 'Field' but i know for a fact that it exists and i'm not spelling it wrong. Can ...
Anonymous
2 years agoNot applicable
First, Thanks for some_bih and Dangar332 help.
Hi, IgorKaradzic
May I ask if you have solved this case, I noticed that no one is following your problem, you may refer to the following DAX.
DAX:
Duration = DATEDIFF('Table'[Planned Start Date],'Table'[Planned Finish Date],DAY)
Pre Duration =
VAR _id = 'Table'[ID Task]
VAR _index = 'Table'[Index]
VAR _preduration =
CALCULATE (
MIN ( 'Table'[Duration] ),
FILTER ( 'Table', 'Table'[ID Task] = _id && 'Table'[Index] = _index - 1 )
)
RETURN
_preduration
Diff =
VAR _id = 'Table'[ID Task]
VAR _index = 'Table'[Index]
VAR _sdate = 'Table'[Planned Start Date]
VAR _prefdate =
CALCULATE (
MAX ( 'Table'[Planned Finish Date] ),
FILTER ( 'Table', 'Table'[ID Task] = _id && 'Table'[Index] < _index )
)
VAR _diff =
IF ( ISBLANK ( _prefdate ), BLANK (), DATEDIFF ( _prefdate, _sdate, DAY ) )
RETURN
_diff
Actual Start Date =
VAR _id = 'Table'[ID Task]
VAR _index = 'Table'[Index]
VAR _sdate = 'Table'[Planned Start Date]
VAR _taskd0 =
CALCULATE (
MIN ( 'Table'[Responsible Task] ),
FILTER (
'Table',
'Table'[ID Task] = _id
&& NOT ( ISBLANK ( 'Table'[Pre Duration] ) )
&& 'Table'[Diff] <= 0
)
)
VAR _sdate1 =
CALCULATE (
MIN ( 'Table'[Planned Start Date] ),
FILTER (
'Table',
'Table'[ID Task] = _id
&& NOT ( ISBLANK ( 'Table'[Pre Duration] ) )
&& 'Table'[Responsible Task] = _taskd0
)
)
VAR _index1 =
CALCULATE (
MIN ( 'Table'[index] ),
FILTER (
'Table',
'Table'[ID Task] = _id
&& NOT ( ISBLANK ( 'Table'[Pre Duration] ) )
&& 'Table'[Responsible Task] = _taskd0
)
)
VAR _dnums =
CALCULATE (
SUM ( 'Table'[Pre Duration] ),
FILTER (
'Table',
'Table'[ID Task] = _id
&& NOT ( ISBLANK ( 'Table'[Pre Duration] ) )
&& 'Table'[Index] <= _index
)
)
RETURN
IF (
ISBLANK ( 'Table'[Diff] ),
'Table'[Planned Start Date],
IF (
'Table'[Responsible Task] = _taskd0,
'Table'[Planned Start Date] + [Diff] + 1,
_sdate1 + _dnums + [Index] - _index1 - 1
)
)
Actual Finish Date = 'Table'[Actual Start Date]+[Duration]
Best Regards,
Yang
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum