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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
RaulB
Frequent Visitor

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:
Capture.PNG


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

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

1 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

 

View solution in original post

2 REPLIES 2
some_bih
Super User
Super User

Hi @RaulB 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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






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

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.