Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Pandadev
Post Prodigy
Post Prodigy

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
Employee
Employee

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 Prodigy
Post Prodigy

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)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.