Forum Discussion
Need help with duplicated data
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]
Anonymous
Ok, that clarifies it. Can you share a sample of your data?
Hey Anonymous
I think you can do the following.
Bear in mind that this is not tested at all (i could have, had you provided data) so it will need refinement but you can get the general idea. Please always share a sample of your data to make things easier for people trying help; you will be more likely to get responses if you do so.
1. Create a calculated column in your table. It will have a 1 when a new treatment is initiated (if more than two weeks since last entry for that farmer) and a zero otherwise.
Table[TreatmentChange] =
VAR MaxWeekGap = 2
VAR Current = Table[Uke]
VAR Previous =
CALCULATE (
MAX ( Table[Uke] );
Table[Uke] < Current;
ALLEXCEPT ( Table; Table[Lockalitetsnummer] )
)
RETURN
IF (
ISBLANK ( Previous );
1;
//Watch out with the edge value. Correct?
IF (
( Current - Previous )
> MaxWeekGap;
1;
0
)
)2. Create a second calculated column that uses the previous one. In each row, Table[TreatmentNumber] will have the number of treatment for that particular farmer.
Table[TreatmentNumber] =
VAR CurrentWeek = Table[Uke]
RETURN
CALCULATE (
SUM ( Table[TreatmentChange] );
Table[Uke] <= CurrentWeek;
ALLEXCEPT ( Table; Table[Lockalitetsnummer] )
)
3. You can then create a third column that concatenates Table[Lockalitetsnummer] and Table[TreatmentNumber]. With that you have an ID for each treatment and can do a count on it or whatever you need.
Thank you for continiuing to look into this. Sorry for the late reply. I've attached the filtered public list in my Power BI.
I'll get on to trying your solution aswell.
www.filedropper.com/emamectinbenzoatsorted
Cheers,
edit@ Everything to the right of "virkestoff" is colums that i use to georaphicly place the treatment, and irrelevant here.
Hi, i tried the formula but i'm getting "token EOF expected" for "MaxWeekGap"
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]
Fantastic! Thank you for all the help. By giving the treatments ID i can use exactly the same approach for all parts of the bigger dataset that I have. Again, thank you!
Cheers,