DAX measure that has a top level name that filters for three companies

[dani_always]

Hello, I'm using the below DAX to create a measure that filters for three companies. I've also tried SUMX then filter. Neither worked. Any suggestions are appreciated. Thanks.

DAX

TopLevelName=

FILTER(

    'SEC',

    'SEC'[CompanyDBA] IN {"Company1", "Company2", "Company3"}

)

Error Message I'm getting

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

[AmiraBedh]

You are encountering this error because you are trying to create a measure, but the DAX expression you’ve provided returns a table, not a scalar value. Measures should always return a single value.

If you are trying to create a measure that sums a certain column for those three companies, you might use CALCULATE combined with FILTER. Here is an example assuming you want to sum a column named "Value":

TopLevelValueSum =
CALCULATE (
    SUM ( 'SEC'[Value] ),
    FILTER (
        'SEC',
        'SEC'[CompanyDBA] IN { "Company1", "Company2", "Company3" }
    )
)

[dani_always · Accepted solution]

Thanks. Got an error message with the above DAX. This ended up working ---

PrimeSales =

CALCULATE (

    COUNTROWS('RevenueAll_V'),

    FILTER(

    'RevenueAll_V',

    'RevenueAll_V'[CompanyDBA] IN {"Company1", "Company2", "Company3"}

))

[AmiraBedh]

My answer is correct, you didn't precise if you want to sum or count the rows.