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, 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]
Anonymous
I'm a bit busy right now. You give it a try and if it doesn't work post what you have and we'll take a look.
It should be possible with code quite similar to what you would use in excel.
Haha, AIB i'm not going to claim credit for those lines of code (yet). After trying for a while i got some help from someone more experienced. I'm a veterinarian, not a computer scientist so my baseline DAX knowledge is low. However, with the help of yourself and the others in this great community i'm quickly progressing. Allready my Power BI sheets give me tenfold the information my old Excel sheets do. Everytime one of you help me with a line of code, i analyze it to understand why it is working.
Again, thank you!
And for the future, any pointers on how to progress is much appreciated!
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,
Anonymous
My pleasure
Hi AlB, thank you for all the previous help. From your formula i have managed to build significant improvments on my dataprocessing. However, could I ask you to look into one more thing?
I have expanded the dataset from just 2018 to also include previous years. Naturally this bring another variable to the "treatmentchange" collumn as this just seperates per week. So if a treatment is started in closely realted week in a previous year it isn't picked up.
The sheet is exactly the same as you allready have downloaded, execpt that the "År" collumn now also contains 2017 ,2016 etc.
Kind regards
Hey Anonymous
What first comes to mind, and in order to reutilise to the max the approach we have already have, is to create a new "global" week number, i.e. that gives the week number not for a year but for the whole period you are considering. For instance, imagine your first year is 2016:
- Week 1 of 2016 is your "beginning of time" and would be assigned GlobalWeekNum=1.
- Week number 13 of 2017 would be assigned GlobalWeekNum=NumberOfWeeksIn2016+13=52+13=65
And so on...
so the GlobalWeekNumber doesn't go back to one at the change of the year like the standard week number does, but just continues on.
If you have an extra column with that global week num you would use it instead of [Uke] in the previous code and I believe it should work . Give it a good thought. I'm just saying this quickly.
Be careful with years with 53 weeks and especially with the ALLEXCEPT( ) we had in the code as it might need some changes.
CALCULATE ( MAX ( Table1[Uke] ); Table1[Uke] < CurrentWeek; ALLEXCEPT ( Table1; Table1[Lokalitetsnummer]) )AlBI like your thought, and i agree that it might work, and i don't see any hickups down the line of where I want to go with my data.
As you probably well have realized i'm not exactly fluent in DAX, so do you have a suggested code for the Globalweek collumn?
Cheers,
Thank you for the pointers,
Globalweek =VAR MaxWeeks = SUMMARIZE(ALL('Slice 2016-2018');'Slice 2016-2018'[År];"MaxWeek";MAX('Slice 2016-2018'[Uke]))VAR MyYear = [År]VAR MyStart = SUMX(FILTER(MaxWeeks;[år]<MyYear);[MaxWeek])VAR firstYear = CALCULATE(FIRSTNONBLANK('Slice 2016-2018'[År];1);ALL('Slice 2016-2018'))VAR myNum = IF(MyYear=firstYear;[Uke];MyStart+[Uke])RETURN myNumAppears to have to have done the trick.Cheers,Anonymous
Aha... so "not exactly fluent in DAX", eh? You were trying to trick me. :smileyvery-happy:
That looks pretty awesome. Well done!
you gotten the whole thing to work?
Anonymous
By the way, not sure if you know, you can use the "insert code" button to paste code keeping the formatting here. It's the one with the </> sign, next to the emoticon in the editor.
A tool you might find interesting is the DAX formatter by the Italian gurus. It formats/indents your code nicely, making it more readable and thus more maintainable.