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:

job no	name	sales	Cost	Main job	Exist related cases
10	Job 1	100	80	20	FALSE
11	Job 2	110	90	30	FALSE
12	Job 3	120	100	20	FALSE
13	Job 4	130	110	30	FALSE
14	Job 5	140	120	20	FALSE
15	Job 6	150	130		FALSE
20	Jobs North			True
30	Jobs South			True

<colgroup><col><col span="4"><col></colgroup><tbody>
</tbody>

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