Can' assign table elements to variables

[IgorKaradzic]

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 anybody help me with this?

Thank you. 

[v-yaningy-msft]

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

[some_bih]

Hi @IgorKaradzic what is your request for possible solution: calculate something and also on your example table there is no [Field] (reference to your initial post: Query1'[Field] but it doesn' work. Erorr is can't find name 'Field' )

[IgorKaradzic]

That's what is says in error message. I sent you example table down bellow.

[IgorKaradzic]

ID Task Responsible	Planned Start Date	Planned Finish Date
1	13.3.2024.	23.3.2024.
2	23.3.2024.	25.3.2024.
2	25.3.2024.	1.4.2024.
2	25.3.2024.	29.3.2024.
2	29.3.2024.	7.4.2024.
2	7.4.2024.	10.4.2024.

[some_bih]

Hi @IgorKaradzic I am sorry, I do not understand your issue based on information from your side.

[IgorKaradzic]

First i would like to compare data from column ID Task Responsible. First row from that column with second row, then second row with third row and so on. If they are not equal then do nothing. If they are equal, we must check if there is overlaping in dates. If there is, then i must for example add 5 hours to second compared row. We must do that for each 2 rows like i mentioned. Is it possible to do something like that? 

[some_bih]

Hi @IgorKaradzic "comparing" row by row for same column could be tricky as you have same (not unique) in column ID.

Why don't you use column Planned Finish Date in your calculation logic, if possible?

[IgorKaradzic]

To rephrase mi initial input, the result of this exercise is to show which dates are overlaping for the same task so the next task can't start during the previous. Basically i want to have another column generated where it suggests the start date of the overlaping task is in form of finished date of previous task + 1 day.

[some_bih]

Hi @IgorKaradzic 

So one of output is which date is not overlaping (not task for that date active or similar...)? It would be view per date?

How you define overlaping? Two and more distinct ID per task or ...?

What is desired output for table above (in than one or more new columns)

[IgorKaradzic]

ID Task Responsible	Task	Planned Start Date	Planned Finish Date
1	Task1	13.3.2024.	23.3.2024.
2	Task2	23.3.2024.	25.3.2024.
2	Task3	25.3.2024.	1.4.2024.
2	Task4	25.3.2024.	29.3.2024.
2	Task5	29.3.2024.	7.4.2024.
2	Task6	7.4.2024.	10.4.2024.

Overlaping is where one employee works on different tasks simultaneously as you can see from this table.

[some_bih]

Hi @IgorKaradzic 

I understand that Task colum and column Planned Finish Date are critical input for new column NextDateForTask with definition below.

It could be that another column is key inputs as your simple example do not have some expected output.

Check it with your real data.

NextDateForTask =

--assuming Task is column criteria for max date

CALCULATE(

    MAX(Sheet1[Planned Finish Date]),

    ALLEXCEPT(Sheet1,Sheet1[Task])

)+1--adding one single date for start next task

Output

[IgorKaradzic]

Hello, sorry for not answering earlier but this is still not the solution i am looking for. It's close but not it. Thank you for your help.

[some_bih]

Hi @IgorKaradzic 

I expected your last answer as I did not know what inputs are and at which level to provide calculated column works as you want.

[IgorKaradzic]

Like i said in the last reply, you were close. Instead of increasing next start date by +1 for every row separately, what should be done is based on previous row end date increase current row start date by previous row end date + 1.

[some_bih]

Hi @IgorKaradzic 

Check definition for column PreviousRowEndDate

PreviousRowEndDate =

//calculation based on ID Task and Finish Date

VAR __idtask_responsible=Sheet1[ID Task Responsible]

VAR __task=Sheet1[Task]

VAR __date_of_planned_finish=Sheet1[Planned Finish Date]

VAR __calculation =

        CALCULATE(

            MAX(Sheet1[Planned Finish Date]),

            FILTER(Sheet1,

                Sheet1[ID Task Responsible]=__idtask_responsible && Sheet1[Planned Finish Date] < __date_of_planned_finish

            )

        )

VAR Result= IF(__calculation = BLANK(), Sheet1[Planned Finish Date], __calculation+1)

//in Result adding 1 day

RETURN Result

[IgorKaradzic]

I know you are trying your best to help me but looks like i don't know how to explain the end goal of this so i will send you table which i want as a result and yu tell me if it's possible to do it. We compare each 2 tasks, for example task 1 with task 2 and nothing changes etween them because their id's are different, task 1 with task 2, same id's and we can see that task 1 end date is same as task 2 start date so we need to move task 2 start date by task 1 end date + 1 and also move task 2 end date too. And we do that for task 2 with task 3 etc.

ID Task Responsible	Task	Planned Start Date	Planned Finish Date
1	Task1	13.3.2024.	23.3.2024.
2	Task2	23.3.2024.	25.3.2024.
2	Task3	25.3.2024.	1.4.2024.
2	Task4	25.3.2024.	29.3.2024.
2	Task5	29.3.2024.	7.4.2024.
2	Task6	7.4.2024.	10.4.2024.

Actual Start Date	Actual Finish Date
13.3.2024.	23.3.2024.
23.3.2024.	25.3.2024.
26.3.2024.	2.4.2024.
3.4.2024.	7.3.2024.
8.4.2024.	17.4.2024.
18.4.2024.	21.4.2024.

[some_bih]

Hi @IgorKaradzic 

I could not follow your logic. Maybe somebody else could help you

[IgorKaradzic]

Got it, thank you for your help regardless. Sorry i wans't able to convey my message in simpler way.

[Dangar332]

hi, @IgorKaradzic 

if its measure then need to provide aggregation function like

min(),max(),sum()...

so maybe try with VAR test = min('Query1'[Field])

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

[IgorKaradzic]

I don't need min or max. I need currently selected value.