@Anonymous
Ok, I refined a bit what we had yesterday. No real changes. Following the logic explained earlier, we have three calculated columns to come up with an ID per treatment that you can then use as you please. See below. Here's your .pbix modified, with the three new columns. Does this solve your issue?
1. First column
Table1[TreatmentChange] =
VAR MaxWeekGap = 2
VAR CurrentWeek = Table1[Uke]
VAR PreviousWeek =
CALCULATE (
MAX ( Table1[Uke] );
Table1[Uke] < CurrentWeek;
ALLEXCEPT ( Table1; Table1[Lokalitetsnummer])
)
RETURN
IF (
ISBLANK ( PreviousWeek );
1;
IF (
( CurrentWeek - PreviousWeek )
> MaxWeekGap;
1;
0
)
)2. Second column. The number of treatment is determined as you explained. For a specific farm (Lokalitetsnummer), we start with treatment number 1 and go only to treatment number 2 when we encounter that more than two weeks have elapsed since the last time the site reported.
Table1[TreatmentNumber] =
VAR CurrentWeek = Table1[Uke]
RETURN
CALCULATE (
SUM ( Table1[TreatmentChange] );
Table1[Uke] <= CurrentWeek;
ALLEXCEPT ( Table1; Table1[Lokalitetsnummer] )
)3. Third column with the treatment ID. It is a concatenation of Lokalitetsnummer and the number of treatment. Thus, for example for site with Lokalitetsnummer 11116 we happen to have 11116_1, 11116_2 and 11116_3. Three different treatments.
Table1[TreatmentID] = Table1[Lokalitetsnummer] & "_" & Table1[TreatmentNumber]
