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
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe 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
To learn more about Power BI, follow me on Twitter or subscribe 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
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)
User | Count |
---|---|
122 | |
60 | |
58 | |
52 | |
40 |
User | Count |
---|---|
121 | |
60 | |
60 | |
54 | |
49 |