If row contains specific text, identify the other rows with the same ID

[Anonymous]

I'm trying to create a custom column where the row contains the word "Excel" in either column A or column B and then flag the ID's.  There will be cases where there are a group of three rows with the same ID but only one will have the word "Excel" in either column A or column B.  I want to make sure I get the all three instead of one.

Thank you in advance!

[parry2k]

@Anonymous well you can easily create sample data in excel and import it in pbix file, you have to do some work to get the help. Not sure how to provide a solution without knowing the details. I hope someone else can help here.

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

@parry2k My apologies.  I'm still pretty new to this but I'll make sure to create some sample data in the future.  It looks like filtering out the rows with blank IDs made everything work great.  Appreciate all your help!

[parry2k · Accepted solution]

@Anonymous if you want to return same ID if col A or col B contains Excel in any of the row then use following measure and you can visual level filter where value is > 0

Measure = 
CALCULATE ( 
    COUNTROWS ( 'Table' ), 
    ALLEXCEPT ( 'Table', 'Table'[Id] ), 
    CONTAINSSTRING ( 'Table'[Column1], "Excel" ) || CONTAINSSTRING ( 'Table'[Column2], "Excel" )
)

@TheoC your solution will not work because if the same ID has two rows and only one row contains excel, it will not work and that seems to be the key requirement as far as I understood the post.

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

Thanks @parry2k 

This almost works.  A row will either contain "Excel" in either column A or column B or could be absent from both.  There's a possibilty of "Excel" being in both columns.  I was able to get the measure to work and then filter everything greater than 0.  Looks like I'm pulling in blanks though.  There are a bunch of rows where "Excel" is not in column A and column B is completely blank.  

[parry2k]

@Anonymous it should take care of those scenarios, can you paste the sample data in a table format or share pbix file and explain which is not working?

[Anonymous]

Hi @parry2k ,

Unfortunately, I don't have any sample data I can share or a pbix file due to data sensitivity issues.

I looked into things more and when I set the measure filter to greater than 0, nothing is getting excluded which doesn't make sense.  The measure you suggested is fitting all the criteria I need.  Do we need to exclude where ID is blank?  Not sure if that would solve it or not

[TheoC]

Hi @parry2k, great pick up mate! 

@Anonymous if you want the ID returned instead of a "1", just modify the solution provided by @parry2k to include an IF ( ISBLANK... ) such as below.

Measure =

VAR _Output1 = CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[Id] ), CONTAINSSTRING ( 'Table'[ColumnA], "Excel" ) || CONTAINSSTRING ( 'Table'[ColumnB], "Excel" ) )

RETURN 

IF ( ISBLANK ( _Output1 ) , "" , 'Table'[ID] )

[TheoC]

Hi @Anonymous 

FlagID = 

VAR _Keyword = "Excel"
VAR _ReturnKey = 'Table1'[ID]
VAR _ColumnA = 'Table1'[ColumnA]
VAR _ColumnB = 'Table1'[ColumnB]

RETURN 

IF ( OR ( CONTAINSSTRING ( _ColumnA , _Keyword ) , CONTAINSSTRING ( _ColumnB , _Keyword ) ) , _ReturnKey , "" )

Hope it helps 🙂

[Anonymous]

Thanks @TheoC .  I tried to create this measue but as soon as I tried to do th VAR _ReturnKey, the ID would not show as an option.  Any idea why that would be?