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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
TornDigorn
Frequent Visitor

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

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 DWELLCOMMENTSCase 1 Case 2 Case 3Case 4
59296997220111C-97145436 27.01.2023 pulling    
3936138561103C-96677635 23.01.2023    
7705892545703C-94559996 03.1.2023 PULLING    
7710820022331KRKCAGE1 pulling    
7709473214723RETOUR EN FRANCHISE    
7710567506383KRKCAGE1 pulling    
7707764956623C-93047787 19.12.2022 pulling    
3939291742984KRKCAGE1    
5407715161504KRKCAGE1    
5731852020984KRK    
54913389799710C-97266744 28.01.2023    
77087071176510C-96774332 24.01.2023 RTS    
77107796852041C-96699803 23.1.2023 RTS    
6203969443491C-97757933 02.02.2023 pulling    
77114421150050KRKCAGE1    
61147223100950KRKCAGE1    
5857444346731C-90572059 RTS    
3939008218021C-97616150 01.02.2023    
7711148316753KRKCAGE1    
39404608410015KRK    
77119428106312KRK    
7711690299341KRKCAGE1 pulling    
39410828570041     
3933487397844C-97756422 02.02.2023 pulling    
59296997220111C-97145436 27.01.2023 pulling    
3936138561104C-96677635 23.01.2023    
1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

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]
)

yingyinr_0-1676436272535.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yiruan-msft
Community Support
Community Support

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]
)

yingyinr_0-1676436272535.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
TornDigorn
Frequent Visitor

TornDigorn_0-1676376973605.png

this table is more readable

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.