Transform calculated columns into measures because dataset needs to be set to DirectQuery

[Anonymous]

I have a table called History that has 7 columns and 3 calculated columns that need to be transformed into measures because the dataset that I am using is very big, it has over 100 million lines so I can't use import, I need to use DirectQuery. Here is a sample of the table and calculated columns:
History table:

Barcode	Cls	Anomaly Code	DateTime	user_name	Crea Time	Index
6801309236	S	66T	08/08/2023 16:51:27	admin	07/08/2023 23:54:41	1
6801309236	S	66T	08/08/2023 16:51:27	admin	07/09/2023 00:11:10	2
6801309236	S	66T	08/08/2023 16:51:27	admin	07/09/2023 00:36:33	3
6801309236	S	66T	08/08/2023 16:51:27	user1	07/09/2023 00:42:00	4
6801309236	X	66T	08/08/2023 16:51:27	user2	07/09/2023 00:44:02	5
6818018058	S	60	07/09/2023 01:11:22	user3	07/02/2023 07:38:29	6
6818018058	X	60	07/09/2023 01:11:22	user3	07/02/2023 07:41:52	7
6818018058	S	60	07/09/2023 01:11:22	user4	07/02/2023 07:45:47	8
6817734956	R	60	07/02/2023 08:15:56	user5	07/02/2023 07:47:38	9
6817734956	X	60	07/02/2023 08:15:56	user5	07/02/2023 08:15:56	10
6817734956	R	60	07/02/2023 08:15:56	user6	07/02/2023 08:19:56	11
6818103629	O	60	07/09/2023 06:17:30	admin	07/09/2023 06:08:25	12
6818103629	S	60	07/09/2023 06:17:30	user2	07/09/2023 06:10:13	13
6823962853	O	000	28/07/2023 13:35:57	admin	28/07/2023 13:09:56	14
6823962853	S	66D	28/07/2023 13:35:57	admin	28/07/2023 13:18:20	15
6823962853	X	66D	28/07/2023 13:35:57	user7	28/07/2023 13:35:57	16
6823962853	S	66D	28/07/2023 13:35:57	user7	28/07/2023 13:38:59	17
6823998135	S	66D	18/07/2023 23:31:14		18/07/2023 22:50:03	18
6823998135	R	000	18/07/2023 23:31:14	user8	18/07/2023 23:21:12	19
6823998135	X	66D	18/07/2023 23:31:14	user2	18/07/2023 23:31:14	20
6823998135	S	66D	18/07/2023 23:31:14	user2	18/07/2023 23:37:41	21
6820161533	S	66D	24/04/2023 17:13:12	user9	24/04/2023 16:26:17	22
6820161533	S	60	24/04/2023 17:13:12	user10	24/04/2023 17:01:24	23
6820161533	O	000	24/04/2023 17:13:12	admin	24/04/2023 17:04:46	24
6820161533	X	66D	24/04/2023 17:13:12	user6	24/04/2023 17:13:11	25
6820161533	S	66D	24/04/2023 17:13:12	user6	24/04/2023 17:23:11	26

Calculated columns:

Next Cls = 

var _order = 'History'[index]

var _value =
CALCULATE(
MAX('History'[Cls]),
FILTER(
ALLEXCEPT('History','History'[Barcode]),
'History'[Index] = _order+1
))

Return
_value

Cond = IF('History'[Cls]="X"&&NOT('History'[Next Cls])="X",IF('History'[Next Cls]=BLANK(),"False","True"),"False")

Barcode True = 
var cond =
CALCULATE(
    FIRSTNONBLANK(
        History[Barcode],
        TRUE()
    ),
    ALLEXCEPT(
        'History',
        'History'[Barcode]
    ),
    'History'[Cond] = "True"
)

RETURN
IF(History[Barcode]=cond,History[Barcode],BLANK())

The whole table looks like this:

After transforming the calculated columns to measures I want a table visual to look like this (expected output):

If you have a better way to do this instead of transforming the calculated columns into measures, please tell me.

[some_bih]

Hi @Anonymous did you try to get "transformation" before Direct Query, like creating view or something? Later or sooner you will be in trouble for performance as you are dealing with lot of rows.

[Anonymous · Accepted solution]

Yes, that's what I did. I used this Select and it worked

SELECT  *
  FROM History with(nolock)
  WHERE [barcode] IN (
      SELECT [barcode]
      FROM History AS h1
      WHERE [overallGrade] = 'X'
      AND [barcode] IN (
      SELECT [barcode]
      FROM History AS h2
      WHERE h2.[last_modified_time] > h1.[last_modified_time]
      AND [overallGrade] != 'X'
      )
)
ORDER BY [barcode], [last_modified_time] OFFSET 0 ROWS