cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Pandadev
Post Partisan
Post Partisan

Is there a way to check if a code has changed in a column filtered by , next date

I have a table consisting of 3 columns and 3 million rows , looking to find a way to highlight when a code changes based on matching ID , based on change from next previous date. example below

ID            / Date           / Code

dd2929   24/11/2020    CC  flag row as code changed from previous next date based matching ID

dd2929  23/11/2020     BB

dd2929  22/11/2020     BB  flag row as code changed from previous next date based matching ID

dd2929  19/11/2020    AA

dd2929  17/11/2020    AA

dd2929  15/11/2020    AA

dd2929   14/11/2020    AA

dd2929  12/11/2020    AA

dd2929 10/11/2020    AA

dd2930   24/11/2020    XX   

dd2930  23/11/2020     XX

dd2930  22/11/2020     XX    

dd2930  21/11/2020    XX  flag row as code changed from previous next date based matching ID

dd2930  19/11/2020    YY

dd2930  17/11/2020    YY

dd2930  15/11/2020    YY

dd2930  14/11/2020    FF  flag row as code changed from previous next date based matching ID

dd2930 10/11/2020    YY

 

 

 

7 REPLIES 7
mahoneypat
Microsoft
Microsoft

Since you have 3 million rows, performance could be an issue.  Please also try this expression and let me know which is faster.

 

Code Change 2 =
VAR thiscode = Codes[Code]
VAR thisdate = Codes[Date]
VAR prevcode =
    CALCULATE (
        LASTNONBLANKVALUE (
            Codes[Date],
            MIN ( Codes[Code] )
        ),
        ALLEXCEPT (
            codes,
            Codes[ID]
        ),
        Codes[Date] < thisdate
    )
RETURN
    IF (
        ISBLANK ( prevcode ),
        BLANK (),
        IF (
            prevcode <> thiscode,
            "Y"
        )
    )

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Pandadev
Post Partisan
Post Partisan

i get this error when adding it to dax , The following syntax error occurred during parsing: Invalid token, Line 3, Offset 112, ].

 

Is there a solution that works in power query

This is much better done on the DAX side.  Here is another column expression that should work.

 

Code Change =
VAR thiscode = Codes[Code]
VAR thisdate = Codes[Date]
VAR prevdate =
    CALCULATE (
        MAX ( Codes[Date] ),
        ALLEXCEPT (
            Codes,
            Codes[ID]
        ),
        Codes[Date] < thisdate
    )
VAR prevcode =
    CALCULATE (
        MIN ( Codes[Code] ),
        ALLEXCEPT (
            Codes,
            Codes[ID]
        ),
        Codes[Date] = prevdate
    )
RETURN
    IF (
        ISBLANK ( prevdate ),
        BLANK (),
        IF (
            prevcode <> thiscode,
            "Y"
        )
    )

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Thanks , that code worked perfect , is it possible to get it work with date time formatted dates as I forgot the dates also have times , as some dates have 3 or 4 rows of data logged

Yes.  It should work with DateTime instead of Date.  Which one did you end up using?  Did you compare calculation times?

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


When I try to run it on the date time column , I get an out of memory errror , please try again when you may have more resources

amitchandak
Super User
Super User

@Pandadev ,

Create a new column like

new column =
var _min= minx(filter(Table, [ID] =earlier([ID]) && [Date] > earlier([Date]) ),[Date])
return
if([code]= minx(filter(Table, [ID] =earlier([ID]) && [Date]= _min]) ,[code]),blank(),1)



!! Microsoft Fabric !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors