cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frivik Regular Visitor
Regular Visitor

Re: Need help with duplicated data

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,

Super User
Super User

Re: Need help with duplicated data

@Frivik

My pleasure

Frivik Regular Visitor
Regular Visitor

Re: Need help with duplicated data

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

Super User
Super User

Re: Need help with duplicated data

Hey @Frivik

 

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])
    )

 

 

Frivik Regular Visitor
Regular Visitor

Re: Need help with duplicated data

@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, 

Highlighted
Super User
Super User

Re: Need help with duplicated data

@Frivik

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.

Frivik Regular Visitor
Regular Visitor

Re: Need help with duplicated data

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 myNum
 
Appears to have to have done the trick. 
 
Cheers,
Super User
Super User

Re: Need help with duplicated data

@Frivik

Aha... so "not exactly fluent in DAX", eh? You were trying to trick me. Smiley Very Happy

That looks pretty awesome.  Well done!

you gotten the whole thing to work?

Super User
Super User

Re: Need help with duplicated data

@Frivik

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.   

Frivik Regular Visitor
Regular Visitor

Re: Need help with duplicated data

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!