Forum Discussion
IN operator usage with Measure
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?
6 Replies
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)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.abhishekjangid Well, if it is hard-coded, then simply use that formula to create a Table instead. Then it will work just fine.
@abhishekjangid ,You can try
Measure =var lists = {"gmail.com", "yahoo.com"}returnFILTER(EmailTable,EmailTable[Email] in lists)
