Adding further criteria to filter

[gambleave]

This seems dead basic, but I keep getting errors with everything I try. 

I have a working measure in the form:

=COUNTROWS(FILTER(ClientGroup, [Account Status]=1 && ClientGroup[Tier]="Gold"))

(Account Status is a measure to categorize accounts based on differrent transaction volumes)

I want to expand the filter criteria to include Account Status values of 1, 2 or 3 as well as Tier of Gold or Silver. Can anyone please help? 

[DimaMD]

hi @gambleave  Can you provide a sample of the data and your desired result?

[gambleave]

Afraid not, as it's in a corporate environment and I need to use my phone just to access this forum!

I thought this should be a measure selection/Syntax problem since it can be approached by creating additional measures for different permutations of Account Status and Tier, then adding them together. 

[DimaMD]

@gambleave  Try it

CALCULATE(
        COUNTROWS('ClientGroup'), 
        FILTER(ClientGroup, [AccountStatus] in {"1","2","3"}  && ClientGroup[Tier] in { "Gold", "Silver"})
      )

[gambleave]

Thanks for the suggestion. For some reason, I end up with the error "MdxScript(Model) (257,105) Calculation error in measure ...Function 'CONTAINSROW' does not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values. 

[wdx223_Daniel]

[AccountStatus] in {1,2,3}

[gambleave · Accepted solution]

Thanks a million to you both for the help. This works perfectly. 

Oddly, I tried the same measure in a Power Pivot version of my project with Excel 2016, but it doesn't work there (...The syntax for 'in' is incorrect...). Is it that this DAX operator doesnt work with Excel? 

[DimaMD]

@gambleave  Check the data format, numeric or text