Create a measure looping through a table

[lalford96]

Hi,

I am trying to create a measure that basically checks if a value in one column equals one from a list that I am currently manually typing, and then checks a second column to see if the value equals one from a list that I am manually typing. It is then calculating the total amount of returns.

The problem I am having is the measure is becoming very long and difficult to read as there are a lot of or statements both sides. For example it looks like this:

Calculate(COUNT('Data'[ColumnName]), FILTER(

'Data', 'Data'[ColumnName] = "Example1" || 'Data', 'Data'[ColumnName] = "Example2" 'Data', || 'Data'[ColumnName] = "Example3" && 'Data', 'Data'[ColumnName2] = "Example1" || 'Data', 'Data'[ColumnName2] = "Example2" || 'Data', 'Data'[ColumnName2] = "Example3"

And this is a short example, this is repeated around 20 times per measure. 

Is there a more simplified way to create this measure, or do I simply have to type them all out the way I have been?

[BA_Pete · Accepted solution]

Hi @lalford96 ,

In DAX you can use the IN { } method to help simplify your measures:

CALCULATE(
    COUNT('Data'[ColumnName]),
    FILTER(
        'Data',
        'Data'[ColumnName] IN {"Example1", "Example2", "Example3"}
            && 'Data'[ColumnName2] IN {"Example1", "Example2", "Example3"}
    )
)

Using this method you should also be able to go a step further by creating a new table with a column just containing the values you want (let's call the table and the column 'SearchValues' and 'SearchValues2'):

CALCULATE(
    COUNT('Data'[ColumnName]),
    FILTER(
        'Data',
        'Data'[ColumnName] IN {SearchValues[SearchValues]}
            && 'Data'[ColumnName2] IN {SearchValues2[SearchValues2]}
    )
)

Pete