IN operator usage with Measure

[abhishekjangid]

Hi all,

I am working with IN operator and I have so many measure which have hardcoded values as of now.

I want to do something like this, I have a constant measure with a few whitelisted domains:
WhitelistedDomains = {"gmail.com", "yahoo.com"}

I want to create another measure which filters out only whitelisted domains rows from my table:
rows = Filter(emailTable, [emailColumn] IN [WhitelistedDomains])

Second measure does not work as expected. It throws an error that WhitelistedDomains is not a valid table.

What can be done here?

[komald]

@abhishekjangid ,You can try

Measure =

var lists = {"gmail.com", "yahoo.com"}

return

FILTER(EmailTable,EmailTable[Email] in lists)

[Greg_Deckler]

@abhishekjangid Yeah, unfortunately tha first measure is not valid because Microsoft doesn't allow you to return a non-scalar value for measures. So, for example, if you tried to use that first measure in a Card visual it would return an error. I really wish Microsoft would allow this. So, what you would need to do is to make your second measure this:

rows =
  VAR WhitelistedDomains = {"gmail.com", "yahoo.com"}
RETURN
  Filter(emailTable, [emailColumn] IN [WhitelistedDomains])

However, this will also not work due to the above explanation about returning non-scalar values so maybe something like this:

rows =
  VAR WhitelistedDomains = {"gmail.com", "yahoo.com"}
RETURN
  IF(MAX('emailTable'[emailColumn]) IN [WhitelistedDomains],1,0)

[abhishekjangid]

@Greg_Deckler 
I could do this but WhitelistedDomains is something I am using in multiple measures. So i though to bring it out somewhere in common place.

If there is some other way I can do this.

[Greg_Deckler]

@abhishekjangid Well, if it is hard-coded, then simply use that formula to create a Table instead. Then it will work just fine.

[abhishekjangid]

@Greg_Deckler 
ahh, do not want a table. Because domains might change over time.

[Greg_Deckler]

@abhishekjangid Yeah, again, I wish Microsoft allowed Measures to return tables. Would be so incredibly useful.