return values for two conditions - value greater than + text ( contains or not contais)

[TornDigorn]

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

[v-yiruan-msft · Accepted 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

[TornDigorn]

this table is more readable