Make a count of records with a function in the same table but filtered by a field in Current record ?

[RasmusKA]

Hello

I have tried and tried but just cant get this to work. I have a feeling that im so close but have just been missing some critical point.

Im Taking data from our SQL with Dynamics NAV. Works perfect

I have a table "Jobs" like the example below. The Column "Exits Related cases" is the field i want calculated - in NAV its a Flowfield so it doesnt come out in my export from the base.

I would like to have a Function that can check whether a Job is related to other Jobs in the same table.

I have tried the following: = if(COUNTAX(Jobs;filter(Jobs;Jobs[Job_No]=[Main_Job]))>0;True;False)
And =CALCULATE(COUNTA(Jobs[Job_No]);Jobs[Main_Job]=[Job_No])

But i just get errors or Blank.

An example from our Jobs Table:
[TABLE="width: 493"]
<colgroup><col><col span="4"><col></colgroup><tbody>[TR]
[TD]job no[/TD]
[TD]name[/TD]
[TD]sales [/TD]
[TD]Cost[/TD]
[TD]Main job[/TD]
[TD]Exist related cases[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Job 1[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]20
[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Job 2[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]30[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Job 3[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]20[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Job 4[/TD]
[TD="align: right"]130[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]30[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Job 5[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]20[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Job 6[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]130[/TD]
[TD][/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD="colspan: 2"]Jobs North[/TD]
[TD][/TD]
[TD][/TD]
[TD]True[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD="colspan: 2"]Jobs South[/TD]
[TD][/TD]
[TD][/TD]
[TD]True[/TD]
[/TR]
</tbody>[/TABLE]

Best Regards and thx for a freat forum.

Rasmus
-Denmark

PS - does anyone know why my formulas need Semicolon instead of comma bettwen function arguments? Is it a regional setting?

 

[Jacob Barnett]

Rasmus, you are pretty close! Try this:

=CALCULATE(
      COUNT(jobs[job no]), 
           FILTER(jobs,
                 jobs[Main job]=EARLIER(jobs[job no])
                     )
            )

The syntax for calculated columns can be a little tricky, EARLIER() is slightly difficult to understand but in simple terms such as this it returns the item from the row you are in (technically its the previous row context hence the name).

The ; thing is regional!

 

[RasmusKA]

Hi Jacob

Fantastic, your advice made it work. Thank you very much.

I was so close but still far away.... Earlier() function now put in the black book.

Best regards
Rasmus