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 ...
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

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