Calculated Column DAX syntax issue

[Anonymous]

Hi all, here my issue... 

I have this table where each raw is a project (with a 'project number', a 'report date', a 'last update date', and a 'project status'). This table is weekly updated, so a same project (=same 'project number') appears several time in the table, but with a different report date (=date of the weekly update), a eventually a different project status and a different 'last update date'.

I'd need to create a calculate column, which calculate for every project ('project number') the date ('last update date') when the project status moved to "in progress"... I've tried the following, but ended up with a circular dependency:

In progress date =
var myproject='mytable'[project #]
var temp= CALCULATETABLE('mytable','mytable'[project #]=myproject,'mytable'[project status]="In Progress")
return
FIRSTDATE(SUMMARIZE(temp,'mytable'[Last Updated Date]))

Would have have a solution to solve this?

Thx!!!

[DAX0110 · Accepted solution]

Hi @Anonymous,  if you rewrite the calculated column this way, you can avoid the circular dependency error:

In progress date =
var myproject='mytable'[project #]

return CALCULATE(

    MIN( 'mytable'[Last Updated Date] )

    , ALL( 'mytable' )

    , 'mytable'[project #] = myproject

    , 'mytable'[project status] = "In Progress"

    )

[Anonymous]

Hi @DAX0110!

brilliant, works perfectly. Why does it look so easy after I've read your proposal, and so tricky before? 🙂

[DAX0110]

You're welcome @Anonymous, circular dependency always gives me a headache ;), so I had to start from scratch...