Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello dear Community
I need to generate some columns for below table, which will be based on the values in the "DWELL" column and the text content of the column named "COMMENTS"
Case 1 - Return AWB number for Case 1 column if DWELL >2 and comment contais "C-"
Case 2 - Return AWB number for Case 2 column if DWELL >2 and comment doesn't contain "C-"
Case 3 - Return AWB number for Case 3 column if DWELL >7 and comment contais "RTS"
Case 4 - Return AWB number for Case 4 column if DWELL >7 and comment doesn't contain "RTS"
If any additional explanation is needed I will be happy to assist
Thank you !
AWB number | DWELL | COMMENTS | Case 1 | Case 2 | Case 3 | Case 4 |
592969972201 | 11 | C-97145436 27.01.2023 pulling | ||||
393613856110 | 3 | C-96677635 23.01.2023 | ||||
770589254570 | 3 | C-94559996 03.1.2023 PULLING | ||||
771082002233 | 1 | KRKCAGE1 pulling | ||||
770947321472 | 3 | RETOUR EN FRANCHISE | ||||
771056750638 | 3 | KRKCAGE1 pulling | ||||
770776495662 | 3 | C-93047787 19.12.2022 pulling | ||||
393929174298 | 4 | KRKCAGE1 | ||||
540771516150 | 4 | KRKCAGE1 | ||||
573185202098 | 4 | KRK | ||||
549133897997 | 10 | C-97266744 28.01.2023 | ||||
770870711765 | 10 | C-96774332 24.01.2023 RTS | ||||
771077968520 | 41 | C-96699803 23.1.2023 RTS | ||||
620396944349 | 1 | C-97757933 02.02.2023 pulling | ||||
771144211500 | 50 | KRKCAGE1 | ||||
611472231009 | 50 | KRKCAGE1 | ||||
585744434673 | 1 | C-90572059 RTS | ||||
393900821802 | 1 | C-97616150 01.02.2023 | ||||
771114831675 | 3 | KRKCAGE1 | ||||
394046084100 | 15 | KRK | ||||
771194281063 | 12 | KRK | ||||
771169029934 | 1 | KRKCAGE1 pulling | ||||
394108285700 | 41 | |||||
393348739784 | 4 | C-97756422 02.02.2023 pulling | ||||
592969972201 | 11 | C-97145436 27.01.2023 pulling | ||||
393613856110 | 4 | C-96677635 23.01.2023 |
Solved! Go to Solution.
Hi @TornDigorn ,
You can create the calculated columns as below to get them:
Case 1 =
IF (
'Table'[DWELL] > 2
&& IFERROR ( SEARCH ( "C-", 'Table'[COMMENTS], 1, 0 ), 0 ) > 0,
'Table'[AWB number]
)
Case 2 =
IF (
'Table'[DWELL] > 2
&& IFERROR ( SEARCH ( "C-", 'Table'[COMMENTS], 1, 0 ), 0 ) = 0,
'Table'[AWB number]
)
Case 3 =
IF (
'Table'[DWELL] > 7
&& IFERROR ( SEARCH ( "RTS", 'Table'[COMMENTS], 1, 0 ), 0 ) > 0,
'Table'[AWB number]
)
Case 4 =
IF (
'Table'[DWELL] > 7
&& IFERROR ( SEARCH ( "RTS", 'Table'[COMMENTS], 1, 0 ), 0 ) = 0,
'Table'[AWB number]
)
Best Regards
Hi @TornDigorn ,
You can create the calculated columns as below to get them:
Case 1 =
IF (
'Table'[DWELL] > 2
&& IFERROR ( SEARCH ( "C-", 'Table'[COMMENTS], 1, 0 ), 0 ) > 0,
'Table'[AWB number]
)
Case 2 =
IF (
'Table'[DWELL] > 2
&& IFERROR ( SEARCH ( "C-", 'Table'[COMMENTS], 1, 0 ), 0 ) = 0,
'Table'[AWB number]
)
Case 3 =
IF (
'Table'[DWELL] > 7
&& IFERROR ( SEARCH ( "RTS", 'Table'[COMMENTS], 1, 0 ), 0 ) > 0,
'Table'[AWB number]
)
Case 4 =
IF (
'Table'[DWELL] > 7
&& IFERROR ( SEARCH ( "RTS", 'Table'[COMMENTS], 1, 0 ), 0 ) = 0,
'Table'[AWB number]
)
Best Regards
this table is more readable
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |