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]
Hi, thank you for taking interest!
Sadly the way a treatment is registered is with an individual input each week without an uniqe ID for the treatment. So each treatment usually has 2-3 inputs in the goverment list. This is due to the way farmers are reporting to the goverment. Which in essense can be seen as: are you treating? if yes there is additional data
The data collected can be seen as: week, year, site-id, site name, action, type of treatment, chemical
As you can see from the attached picture, treatments often range over several weeks, which makes it hard to figure out how many uniqe treatments there actually has been. A regular remove duplicate would make earlier treatments disapear.
So, could you think of a way to get power bi to count just one of the inputs per treatment?
Could for example, making it count the treatment if there is more than 5 weeks since the last treatment at that site be a solution?
Hope that clarifies a bit,
Thank you!
Anonymous
I'm a bit lost here.
We first need to clarify what a "treatment" is. You say they do not have IDs but at the same time you claim for instance that "...each treatment usually has 2-3 inputs in the goverment list..."
How do you tell those 2-3 inputs from the rest???
Hi, I can see that it is a bit messy written, sorry for that.
A treatment in this case is when a farm has reported in their weekly report to the authorities that they are using a medical compound. These usualy ranges from 1-3 weeks, and generates a row on the public list per week the treatment is ongoing.
I am interested in generating statistics on how many treatments has been conducted. Therefore, the weekly entries messes up the data as what is actually one treatment is counted 2-3 times by Power Bi.
to clarify, attached is a small part of the list, with two scenarios.
1. In this case you can see that the name of the site treating is Aldeøyna. both of the rows is in this case the same treatment (which is what messes up the counting). However, it is ranging over two weeks and therefore has two rows (one per week reportend ongoing treatment)
2. In this case the site Austenesåa is treating. The entries for week 1 and 2 is the same treatment (ongoing for two weeks), but since there is more than 2 weeks since the last reported treatment, you can see that in week (uke) 6 a new treatment is initiated.
The way to seperate between what is the same treatment and a new one is to look at the weeknumbers. if there is more than 2 weeks since the site (lokalitetsnavn) reported a treatment, it should be considered a new one.
Hope this clarifies
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.