Forum Discussion

lalford96's avatar
lalford96
Frequent Visitor
3 years ago
Solved

Create a measure looping through a table

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?
Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

    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

1 Reply

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    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