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
pawelj795
Post Prodigy
Post Prodigy

Row contains specified value

Hi,
I have a column like below.

image.png

I want to return rows which contain let's say VVS150.

How to do this?

1 ACCEPTED SOLUTION
az38
Community Champion
Community Champion

@pawelj795  🙂

TEST= 
var VVS = SEARCH("VVS"; Warehouse[Item Name]; 1; BLANK())

RETURN
IF(ISBLANK(VVR); "Undefined"; MID(Warehouse[Item Name]; VVS+4; 3))

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

10 REPLIES 10
az38
Community Champion
Community Champion

Hi @pawelj795 

for example you can create a calculated column

_isContain = IF(SEARCH("VVS150", [Column]) > 0, TRUE(), FALSE())

then filter in visual by this new column

or

to create a new calculated table

_newTable = FILTER(Table, SEARCH("VVS150", Table[Column]) > 0)

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

@az38 
It's a only a bit of much complicated function, so it must end in one formula.

 

I ask differently.

image.png


From above screenshot I want to RETURN three digits.

Where is my mistake?
image.png

az38
Community Champion
Community Champion

@pawelj795 

try to replace BlANK() to 0 in your var statement - MID should have a digit second argument, BlANK() will provide an expected error

or try to obtain MID in if like

IF(ISBLANK(VVR); "Undefined"; MID(Warehouse[Item Name];VVS;3))

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

@az38 
Your first solution return same error.

But I can't understand your second idea.

What VVR and "Undefined" in your statement mean?

az38
Community Champion
Community Champion

@pawelj795 

it will check - if VVR returns BLANK() value then column wll be equal "Undefined" or anything you want instead. If VVR returns digit it will execute MID() function


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

@az38 
Now error dissappear 😉

Moving to the next.

image.png
image.pngimage.png

I want to return respectively 180,100,075,075,021,040 -> How to modify my function?

az38
Community Champion
Community Champion

ok

@pawelj795 

TEST= 
var VVS = SEARCH("VVS"; Warehouse[Item Name]; 1; BLANK())

RETURN
IF(ISBLANK(VVR); "Undefined"; MID(Warehouse[Item Name]; VVS+3; 3))

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

@az38 image.png

Unfortunately, it's not working

az38
Community Champion
Community Champion

@pawelj795  🙂

TEST= 
var VVS = SEARCH("VVS"; Warehouse[Item Name]; 1; BLANK())

RETURN
IF(ISBLANK(VVR); "Undefined"; MID(Warehouse[Item Name]; VVS+4; 3))

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

@az38 
Thank you so much for your help!

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.