Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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

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:

BarcodeClsAnomaly CodeDateTimeuser_nameCrea TimeIndex
6801309236S66T08/08/2023 16:51:27admin07/08/2023 23:54:411
6801309236S66T08/08/2023 16:51:27admin07/09/2023 00:11:102
6801309236S66T08/08/2023 16:51:27admin07/09/2023 00:36:333
6801309236S66T08/08/2023 16:51:27user107/09/2023 00:42:004
6801309236X66T08/08/2023 16:51:27user207/09/2023 00:44:025
6818018058S6007/09/2023 01:11:22user307/02/2023 07:38:296
6818018058X6007/09/2023 01:11:22user307/02/2023 07:41:527
6818018058S6007/09/2023 01:11:22user407/02/2023 07:45:478
6817734956R6007/02/2023 08:15:56user507/02/2023 07:47:389
6817734956X6007/02/2023 08:15:56user507/02/2023 08:15:5610
6817734956R6007/02/2023 08:15:56user607/02/2023 08:19:5611
6818103629O6007/09/2023 06:17:30admin07/09/2023 06:08:2512
6818103629S6007/09/2023 06:17:30user207/09/2023 06:10:1313
6823962853O00028/07/2023 13:35:57admin28/07/2023 13:09:5614
6823962853S66D28/07/2023 13:35:57admin28/07/2023 13:18:2015
6823962853X66D28/07/2023 13:35:57user728/07/2023 13:35:5716
6823962853S66D28/07/2023 13:35:57user728/07/2023 13:38:5917
6823998135S66D18/07/2023 23:31:14 18/07/2023 22:50:0318
6823998135R00018/07/2023 23:31:14user818/07/2023 23:21:1219
6823998135X66D18/07/2023 23:31:14user218/07/2023 23:31:1420
6823998135S66D18/07/2023 23:31:14user218/07/2023 23:37:4121
6820161533S66D24/04/2023 17:13:12user924/04/2023 16:26:1722
6820161533S6024/04/2023 17:13:12user1024/04/2023 17:01:2423
6820161533O00024/04/2023 17:13:12admin24/04/2023 17:04:4624
6820161533X66D24/04/2023 17:13:12user624/04/2023 17:13:1125
6820161533S66D24/04/2023 17:13:12user624/04/2023 17:23:1126

 

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.

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

     

2 Replies

  • some_bih's avatar
    some_bih
    Community Champion
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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