Filtering containing text on long string and referencing the filtered value

[Anonymous]

Greetings!

Pretty new to BI, I have a file with all Microsoft365 groups in my Company. I am interested in creating a dashboard that will allow me to filter for a specific mail and it'll show all the groups that user is member or owner of.

The relevant columns for every site are Owners and Members, each one having a string of all the users and members of that site separated by ";".

Example: mail1@contoso.com;mail2@contoso.com;mail3@contoso.com

I grouped those columns into one ("GroupUsers") to be able to filter by a specific mail using "Advanced filter" > "Contains" > "mail1@contoso.com".

What I would like to add, is a new column showing if the user is Owner or is Member(Permissions "Member" or "Owner"), for that I should be able to reference the filtered mail in the Advanced filter and having a measure finding that specific text in each row, but I am unable to make it work, so far I've tried the comands HASONEVALUE or SELECTEDVALUE, but I am pretty sure I am wrong on how this should work.

Does someone know a way to make it work?

[Anonymous · Accepted solution]

Hi @Anonymous 

Here I will show you two ways:

1.

If you can transform your data model, you can try Split Column by Delimiter in Power Query.

My Sample:

Select Member column and use Split Column function.

Do the same option in Owner Column.

New Table:

Then build an unrelated mail table for slicer and build a filter measure.

Mail Table:

Mail = 
SUMMARIZE(UNION(VALUES('Table'[Member]),VALUES('Table'[Owner])),[Member])

Filter Measure:

Measure = 
VAR _Selectvalue = SELECTEDVALUE(Mail[Mail])
VAR _Filter = IF(OR(_Selectvalue in VALUES('Table'[Member]),_Selectvalue in VALUES('Table'[Owner])),1,0)
RETURN
_Filter

Build a table visual by Group column, add this measure into filter field and set it to show items when value equal to 1.

2.

If you don't want to transform your data model, you need to get a mail table by yourself. Then you just need to build a filter measure.

Measure 2 = 
VAR _Selectvalue = SELECTEDVALUE(Mail[Mail])
VAR _Filter = IF(OR(CONTAINSSTRINGEXACT(MAX('Table No Transform'[Member]),_Selectvalue),CONTAINSSTRINGEXACT(MAX('Table No Transform'[Owner]),_Selectvalue)),1,0)
RETURN
_Filter

Do the same step as above.

Best Regards,

Rico Zhou

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

[Anonymous]

Thank you so much, its PERFECT!